Moving unattached MDF and LDF

[jdl508]

Hello, I have a server that died but I was able to retrieve the MDF and LDF for all databases on it. My questions is that now that I have built a new server is there a way to attach these DB's even though they were never 'unattached'?
All other specs are the same. The server is win2k adv server running sql 2000 sp2.
Thanks,
jdl508

 

[JayKusch]

You can try running the sp_attach_db and if that does not seem to do the trick ... rename the LDF to a new name, maybe CurrentDBName.LDF2 and then try to run sp_attach_single_file_db on just the MDF. Would advise you took a look at BOL for good measure on the correct syntax for each type of reattach!!!

Let us know how this works out and if there are any error/issues. We have other methods to try if things do not go right for us!

By the way ... what version of SQL Server is installed AND what version was used to create the MDF/LDFs we are trying to attach??



Good Luck!



Thanks

J. Kusch

 

[SQLBill]

The new SQL Server server has to have the same patch level as the original server. But I assume that is how it already is set up since you say all other specs are the same.

But, why, why, why aren't you patched to SQL Server 2000 sp3/3a???? That service pack protects you against the SLAMMER worm (among other things).

-SQLBill

 

[jdl508]

Well so far no good. I am getting the following error when trying to attach the db. I have tried both the traditional sp_attach_db and
sp_attach_single_file_db
I'm assuming that because the DB file was not properly detached it is not happy. Here is the error
++++++++++++++++++++++++++++


Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'e:\progra~1\micros~1\mssql\data\iSolutions_S2_inq.mdf' is not a valid database file header. The PageAudit property is incorrect.

+++++++++++++++++++++++++++

JayKusch and SQLBill appreciate your help I was here until 2 AM and now back in to get this up monday morn
Thanks
jdl

 

[jdl508]

I just noticed 2 discrepencies between the old and new sql server.
old server had MSMQ installed and was sql 2000 SP3

new server no MSMQ and sql 2000 SP2

I am making the appropriate upgrades and crossing my fingers.

 

[jdl508]

Well no luck as of this morning yet. I am sure there must be a way to do this. I checked our tape backups and the *.bak files wont restore either. I have also tried creating a blank DB same name then stopping services and changing out the mdf and ldf files. Well time to start brainstorming
thanks
jdl

 

[JayKusch]

OK ... let brainstorm ... Does this link meet any of your current setup ???

http://support.microsoft.com/default.aspx?scid=kb;en-us;268481

Thanks

J. Kusch

 

[jdl508]

Thanks for all the replies, fortunately at another site we had a replicated test db server and I just made a quick backup of all the DB's and they restored like a champ. Why the original DB.bak files would not restore I dont know. There must have been some hardware inconsistency or something. Oh well 32 hrs later I'm a happy camper again. Thanks for all the help