Moving DBF from one disk to a another disk

[sctxms]

I just started as a sql dba my backround is Oracle and DB2 so please excuse my question
I just added new disks to my server and would like to move some of the database files from the E and F drives to the G and H drives. My question is how is the best way to do this plus do I just write a update script for the master database once I move the database files?

 

[mrdenny]

Avoid updating the system tables as much as possible.

There are a few methods. All work fine. All require downtime.

1. In Enterprise Manager right click on the database > All Tasks > Detach Database. Move the physical files to the new drive. Right click on the Databases folder in Enterprise Manager > All Tasks > Attach Database. Point it to the mdf file, adjust any other paths so that everything shows a green checkmark, select the new owner, and click ok.

2. Same procedure but via T/SQL instead of the GUI using the sp_detach_db and sp_attach_db. Use sp_detach_db to detach the database. Move the files and use sp_attach_db to reattach the database. Books OnLine has the full syntax for these procedures.

3. Backup and Restore the database.

Option 1 is the easiest.
Option 1 and 2 have the same amount of down time.
Option 2 is my preferred method (since you can save the script for next time, and I'm very lazy).

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sctxms]

Thanks I will use the script method because I am lazy too. So I guess the master db will be updated when I reattach my other db

 

[mrdenny]

Yes, sort of.

When you detach the database it's entry in the master database is removed as the database is no longer part of the SQL Server, so SQL doesn't care about it.

Once you attach it, it will be placed back into the master database.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com