Wednesday, February 21, 2007

SQL Server: How to move a database file

I recently had to move the log file of a SQL Server database. Initially I thought it might be a simple alter database command, as in How to Change a Database File, but that does not work out:


-- Note: this will not work
alter database MyDatabase
modify file (name='MyDatabase_Log',
filename='c:\Program Files\Microsoft SQL Server\MSSQL\Data_log\MyDatabase_Log.LDF')
go
Msg 5037, Level 16, State 1, Server MYSERVER, Line 1
MODIFY FILE failed. Do not specify physical name.

I found the answer in the SQL Server 2000 Administrator's Pocket Consultant: you have to detach the database, move the file using Windows, and then reattach the database, specifying where the files have gone:


-- Put the database in single user mode
use master
exec sp_dboption MyDatabase, 'single user', true
go

-- Find out where the database files are located
exec sp_helpdb MyDatabase
...
C:\Program Files\Microsoft SQL Server\MSSQL\data\MyDatabase_Data.MDF
C:\Program Files\Microsoft SQL Server\MSSQL\data\MyDatabase_Log.LDF

-- Detach the database
exec sp_detach_db 'MyDatabase', 'true'
go

-- After this, you will need to move the files to their new locations using Windows

-- Reattach the database, specifying where the files are now located
exec sp_attach_db 'MyDatabase',
'C:\Program Files\Microsoft SQL Server\MSSQL\data\MyDatabase_Data.MDF',
'C:\Program Files\Microsoft SQL Server\MSSQL\data_log\MyDatabase_Log.LDF'
go

8 comments:

  1. Thank you! That was driving me mad too!

    ReplyDelete
  2. Yup, SQL Server makes everything easy... except for the things it makes nearly impossible!

    ReplyDelete
  3. EXCELLENT!

    Great Help

    ReplyDelete
  4. ¡De nada! Me alegro que te haya funcionado.

    ReplyDelete
  5. Thanks for this. It solved a pernicious problem I was having when restoring a database from a previous backup. The data and log files were both specified with a '.mdf' extension which seems to confuse SQLServer Management Studio when doing a restore, and it will specify the same path for both. Since we ship this backup as part of our product, it was an issue that could not be ignored. Thanks again.

    ReplyDelete
  6. libauer, I'm really happy to hear that this worked for you! Glad it was helpful.

    ReplyDelete

I moderate comments blog posts over 14 days old. This keeps a lot of spam away. I generally am all right about moderating. Thanks for understanding.