restoring old Databases

[Guest_imported]

I had all my databases on a HDD on MS SQL Server 7.0. Now I needed to install a new HDD to replace my old HDD.
I installed SQL Server 7.0, but now what is the best and easier way to get all the databases I had on my older HDD into my new one? I'm not used to SQL Server I hope this is easy to do...

thank you in advance

 

[RickCole]

Can you still access your databases via Enterprise manager on your old HDD??

If so you can use the stored procedure sp_detach_db to dettach your database mdf and ldf files. Move the files to you new HDD and then use sp_attach_db to get them back on-line again.

Failing that if you have any backups you can just restore the databases straight onto your new HDD via Enterprise Manager.

Rick.

 

[Guest_imported]

thank you for the reply Rick,
I don't seem to know how to do what you said, I mean... I have now the old disk as a slave and the new one as master. On SQLServer it will only appear the databases that are on the new one, but still I have the old ones on the other HDD.
How exatcly am I able to do this? I can't find that stored procedure...

I actually managed to transfer the DBs, by exporting the old DB to a 2nd computer and then importing then them from SQLServer running on the new HDD. The only problem was that I lost the diagrams of the DBs, using import and Export the diagrams won't be transferred for what I've seen or is there a way to do it?

Sorry for taking your time.

 

[RickCole]

Can you still access your old databases via a version of Enterprise manager on your old HDD??

Rick.

 

[RickCole]

Oh the sp_attach_db and sp_detach_db stored procedures are held in the master database.

This is a Northwind database example. Where the physical files Northwind_Data.MDF and LDF are currently held on the C: drive.

sp_attach_db
@dbname = 'Northwind',
@filename1='C:\MSSQL7\DATA\Northwind_Data.MDF',
@filename2='C:\MSSQL7\DATA\Northwind_Log.LDF'

In your example move your files from you old HHD to somewhere on your new. And then tweak the above SQL and run from a Query anlyser window.

Rick.

 

[Guest_imported]

ok, think I got it now
So basically to restore the old DB all that I need is the MDF and LDF files! This is just what I was looking for, you saved my day. I wasn't sure if I could restore a DB by using only those files.
I will try it later hope it will work! Will this also copy the Diagrams from the old DB?

If it isn't asking too much... what is the sp_detach_db stored procedure for? To delete a database from the HDD?

Many Many thanks

 

[RickCole]

Yes all you need is those files for each database you need to restore. The databases will be exactly as there were so your diagarms will also be included.

sp_detach_db deletes the database references from the master database but does not delete the physical mdf or ldf files.

Rick.