Moving or Copying a Database | Inside Microsoft SQL Server 2005: The Storage Engine (Solid Quality Learning)

You might need to move a database before performing maintenance on your system, after a hardware failure, or when you replace your hardware with a newer, faster system. Copying a database is a common way to create a secondary development or testing environment. You can move or copy a database by using a technique called "detach and attach" or by backing up the database and restoring it in the new location.

Detaching and Reattaching a Database

You can detach a database from a server by using a simple stored procedure. Detaching a database requires that no one is using the database. If you find existing connections that you can't terminate, you can use the ALTER DATABASE command and set the database to SINGLE_USER mode using one of the termination options that breaks existing connections. Detaching a database ensures that no incomplete transactions are in the database and that there are no dirty pages for this database in memory. If these conditions cannot be met, the detach operation will not succeed. Once the database is detached, the entry for it is removed from the sys.databases catalog view and from the underlying system tables.

Here is the command to detach a database:

EXEC sp_detach_db <name of database>;

Once the database has been detached, from the perspective of SQL Server it's as if you had dropped the database. No trace of the database remains within the SQL Server instance. If you are planning to reattach the database later, it's a good idea to record the properties of all the files that were part of the database.

Note

The DROP DATABASE command removes all traces of the database from your instance, but dropping a database is more severe. SQL Server makes sure that no one is connected to the database before dropping it, but it doesn't check for dirty pages or open transactions. Dropping a database also removes the physical files from the operating system, so unless you have a backup, the database is really gone.

To attach a database, you can use the sp_attach_db stored procedure, or you can use the CREATE DATABASE command with the FOR ATTACH option. In SQL Server 2005, the CREATE DATABASE option is the recommended one because it gives you more control over all the files and their placement and because sp_attach_db is being deprecated. With sp_attach_db, the limit is 16 files. CREATE DATABASE has no such limitin fact, you can specify up to 32,767 files and 32,767 file groups for each database.

CREATE DATABASE database_name     ON <filespec> [ ,...n ]     FOR { ATTACH         | ATTACH_REBUILD_LOG }

Note that only the primary file is required to have a <filespec> entry because the primary file contains information about the location of all the other files. If you'll be attaching existing files with a different path than when the database was first created or last attached, you must have additional <filespec> entries. In any event, all the data files for the database must be available, whether or not they are specified in the CREATE DATABASE command. If there are multiple log files, they must all be available.

However, if a read/write database has a single log file that is currently unavailable and if the database was shut down with no users or open transactions before the attach operation, FOR ATTACH rebuilds the log file and updates information about the log in the primary file. If the database is read-only, the primary file cannot be updated, so the log cannot be rebuilt. Therefore, when you attach a read-only database, you must specify the log file or files in the FOR ATTACH clause.

Alternatively, you can use the FOR ATTACH_REBUILD_LOG option, which specifies that the database will be created by attaching an existing set of operating system files. This option is limited to read/write databases. If one or more transaction log files are missing, the log is rebuilt. There must be a <filespec> entry specifying the primary file. In addition, if the log files are available, SQL Server will use those files instead of rebuilding the log files, so the FOR ATTACH_REBUILD_LOG will function as if you used FOR ATTACH.

If your transaction log is rebuilt by attaching the database, using the FOR ATTACH_REBUILD_LOG will break the log backup chain. You should consider making a full backup after performing this operation.

You typically use FOR ATTACH_REBUILD_LOG when you copy a read/write database with a large log to another server where the copy will be used mostly or exclusively for read operations and will therefore require less log space than the original database.

Although the documentation says that you should use sp_attach_db or CREATE DATABASE FOR ATTACH only on databases that were previously detached using sp_detach_db, sometimes following this recommendation isn't necessary. If you shut down the SQL Server instance, the files will be closed, just as if you had detached the database. However, you will not be guaranteed that all dirty pages from the database were written to disk before the shutdown. This should not cause a problem when you attach such a database if the log file is available. The log file will have a record of all completed transactions, and a full recovery will be done when the database is attached to make sure the database is consistent. One benefit of using the sp_detach_db procedure is that SQL Server will know that the database was shut down cleanly, and the log file does not have to be available to attach the database. SQL Server will build a new log file for you. This can be a quick way to shrink a log file that has become much larger than you would like, because the new log file that sp_attach_db creates for you will be the minimum sizeless than 1 MB.

Backing Up and Restoring a Database

You can also use backup and restore to move a database to a new location, as an alternative to detach and attach. One benefit of this method is that the database does not need to come offline at all because backup is a completely online operation. Because this book is not a how-to book for database administrators, you should refer to the bibliography in the companion content for several excellent book recommendations about the mechanics of backing up and restoring a database and to learn best practices for setting up a backup-and-restore plan for your organization. Nevertheless, some issues relating to backup-and-restore processes can help you understand why one backup plan might be better suited to your needs than another, so I will discuss backup and restore in Chapter 5. Most of these issues involve the role of the transaction log in backup-and-restore operations.

Moving System Databases

You might need to move system databases as part of a planned relocation or scheduled maintenance operation. The steps for moving tempdb, model, and msdb are slightly different than for moving the master database or the resource database.

Here are the steps for moving an undamaged system database (that is not the master database or the resource database):

1.	For each file in the database to be moved, use the ALTER DATABASE command with the MODIFY FILE option to specify the new physical location.
2.	Stop the SQL Server instance.
3.	Physically move the files.
4.	Restart the SQL Server instance.
5.	Verify the change by running the following query:

SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'<database_name>');

If the system database needs to be moved because of a hardware failure, the solution is a bit more problematical because you might not have access to the server to run the ALTER DATABASE command. Here are the steps to move a damaged system database (other than the master database or the resource database):

Stop the instance of SQL Server if it has been started.

Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt.

-- If the instance is the default instance: NET START MSSQLSERVER /f /T3608 -- For a named instance: NET START MSSQL$instancename /f /T3608

For each file in the database to be moved, use the ALTER DATABASE command with the MODIFY FILE option to specify the new physical location. You can use either SQL Server Management Studio or the SQLCMD utility.
Exit SQL Server Management Studio or the SQLCMD utility.
Stop the instance of SQL Server.
Physically move the file or files to the new location.
Restart the instance of SQL Server. For example, run NET START MSSQLSERVER.

Verify the change by running the following query:

SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'<database_name>');

Moving the master Database and Resource Database

The location of the resource database (which is actually named mssqlsystemresource) depends on the location of the master database. If you move the master database, you must move the resource database files to the same directory.

Full details on moving these special databases can be found in Books Online, but I will summarize the steps here. The biggest difference between moving these databases and moving other system databases is that you must go through the SQL Server Configuration Manager.

To move the master database and resource database, follow these steps.

1.	Open the SQL Server Configuration Manager. Right-click on the desired instance of SQL Server, choose Properties, and then click on the Advanced tab.
2.	Edit the Startup Parameters values to point to the new directory location for the master database data and log files. You can optionally choose to also move the SQL Server error log files. The parameter value for the data file must follow the -d parameter, the value for the log file must follow the -l parameter, and the value for the error log must follow the e parameter, as shown here: -dE:\SQLData\master.mdf;- eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG; -lE:\SQLData\mastlog.ldf; -eE:\ SQLData\LOG\ERRORLOG;-
3.	Stop the instance of SQL Server and physically move the files for master and mssqlsystemresource to the new location.
4.	Start the instance of SQL Server in master-only recovery mode by using the /f and / T3608 flags, as shown previously.
5.	Using SQLCMD commands or SQL Server Management Studio, use ALTER DATABASE to change the FILENAME path for the mssqlsystemresource database to match the new location of the master data file. Do not change the name of the database or the file names. ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf'); ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf');
6.	Set the mssqlsystemresource database to read-only, and stop the instance of SQL Server.
7.	Move the resource database's data and log files to the new location.
8.	Restart the instance of SQL Server.
9.	Verify the file change for the master database by running the following query. Note that you cannot view the resource database metadata by using the system catalog views or system tables. SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID('master');