Moving a database

[SilviaS]

Ok everyone look at this,
at present windows NT4.0 server running sql7.0
When installed originally it was set up on the C:.
I have another partition D: and therefore need to move the database to D:.
I also have iis running. Which way should we do this.
If i reinstall sql 7.0 to the d: partition can i just move the databases over?
Let us know.
cheers

 

[soy4soy]

Yes, you can move it... sort of.

It has been a couple of years since I've had to do this, so please do not take it as gospel. Microsoft.com will have an updated detailed procedure for this.

But basically:
1) You will need to back up the database(s).
2) Do your uninstalls/reinstalls of SQL server.
3) Create new database(s) in the new system.
4) RESTORE, but you will need to check a check box that will allow you to restore it from "another source/location" or the like...

Sorry for the fuzzy, but that is all I recall off the top of my head. (really, microsoft.com will have the exact detailed procedure)

 

[Corran007]

You really dont need to jump through that many hoops for it all. You can just detach the user databases, and move them to their new location. Ive never tried detaching the msdb, but you can just backup and restore that one.

uninstall then reinstall sqlserver in the new location, restore msdb and reattach user databases. backing up and restoring does work, but attach/detach is usually faster, especialy if you have large databases.

make sure that on the msdb you get them to the same patch level before you restore it. It will have problems if you do not.

For more info on syntax look up sp_detach_db and sp_attach_db in bol