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

• For SQL Server 2000 32 bit editions, visit the following MSDN Web site:
  http://msdn.microsoft.com/en-us/library/aa176560(SQL.80).aspx (http://msdn.microsoft.com/en-us/library/aa176560(SQL.80).aspx)
• For SQL Server 2000 64 bit editions, visit the following MSDN Web site:
  http://msdn.microsoft.com/en-us/library/aa274567(SQL.80).aspx (http://msdn.microsoft.com/en-us/library/aa274567(SQL.80).aspx)
• For SQL Server 2005 editions, visit the following MSDN Web site:
  http://msdn.microsoft.com/en-us/library/ms143547(SQL.90).aspx (http://msdn.microsoft.com/en-us/library/ms143547(SQL.90).aspx)

Back to the top

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_name>
  go
  sp_helpfile
  go

• You should have exclusive access to the database that you are moving. If you experience problems during the process, and if you cannot access a database that you have moved or if you cannot start SQL Server, examine the SQL Server error log and SQL Server Books Online for more information about the errors that you are experiencing.

Back to the top

Moving user databases

1. Detach the database as follows:

   use master
      go
      sp_detach_db 'mydb'
      go

2. Next, copy the data files and the log files from the current location (D:\Mssql7\Data) to the new location (E:\Sqldata).
3. Re-attach the database. Point 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 by using the sp_helpfile stored procedure:

   use mydb
      go
      sp_helpfile
      go

   The filename column values should reflect the new locations.

Back to the top

Moving sample databases

Back to the top

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 2005 and SQL Server 2000

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:
   -c -m -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 -c -m -T3608 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

Back to the top

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 2005 and SQL Server 2000

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 -c -m -T3608 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 the -c option, the -m option, and trace flag 3608, 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

Back to the top

Moving the master database

SQL Server 7.0 and SQL Server 2000

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

SQL Server 2005

Back to the top

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 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.
3. Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.
4. Stop and then restart SQL Server.

Back to the top