Attaching and Detaching databases in SQL Server

There are a lot of options available to the database developer/DBA to move the databases from one instance to another instance.  Backup and restore is one of them, copying them over using DTS or SSIS is another one, having your DDL and DML copy scripts is yet another way to do it.  One of the popular ways to move databases to a different instance on the same server or to a different server is using sp_detachdb and sp_attach_db system stored procedures.  In this blog post, we will go through the steps involved in detaching and attaching databases.

To start with we will create test database. Please log on to Query analyzer or Management studio with proper authorization and issue following command to create the database. Just make sure that you have the proper privileges (CREATE DATABASE OR CREATE ANY DATABASE) assigned or the login that you are using is a member of the sysadmin or dbcreator fixed server roles.  Let’s keep it simple and create a sample TEST database:

USE MASTER
GO
CREATE DATABASE TEST
GO

Now run sp_helpfile system stored procedure to check how many files are created and location of the files.

USE TEST
GO
SP_HELPFILE

Here is the abbreviated output.

TEST        C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TEST.mdf
TEST_log    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ TEST_log.LDF

Output indicates that data file and log file are created in the default location. Once we created the database, we realized that there will not be enough space on c:\ drive so we need to move database files to the d:\ drive. We will achieve this using sp_detach_db and sp_attach_db.

sp_detach_db takes two or three arguments depending upon SQL Sever version. In SQL Server 2000, it takes only two arguments and in 2005 it sakes three arguments.

/* SQLServer 2000 */

sp_detach_db @dbname = ‘dbname’, @skipchecks = ‘skipchecks’

/* SQLServer 2005*/

sp_detach_db @dbname= ‘database_name’, @skipchecks = ‘skipchecks’,
@keepfulltextindexfile = ‘KeepFulltextIndexFile’

•    @dbname is name of the database we want to detach.
•    @skipchecks indicates whether we want to skip UPDATE STATISTICS during detach process or not. It is optional parameter with default value of NULL. If it set to ‘true’, it will skip the UPDATE STATISTICS, if it is set to false, it will run the UPDATE STATISTICS.
•    @ keepfulltextindexfile is also an optional parameter with a default value of true. If it is set to false, it will drop the full text index files and metadata associated with it. It will keep the metadata if option is null or set to true.

Major restriction to detach the datbase is that database should not be in use. You cannot detach the database which is being used actively. In order to detach the databas,e you need to kill all the connections and set the database in single user mode so that further connections are prohibited. For complete list of restricions, please refer to BOL. Following is an example of detaching the database in SQLServer 2005.

USE MASTER
GO
exec sp_detach_db ‘TEST’,'true’,'true’

Now let us move the file to different location on d: drive (D:\SQServer\Data) and then we will use sp_attach_db procedure to attach the database back. Only users who are member of sysadmin and dbcreator fixed server roles can execute this procedure.

sp_attach_db @dbname = ‘dbname’ @filename1 = ‘filename_n’ [,...16]

sp_attach_db can take upto 17 arguments. Database name and 16 file names. If there are more than 16 data files then one has to use CREATE DATABASE command with FOR ATTACH clause to attach the database (please note that in future versions, the CREATE DATABASE … FOR ATTACH command will be the preferred way to go as BOL states that the sp_attach_db command will be deprecated). Execute following procedure to attach the database back.

EXEC sp_attach_db @dbname = N’TEST’,
@filename1 = N’d:\SQLServer\Data\TEST.mdf’,
@filename2 = N’d:\SQLServer\Data\TEST_log.LDF’
GO

Once database is attached and used, you can remove the files from the previous location if you have copied it to the new location to avoid the confusion. Sp_helpfile will indicate the new location of data files. You can also run DBCC CHECKDB command to verify the sanctity of the attached database.

If database is replicated, then one should take little extra care during detach and attach process to make sure that you don’t run into any restricion scenarios. For replicataion consideration, please refer to BOL.  This is much a easier and faster way to move the database to different location compare to backup and restore.

Couple of things to keep in mind:

1)    If you are moving databases from one instance to another, you have to take care of the orphaned users in the database i.e. you will need to re-run a script to aling the logins with the users in the database.
2)    You should update the statistics with a FULL SCAN once the attach is done.
3)    Beware of different collation settings between the source and the destination instance.