Microsoft KB Archive/224071

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

Article ID: 224071

Article Last Modified on 10/2/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Workgroup Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q224071



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

For more information about how to move system databases in SQL Server 2005, see the "Moving System Databases" topic in SQL Server Books Online. To view this topic, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/ms345408.aspx



MORE INFORMATION
The steps that you must follow to change the location for some SQL Server system databases differ from the steps that 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 folder. Additionally, the examples assume that all data files and log files are located in the default D:\Mssql7\Data folder. The examples move the data files and the log files for all the databases to the E:\Sqldata folder.

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

Note You can determine the name and the current location of all files that a database uses by using the sp_helpfile stored procedure: use ', 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 Transact-SQL statements. You can do this by using the Osql.exe command-line utility (SQL Server 7.0 and SQL Server 2000) or the Sqlcmd.exe command-line utility (SQL Server 2005): 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 Object ownership chain checking across databases depends on the login that is mapped to the object owners

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

Note You may also change the location of the error log here. Right-click the SQL Server in Enterprise Manager and then click Properties.  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. </li> Change these values as follows: <ol style="list-style-type: lower-alpha;"> Remove the current entries for the Master.mdf and Mastlog.ldf files.</li>  Add new entries specifying the new location: -dE:\SQLDATA\master.mdf -lE:\SQLDATA\mastlog.ldf </li></ol> </li> Stop SQL Server.</li> Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata).</li> Restart SQL Server.</li></ol>

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

Moving the tempdb database
You can move tempdb files by using the ALTER DATABASE statement. <ol>  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. </li>  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 that confirm the change:

Message 1

File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.

Message 2

File 'templog' modified in sysaltfiles. Delete old file after restarting SQL Server.

</li> Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.</li> Stop and then restart SQL Server.</li></ol>

Moving the master database and the Resource database
For more information about moving the master database and the Resource database, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/ms345408.aspx

<div class="references_section">