When I was a baby DBA, I used to detach and attach but as I becomes strong, I do believe you dont have to stop the SQL Server service to move database files, but you do have to take the specific database offline. This is because you can’t move files while they’re being accessed and taking the database offline stops the files from being used by the SQL Server application.
The process to move them is fairly simple. Detach/Attach was already described, but it is not nearly this complex.
Change the file locations with an ALTER DATABASE command:
USE master;--do this all from the masterALTERDATABASE foo
MODIFY FILE(name='DB_Data1',filename='X:\NewDBFile\DB_Data1.mdf');--Filename is new location
Note, you do not need to declare the old location in this command. Changing this path does not take effect immediately, but will be used the next time the database starts up.
Set the database offline
(I use WITH ROLLBACK IMMEDIATE to kick everyone out and rollback all currently open transactions)
ALTERDATABASE foo SET OFFLINE WITHROLLBACK IMMEDIATE;
Move/Copy the files to the new location
Just copy the files over using your favorite method (Click ‘n Drag, XCopy, Copy-Item, Robocopy)
Bring the database online
ALTERDATABASE foo SET ONLINE;
This method is good when you are co-hosting other databases of other applications and you dont want to have impact on them.
Need Help? Chat with us
Start a Conversation
Hi! Click one of our member below to chat on WhatsApp