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.

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.

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 <database_name> 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.

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.

Moving sample databases

To move the pubs and Northwind sample databases (SQL Server 7.0 or SQL Server 2000) or the AdventureWorks and AdventureWorksDW sample databases (SQL Server 2005), follow the same procedure for moving user databases.

Moving the model database

SQL Server 7.0

1.	Make sure that the SQL Server Agent is not currently running.
2.	Follow the same procedure for moving user databases.

SQL Server 2000 and SQL Server 2005

In SQL Server 2000 and in SQL Server 2005, system databases cannot be detached by using the sp_detach_db stored procedure. Running the sp_detach_db 'model' statement does not work, and you receive the following error message: To move the model database, you must start SQL Server together with trace flag 3608 so SQL Server does not recover any database except the master database.

Note You will not be able to access any user databases after you do this. 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 the following new parameter: -T3608

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 (MSDN) Web site: After you add trace flag 3608, follow these steps:

1.	Stop and then restart SQL Server.
2.	Detach the model database by using the following commands: use master go sp_detach_db 'model' go
3.	Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder to the E:\Sqldata folder.
4.	Reattach the model database by using the following commands: 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 in SQL Server Enterprise Manager or in SQL Server Configuration Manager.
6.	Stop and then restart SQL Server. You can verify the change in file locations by using the sp_helpfile stored procedure. For example, use the following command: use model go sp_helpfile go

Moving the MSDB database

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. Follow these steps:

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:

SQL Server 2000 and SQL Server 2005

To move the MSDB database, you must startt SQL Server together with trace flag 3608 so SQL Server does not recover any database except the master database. To add trace fkag 3608, follow the steps in the "Moving the model database" section. 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. Note If you try to reattach the msdb database by starting SQL Server together with trace flag -T3608, you may receive the following error message: Server: Msg 615, Level 21, State 1, Line 1 Could not find database table ID 3, name 'model'.
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 use this procedure together with moving the model database, you are trying to detach the msdb database while you detach the model database. When you do this, the order of reattachment must be the model database first and then the msdb database. If you reattach the msdb database first, you receive the following error message when you try to reattach the model databse: In this case, you must detach the msdb database, reattach the model database, and then reattach the msdb database,

After you move the MSDB database, you may receive the following error message: 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 Transact-SQL statements. You can do this by usingthe Osql.exe command-line utility (SQL Server 7.0 and SQL Server 2000) or the Sqlcmd.exe command-line utility (SQL Server 2005): For more information, click the following article number to view the article in the Microsoft Knowledge Base:

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.

Note If you are using SQL Server 2005, use SQL Server Configuration Manager to change the path for the master data and log files.

Moving the tempdb database

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.

References

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
For more information, see the following books: