How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Serve
Posted Wednesday, 02 May 2007 by Michael Khanin
Digg!
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.

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

Moving user databases
The following example moves a database that is named mydb. This database contains one data file, Mydb.mdf, and one log file, Mydblog.ldf. If the database that you are moving has more data files or log files, specify the files 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 the sp_detach_db procedure does not list the files.
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.
Note Microsoft Knowledge Base article 922804 describes an issue for SQL Server 2005 databases on a network-attached storage. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

922804 (http://support.microsoft.com/kb/922804/) FIX: After you detach a Microsoft SQL Server 2005 database that resides on network-attached storage, you cannot reattach the SQL Server database

Consider this issue. Additionally, consider the permissions that are applied to a database when it is detached in SQL Server 2005. For more information, see the "Detaching and Attaching a Database" section of the "Securing Data and Log Files" 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/ms189128.aspx

Moving sample databases
To move the pubs sample database and the Northwind sample database in SQL Server 2000 or in SQL Server 7.0, or to move the AdventureWorks sample database and the AdventureWorksDW sample database in 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 2005 and SQL Server 2000

In SQL Server 2005 and in SQL Server 2000, you cannot detach system databases by using the sp_detach_db stored procedure. When you try to run the sp_detach_db 'model' statement, you receive the following error message:
Server: Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
To move the model database, you must start SQL Server together with the -c option, the -m option, and trace flag 3608. Trace flag 3608 prevents SQL Server from recovering 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:
-c -m -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:
http://msdn2.microsoft.com/en-us/library/ms190737.aspx (http://msdn2.microsoft.com/en-us/library/ms190737.aspx)
After you add the -c option, the -m option, and 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 -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

Moving the MSDB database

SQL Server 7.0

Note If you are using this procedure while moving the msdb and model databases, you must reattach the model database first, and then reattach the msdb database. 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:
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.

SQL Server 2005 and SQL Server 2000

To move the MSDB database, you must startt SQL Server together with the -c option, the -m option, and trace flag 3608. Trace flag 3608 prevents SQL Server from recovering any database except the master database. To add the -c option, the -m option, and trace flag 3608, follow the steps in the "Moving the model database" section. After you add the -c option, the -m option and 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 -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
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, you must reattach the model database first, and then reattach the msdb database. If you reattach the msdb database first, you receive the following error message when you try to reattach the model database:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
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:
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 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

Moving the master database

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

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



Reddit!Del.icio.us!Google!Facebook!Slashdot!Netscape!Technorati!StumbleUpon!Newsvine!Furl!Yahoo!Ma.gnolia!Free social bookmarking plugins and extensions for Joomla! websites! title=
 

Add comment

You have no rights to add a new comment. May be you need to register on the site.

[ Back ]