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

MORE INFORMATION

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

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

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

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

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

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

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

USE MSDB 
Go 
EXEC sp_changedbowner 'sa' 
Go

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:
   1. Remove the current entries for the Master.mdf and Mastlog.ldf files.
   2. 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.

Moving the tempdb database

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