Need some guidance!

I have been trying to rescue my company's helpdesk database because the company moved premesis and we lost that particular server.

It was originally running SQL Server 6.5, and my line manager simply copied the database.DAT files straight out of the MSSQL\Data directory without running a backup through Enterprise Manager.

Does anyone know how i can rescue these files so that we can get our helpdesk up and running again?!

thanks in advance for any help you can provide.I've never done this...but did you try and put the files in the exact location as the old box?

Is the line manager your boss?

If not, smack him/her in the head...

If yes, say what a great job they did to back up the data...

IOs the old box still available?

And how big is the file?|||ok,

Yes i have tried putting them back into the MSSQL\Data directory of an SQL server 6.5 install on a different server, but enterprise manager doesn't recognise them. I've tried creating databases with the said files in question and over-writing them with the originals, but it just makes them suspect, and therefore unusable.

Yes the line manager is my boss, and unfortunately, there's not much i can really say about them, but the "great job" one has sprung to mind.

The files are 77Mb for the main database i want to restore, and the master database is 100Mb.|||As SA you can go into master and manually change the status of the database from Suspect to "AOK". This is not guaranteed to work, but I have done it succesfully on 6.5.

If you got nothing to lose...|||Ok, so he's your boss...buy him a drink...

So you don't have a contact to someone where the old box is?

I remember reading that you have to modify the system tables...

It's not the best solution...
I would try and find someone to help from the old box...is it decommisioned?

Also try doing a search over at sqlteam...I know I saw it over there...|||thanks for that. we don't really have anything to lose, but thankfully we have an old version of the database that works, but it'll put us back to march 2002, and my boss thinks that is unacceptable.

Unfortunately the old box has been formatted and is being used for something else.

have you got the url for sqlteam? it won't hurt to go and have a look, and we might just get a solution to this (we've been working on it for almost a week now).|||Seriously. Try what you did before where you got the suspect database warnings.

Then set the database to allow changes to the system tables, locate your database in the sysdatabases table, and change its status to the value for a database that is not suspect. What can you lose? Not all suspects are guilty, you know...|||http://www.sqlteam.com/default.asp

Good luck...

Formatted the box...sheesh...

Do they have a tape backup?

And if your boss thinks it's unacceptable...you might want to mention that taking backups with his method was un...well not the prefered method...

Where are you from...want me to pay a visit?

I don't have to be nice....

Or better yet...send the hit squad...

The blind dude and rdjabarov should put the fear of god in to him...|||Got the first part done, but as for this part:

Then set the database to allow changes to the system tables, locate your database in the sysdatabases table, and change its status to the value for a database that is not suspect. What can you lose? Not all suspects are guilty, you know..

This is going to sound more than a little stupid, but where do i go to access the above? (i've just been going along blind, because A, im the only one who is free to do it, and B because i don't have any training on server software at all. Not even our IT guy knows how to work with SQL server 6.5, but if you could provide me with a bit of an idiots guide to get to those areas then i would really appreciate it.

Brett, im from outside Aberdeen, but i dont think my boss would take too kindly to having to get someone in to sort this one out. Must be a manager thing...|||That doesn't sound stupid. This is advanced crap.

To set the allow updates option:

Expand a server group.
Right-click a server, and then click Properties.
Click the Server Settings tab.
Under Server behavior, select or clear the Allow modifications to be made directly to the system catalogs check box.

Now run:
select status, name from sysdatabases
to see that status of all your databases

Then run:
Update sysdatabases set status = [YourDesiredStatus] where name = [YourDatabaseName]

Then RESET THE ALLOW UPDATES OPTION!

Here are the bit values for the status column. Select the options you want, add up their values, and that is [YourDesiredStatus].
1 = autoclose; set with sp_dboption.
4 = select into/bulkcopy; set with sp_dboption.
8 = trunc. log on chkpt; set with sp_dboption.
16 = torn page detection, set with sp_dboption.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with sp_dboption.
1024 = read only; set with sp_dboption.
2048 = dbo use only; set with sp_dboption.
4096 = single user; set with sp_dboption.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.

I'd run backups of any critical data on the server before doing this, just as a matter of principle.

You now know enough to be very dangerous.

Oh, and we DBAs prefer to call it "Books Online" rather than "Idiot's Guide".|||ok, cheers for that. I just had a thought though. Would the same be possible on a server 2000 install? i.e. re-naming the old .DAT files to the respective file formats and following the above (with the obvious changes due to the software).

If it does then i can put 2000 on the test machine to see if that works out. I seem to remember something about 6.5 databases being incompatible with 2000 for some reason, unless a direct upgrade is carried out...|||You don't know them to well...

I didn't say they'd come in and fix it...mostly berate the guy...

And it's 6.5...I didn't look to close...

I try to forget everything pre 7...

A search...this was a fun thread

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36375&whichpage=1&SearchTerms=restore%2Cdb%2Cwith%2Cdb%2Cfile

Need to do more digging...|||That doesn't sound stupid. This is advanced crap.

You now know enough to be very dangerous.

Oh, and we DBAs prefer to call it "Books Online" rather than "Idiot's Guide".

AMEN brother

Your a DBA?...go figure...

And it's BOL|||im nowhere near a DBA (just incase that was intended for me), i just seem to be the try and fix it guy.

Oh, here's another challenge for you. I've just discovered that i need to have it up to service pack 5a, looking through the microsoft site there's only post service pack 5a, not much use there. do you guys know where to find the relevant service packs 'cause im now stumped. (and it all started off with my boss saying "no you don't need the service packs. It should run fine enough without them.")|||and it all started off with my boss saying "no you don't need the service packs. It should run fine enough without them.")

I'm speechless...

What line of business are you in?

I hope it's one where you don't need to worry about data

http://www.microsoft.com/sql/downloads/2000/sp3.asp|||Im not speechless, im pretty fed up with it.

The company i work for is a supplier for power managment companies like the National Grid and a few other ones in other countries. The helpdesk that im trying to fix (which im employed to take the calls for, not fix it) deals with any control system problems that may exist, as well as keeps track of all the hardware that is used in the makeup of the control system (so you can see it's a fairly important thing to get sorted out asap, but it hasn't been the case.)

the problem had been sitting unresolved with a few half-hearted efforts to solve it over the past 8 months, which i was pretty much screaming to get it fixed the whole time. now i got fed up so i started looking into it, and that brings us to where we are today.

Anyway, im going to have to log off now and go home and finish off servicing 4 mountain bikes (finally, something relaxing!), which should take me until friday to do.

I'll post any progress made and/or any other queries up here tomorrow, because i'll probably get stuck real quick.|||You gotta be across the pond somewhere...Ireland perhaps?

And you need the service packs for bot the OS and SQL Server...

Ever hear of slammer?

And if it's out there 8 months now...how are they recording the data now?

Good luck...and have fun with the bikes...

Sounds like you know a good deal about them...

I'm looking to buy a couple...what would be a good one...not too high end, but not junk either...|||If you are still have problems, why don't you just place a call to your company's helpdesk? That person should know all about database servers and system tables and suspect files and ... Oh. Nevermind...

Do NOT try klugeing in a 6.5 database file in place of a 2000 database file and then hand-changing the status code. A general rule of ANY issue resolution situation is to fix ONE THING AT A TIME. Reinstall your 6.5 data base server, bet the old database working, and then upgrade to 2000.|||thanks for the input guys. Brett, i live by Aberdeen, Scotland. If you're looking for a couple of bikes, what kind of riding are you most likely to do?|||How big is aberdeen?

http://www.dbforums.com/showthread.php?p=3701716#post3701716|||Aberdeen has a population of about 265,000 (i think). it covers a fair old area at any rate.|||Did ya give a shout out to David?

And your day's close to being over...isn't it?|||yeah. You'll see my replies in the Yak Corral thread. I wish it was almost over. Its 14:25 here and i don't finish until 16:45. I went and asked a mate of mine if i could do a straight swap for his bike. won't find out until around 7ish, and i really hope he goes for it, even if he is losing out slightly.|||is there any way to use command prompt to create a backup of a database file from a specified location i.e. C:\backup\tempdb.DAT?|||Yes, through the OSQL utility included with SQL Server.|||thanks blindman. I was in the middle of finding a tutorial for using bcp, but i'll try this first to see if it works.|||actually, how do you use it? just had a quick look and it seems to be along the same lines as bcp (i.e. too complicated for me).

just going back to one of your past posts, the database server we are running for the helpdesk is a seperate entity from the company network, and as such doesn't come under normal company IT support. If we were going to raise a call with the IT department, they would be required to take full control of the server and that would mean that they could do as they see fit with it, which my boss says is unacceptable (as usual).

so basically im left with the job of trying to figure out a way to fix the problem (with no training in servers, sql or database management at all, but with this company im not suprised that they expect things to work with no training being given), which is why i ended up here. At least i'll be able to get advice on areas i won't have thought on, or can't because i don't really know what im doing.|||OSQL utility allows you to run SQL statements from the command prompt. You can either enter them line-by-line or call OSQL with a batch file of SQL commands. Your SQL batch file would contain the SQL commands for backing up your database.

BCP is only mildly complicated. It can be a bit quirky to get to work right the first time, but after that it is very fast, simple, and reliable.|||along the same lines as bcp (i.e. too complicated for me).

Huh?

Damn that hurt...

bcp is your freind...get to know her extremely well....

and I use osql very infrequently...you can do it all in sprocs...

I actually justy use osql so I can build a script that I give to my PROD DBA...mostly so they don't screw up a release...|||i guess it'll just take time to learn how to use it.

Got some other bad news today. I was trying to re-allocate resources on my website, and because of the package i have i can't re-allocate any resources at all (im running a php 6.5 website at the moment). So i only have three options: upgrade my package deal (cost somewhere in the region of $100US to do it though), build a brand new html website that won't have the same functionality as the one i have at the moment, or move servers to somewhere where i can re-allocate resources to my hearts content. Is that readysetconnect server any good? 1250Mb with 40Gb transfer is a heck of alot better than my current 100Mb space with 1Gb transfer.|||Here's a nice quick GUI tool for BCP.
http://weblogs.sqlteam.com/davidm/archive/2004/03/31/1151.aspx|||that's brilliant. thanks peter|||A GUI?

Don't use xp_cmdshell....|||Man, you guys are confusing him! OSQL did not exist in 6.5, so the only thing is ISQL there. BCP? He can't even get to his database, how in the world is he gonna use BCP? Monkeying with the status? Oh Codd...Check DISK REFIT/DISK REINIT...Man, I don't even have BOL for 6.5 any more...
Search for Sybase documentation, they had a lot in common, including the commands that I posted.|||You're right. OSQL did not exist. But ISQL did and he should have that. I never did figure out what the difference was...|||ISQL is using DB-Library to "talk" to the server, while OSQL is using ODBC.|||Well, I am wading into this thread a little late. Let me see if I have this all straight.

1) There once was a SQL 6.5 box with the database for the helpdesk application on it.
2) In a move, this server was lost.
3) The last known backup of the database is from March.
4) You have the .DAT files from the old SQL 6.5 box, and you boss thinks you can get them attached to the new 6.5 box?

Hmm. I think I would have to plead ignorance on this one, and have your boss show the "poor ignorant lowly wage drone" just how to do it. After he fails, you can have a private laugh in the pub ;-).

There might be a way of doing it, but I can not think of what it may be. I have not touoched SQL 6.5 in ages (3 years), now. If I remember right, there was a column in the master database that was related to the last transaction ID in each database. If that is right (and that is a big "if"), I think reattaching this database could be impossible. As Blindman pointed out, it is certainly darned difficult.

Without the database being attached to a live SQL Server, You will not be able to run anything against it (isql and bcp included). All I can think of is what you have already tried (create new database with the same name, shut down, switch files, pray).

As I remember detaching and reattaching databases was a big deal when SQL 7.0 came out. I did not see the big deal at the time, and thought it was just another Microsoft marketing blitz fixating on minor details that have almost no application. Wait... I still think that. Shows you how often I use detaching databases, eh? The point is, it is not supported in SQL 6.5

If you do go humbly before your boss to have him show you how it is done, be sure to let us know what he says when he confronsts the inevitable. I think we can all guess what Microsoft Support's answer to all of this will be.|||...Again, DISK REFIT/DISK REINIT should do the drick ONLY if you have all .DAT files. I hope you're not missing log devices or have an incomplete set of .DAT files...|||rdabarov probably has it. I do not remember DISK REFIT, but DISK REINIT rings a bell. Be sure you have a safe copy of the .dat files, though. These commands may make a few changes to the files.|||ISQL is using DB-Library to "talk" to the server, while OSQL is using ODBC.

Yeah, but I don't remember any functional difference. Was there?|||Beyond OSQL having a few extra/changed switches, there was not a whole lot of difference that I could see. I could have been Microsoft trying to phase out DB Library. Why they went to ODBC, I am not entirely sure. Looks like OSQL could be used with an ODBC DSN, so technically, you could try to query Oracle with it. Not sure why you would want to, though.

Judging by the SQL 2005 download, it looks like ISQL is dead, OSQL is deprecated, and SQLCMD is the new tool for scripting. Again, just a few changes in the switches from OSQL. Probably a lot under the covers, but I don't see it, yet.

EDIT: Oh, and BCP is still there. How much of Brett's stuff would break, if they changed BCP to SQLCOPY? ;-)|||Bite your tonque...the horror, the sheer horror....|||We need a topic change. Or move it to Brett's place (everybody should know by now where it is ;))|||Does Brett's place serve Margaritas? I've been working with Oracle all week, so I could use a pitcher myself.|||I have all the .DAT files, but they are not backup files of the databases. My boss has only just realised that he should have made a backup of everything through the enterprise manager, but instead just copied them directly from the MSSQL\Data directory.

But here's the latest on what's been going on:

We have set up the box we were using to run server 2000 (windows and sql) with the march 2002 backup we know works. sounds straight forward enough.

My boss has put another box together and has set it up the same as we had it before (windows server 2000 and sql 6.5), but he wants to try and see if he can get it working.

Im overjoyed that he finally took it in hand and decided to try this for himself because he knows alot more about sql server and all the rest too. I keep getting asked about what i did and how i did it, but i'll mention this and see if he knows about it. (rdjabarov, if you could give me a procedure on how to do the DISK REFIT/DISK REINIT then that would be great)

but utill then im stuck trying to get the rest of the software working, which due to fact it is installed on different machines, will have to be re-license and god only knows how much that'll cost. Once that's done, i'll have to try and bring the information up to date manually, or at least until we rescue those .DAT files.|||ok, here's the latest update:

I got the AHDTEST database that i had been trying to rescue working to a degree in 6.5, but i got 81 pages (A4 pages) of errors because the master database has not been restored. I have a few queries about this because the .DAT files were made on a server called BK_MAN5_INV, and the name of this server is XW4000, so there might be a problem with the DISK REFIT/DISK REINIT anyway wouldn't there? My train of thought leads me down to the fact that the servers have different names and different configurations, thus unless the REFIT/INIT commands altered these settings to be the same as the target machine (in this case XW4000) then that would be fine, but im not so sure about that.

Does anyone know if it actually does this?|||So what database have you restored? I didn't mention it (got busy here for a change ;)) but when installing a fresh 6.5 instance you needed to make sure that code page, language, and sort order are the same as the original. Is this the case?|||no one here knows what the code page, language and sort order settings were due to this being an inheritence case. all we could do is assume they used system defaults. Database wise, i got the AHDTEST50 database in without it going suspect, but got a shed load of errors due to the fact that the master database, and thus all the other databases too, haven't been put in. So as a result im looking at restoring the system databases as well as the user databases. I just wish there was a way to put them all in at the same time (through MS Query or something like it).sql