Moving the location of a database

[Erics44]

Hi
This might sound like a stoopid question but how do i move a database to a different location. It is currently on the C drive and i want it to be on the E drive
Thanks

 

[sodakotahusker]

Right click on your database name in Enterprise Manager. Choose All tasks from the menu and then Detach from the submenu. Move your files to desired location. Right click on the word Databases (your database disappeared from the list of databases by the way)
in EM. Choose All Taska again and then choose Attach. Enter the location/name of your .mdf (data file) and the name of the database it should be attached to and click OK. You should be back in business in your new location.
I would try this process with a test database first just to make sure you have it cause you do lose your database until you attach again.

 

[Erics44]

brilliant mate thanks

 

[sodakotahusker]

PS: I left out one mouse click. You have to click the verify button if you type in the name of the location of your data file to activate your OK button. If you use the browse feature you don't cause it verifies when you chose the file.

 

[Erics44]

The problem I have now is that all of my databases are saved on the C drive of the SQL server and I want to save them to a different drive E.

Looking in enterprise manager I am not sure how I change the drive location. I can only see the list of databases.

Thanks for your time

 

[thegeezza]

Evening,

Once you detach the database, simply move the physical .mdf file to the new location/hard drive.
Then re-attach the database from its' new location.

There may be a few more steps involved/files to move if your DB has not been kept in simple recovery model however.

cheers,
Thegeezza

 

[Erics44]

So I open enterprise manager

right click the database and detach it

move the mdf file to the new location just in explorer

then reattache the database in enterprise manager in the same place as i detached it?

sorry if i am being a bit stupid