How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server

This article was previously published under Q224071
On This Page
SUMMARY
MORE INFORMATION
Prerequisites
Moving user databases
Moving pubs and Northwind
Moving MSDB (SQL Server 7.0)
Moving the MSDB database (SQL Server 2000 and SQL Server 2005)
If you are using SQL Server 2005
Moving the master database
Moving the model database
Moving tempdb
References
APPLIES TO

SUMMARY
This article describes how to change the location of the data and log files for any SQL Server 7.0, SQL Server 2000 or SQL Server 2005 database.
Back to the top

MORE INFORMATION
The steps that you must follow to change the location for some SQL Server system databases differ from the steps you must follow to change the location for user databases. These special cases are described separately.
Note SQL Server 7.0 system databases are not compatible with SQL Server 2000. Do not attach SQL Server 7.0 master, model, msdb or distribution databases to SQL Server 2000. If you are using SQL Server 2005, you can only attach databases of SQL Server 2005 to an instance.
All the examples in this article assume that SQL Server is installed in the D:\Mssql7 directory with all database and log files located in the default directory D:\Mssql7\Data. The examples move the data and log files for all the databases to E:\Sqldata.
Back to the top

Prerequisites
? Make a current backup of all databases, especially master, from their current location.
? You must have system administrator (sa) permissions.
? You must know the name and current location of all data and log files for the database.

Note You can determine the name and current location of all files used by a database by using the sp_helpfile stored procedure:use
go
sp_helpfile
go

? You should have exclusive access to the database being moved. If you experience problems during the process and cannot access a database you have moved or cannot start SQL Server, examine the SQL Server error log and SQL Server Books Online for more information about the errors experienced.

Back to the top

Moving user databases
The following example moves a database named mydb, which contains one data file, Mydb.mdf, and one log file, Mydblog.ldf. If the database you are moving has additional data or log files, specify them in a comma-delimited list in the sp_attach_db stored procedure. The sp_detach_db procedure does not change regardless of how many files the database contains because it does not list them. 1. Detach the database as follows:use master
go
sp_detach_db 'mydb'
go

2. Next, copy the data and log files from the current location (D:\Mssql7\Data) to the new location (E:\Sqldata).
3. Re-attach the database pointing to the files in the new location as follows:use master
go
sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'
go
Verify the change in file locations using sp_helpfile:use mydb
go
sp_helpfile
go
The filename column values should reflect the new locations.

Back to the top

Moving pubs and Northwind
Follow the same procedure for moving user databases.
Back to the top

Moving MSDB (SQL Server 7.0)
Note If you are using this procedure while moving the msdb and model databases, the order of reattachment must be model first and then msdb. If msdb is reattached first, it must be detached and not reattached until after model has been attached. 1. Make sure that the SQL Server Agent is not currently running.
2. Follow the same procedure for moving user databases.
Note If SQL Server Agent is running, the sp_detach_db stored procedure will not succeed and you will receive the following message:
Server: Msg 3702, Level 16, State 1, Line 0
Cannot drop the database 'msdb' because it is currently in use.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Back to the top

Moving the MSDB database (SQL Server 2000 and SQL Server 2005)
Note If you are using this procedure together with moving the msdb and model databases, the order of reattachment must be model first and then msdb. If msdb is reattached first, it must be detached and not reattached until after model has been attached.

In SQL Server 2000 and SQL Server 2005, system databases cannot be detached using the sp_detach_db stored procedure. Running sp_detach_db 'msdb' will not succeed and you will receive the following message:
Server: Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
To move the MSDB database on SQL Server 2000, follow these steps: 1. In SQL Server Enterprise Manager, right-click the server name and then click Properties.
2. On the General tab, click Startup Parameters.
3. Add a new parameter as "-T3608" (without the quotation marks).
After you add trace flag 3608, follow these steps: 1. Stop, and then restart SQL Server.
2. Make sure that the SQL Server Agent service is not currently running.
3. Detach the msdb database as follows:use master
go
sp_detach_db 'msdb'
go

4. Move the Msdbdata.mdf and Msdblog.ldf files from the current location (D:\Mssql8\Data) to the new location (E:\Mssql8\Data).
5. Remove the -T3608 trace flag from the startup parameters box in Enterprise Manager.
6. Stop and then restart SQL Server.
7. Reattach the msdb database as follows:use master
go
sp_attach_db 'msdb','E:\Mssql8\Data\msdbdata.mdf','E:\Mssql8\Data\msdblog.ldf'
go
Note If you try to reattach the msdb database by starting SQL Server together with trace flag -T3608, you receive the following error:
Server: Msg 615, Level 21, State 1, Line 1
Could not find database table ID 3, name 'model'.

Back to the top

If you are using SQL Server 2005
You can use SQL Server Configuration Manager to change the startup parameters of the SQL Server service. For more information about how to change the startup parameters, visit the following Microsoft Developer Network Web site:
http://msdn2.microsoft.com/en-us/library/ms190737.aspx (http://msdn2.microsoft.com/en-us/library/ms190737.aspx)
After you move the MSDB database, you may receive the following error message:
Error 229: EXECUTE permission denied on object 'ObjectName', database 'master', owner 'dbo'.
This problem occurs because the ownership chain has been broken. The database owners for the MSDB database and for the master database are not the same. In this case, the ownership of the MSDB database had been changed. To work around this problem, run the following commands in either the Isql.exe command-line utility or the Osql.exe command-line utility:
USE MSDB
Go
EXEC sp_changedbowner 'sa'
Go
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
272424 (http://support.microsoft.com/kb/272424/) Object ownership chain checking across databases depends on the login that is mapped to the object owners
Back to the top

Moving the master database
1. Change the path for the master data and log files in SQL Server Enterprise Manager.

Note You may also change the location of the error log here.
2. Right-click the SQL Server in Enterprise Manager and then click Properties.
3. Click Startup Parameters to see the following entries:-dD:\MSSQL7\data\master.mdf
-eD:\MSSQL7\log\ErrorLog
-lD:\MSSQL7\data\mastlog.ldf
-d is the fully qualified path for the master database data file.

-e is the fully qualified path for the error log file.

-l is the fully qualified path for the master database log file.
4. Change these values as follows: a. Remove the current entries for the Master.mdf and Mastlog.ldf files.
b. Add new entries specifying the new location:-dE:\SQLDATA\master.mdf
-lE:\SQLDATA\mastlog.ldf


5. Stop SQL Server.
6. Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata).
7. Restart SQL Server.

Back to the top

Moving the model database
To move the model database, SQL Server must be started with trace flag 3608 so that it does not recover any database except the master.

Note You will not be able to access any user databases now. You must not perform any operations, other than the following steps, while you use this trace flag. To add trace flag 3608 as a SQL Server startup parameter, follow these steps: 1. In SQL Server Enterprise Manager, right-click the server name, and then click Properties.
2. On the General tab, click Startup Parameters.
3. Add a new parameter as "-T3608" (without the quotation marks).

After you add trace flag 3608, following these steps: 1. Stop, and then restart SQL Server.
2. Detach the model database as follows:use master
go
sp_detach_db 'model'
go

3. Move the Model.mdf and Modellog.ldf files from D:\Mssql7\Data to E:\Sqldata.
4. Reattach the model database as follows:use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
go

5. Remove the -T3608 trace flag from the startup parameters box in the Enterprise Manager.
6. Stop and restart SQL Server. You can verify the change in file locations using sp_helpfile:use model
go
sp_helpfile
go


Back to the top

Moving tempdb
You can move tempdb files by using the ALTER DATABASE statement. 1. Determine the logical file names for the tempdb database by using sp_helpfile as follows:use tempdb
go
sp_helpfile
go
The logical name for each file is contained in the name column. This example uses the default file names of tempdev and templog.
2. Use the ALTER DATABASE statement, specifying the logical file name as follows:use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
go
You should receive the following messages confirming the change:
File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.

File 'templog' modified in sysaltfiles. Delete old file after restarting SQL Server.
3. Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.
4. Stop and restart SQL Server.

Back to the top

References
For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
274188 (http://support.microsoft.com/kb/274188/) PRB: "Troubleshooting orphaned users" topic in Books Online is incomplete
246133 (http://support.microsoft.com/kb/246133/) How to transfer logins and passwords between instances of SQL Server
168001 (http://support.microsoft.com/kb/168001/) PRB: User logon and/or permission errors after restoring dump

For more information, see the following books:
Microsoft Corporation
Microsoft SQL Server 7.0 System Administration Training Kit
Microsoft Press, 2001
Microsoft Corporation
MCSE Training Kit: Microsoft SQL Server 2000 System Administration (http://www.microsoft.com/MSPress/books/4885.asp)
Microsoft Press, 2001
Microsoft Corporation
Microsoft SQL Server 2000 Resource Kit (http://www.microsoft.com/MSPress/books/4939.asp)
Microsoft Press, 2001
Back to the top


--------------------------------------------------------------------------------

APPLIES TO
? Microsoft SQL Server 7.0 Standard Edition
? Microsoft SQL Server 2000 Standard Edition
? Microsoft SQL Server 2005 Standard Edition
? Microsoft SQL Server 2005 Express Edition
? Microsoft SQL Server 2005 Developer Edition
? Microsoft SQL Server 2005 Enterprise Edition
? Microsoft SQL Server 2005 Workgroup Edition