This article provides a short overview of attaching and de-attaching databases. The article does not mention any GUI functionality (on purpose of course) as the GUI is simplistic and self-explanatory. This, and other articles I write will focus on the “command line” (namely Query Analyser) to provide DBA’s with a reference point to turn back to when the GUI is not available for whatever reason.

Introduction

You can copy or duplicate a database very quickly using a variety of methods:

a)Shutdown instance, copy database files, and re-attach at destination server.

b)Offline the database, copy files, and re-attach at destination server.

c)De-attach the database, copy files, and re-attach at destination server.

Each method is described below. Remember that copying a database will not take the logins with it, as this information is stored in the master database. Fixing orphaned logins is very simple and is not discussed in this article.

Shutdown instance method

Simply shutdown the SQL Server instance, taking care when running multiple instances on the same server. When down, copy the database files to the other server (or copy/rename/move if it will be attached to the same server). As the database was cleanly shutdown there will be no issues with re-attaching so long as the copy and destination did not fail unexpectedly. If the instance did fail unexpectedly and you have no backups, re-attaching may still be possible (with the added risk of data corruption).

When using this method, the database will of course remain on the source server with no change what-so-ever to the source database.

Offline a Database method

Once the database is “offline”, you can copy its database files to a new server and re-attach. Use this method when shutting down the SQL Server instance is not desirable and you want to retain the database on the source server.

Reminder – User sessions will not be disconnected; this is applicable for sp_dboption and the ALTER database command. 

To take the instance offline:

Using the alter database statement (SQL Server 2k and beyond) is the preferred method. The rollback after statement will force currently executing statements to rollback after N seconds. The default is to wait for all currently running transactions to complete and for the sessions to be terminated. Use the rollback immediate clause to rollback transactions immediately.

When running the command with users connected you will get something like:

See the script http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=271 to kill off all connections for a database.

To confirm the offline status:

SELECT DATABASEPROPERTYEX('mydb', 'Status')

Attempting to connect to the database will give you:

Server: Msg 942, Level 14, State 4, Line 1

Database 'aa' cannot be opened because it is offline.

De-Attaching the database

If you want to completely remove the database from the master database and the SQL Server instance, use the deattach command rather than off-lining the database. 

When attempting to de-attach with Enterprise manager it will warn you when:

a)there are users connected to the database

b)replication is active

All user sessions must be disconnected and replication disabled before attempting the de-attachment. The command is:

exec sp_detach_db N'mydb', N'false'

The second parameter denotes wether to include a statistics collection before de-attaching the database. You must be a member of sysadmin to issue this command. Also note this error:

Server: Msg 7940, Level 16, State 1, Line 1

System databases master, model, msdb, and tempdb cannot be detached.

Funny enough, statistics are still updated before receiving this error. 

The de-attachment will remove the database from the sysdatabases table in the master database. The sysxlogins table will retain references to the de-attached database, therefore, you will need to either remove the login(s) or alter their default database connections, this is shown below.

exec sp_defaultdb N'myuser', N'master'

change default db from myuser to the master database.

exec sp_droplogin N'mytest'

drop login

Dropping logins is not straight forward, you need to either orphan the login from its associated database user or drop the user, otherwise you will get this message:

Server: Msg 15175, Level 16, State 1, Procedure sp_droplogin, Line 93

Login 'myuser' is aliased or mapped to a user in one or more database(s). Drop the user or alias before dropping the login.

You cannot remove users that own database objects, the standard drop user command is:

use [mydb]

exec sp_dropuser N’myuser’

Checking Files before Attaching

You should note that you cannot re-attach more than 16 files for a single database. Before attaching the database, issue the following commands over the primary file-group data file to get a listing of files that make up the database structure:

dbcc checkprimaryfile (N'E:\SQLServerData\MSSQL\Data\mydb_Data.MDF', 0)

Is the file a primary file-group MDF file?

dbcc checkprimaryfile (N'E:\SQLServerData\MSSQL\Data\mydb_Data.MDF', 2)

Get me the database name, version and collation

dbcc checkprimaryfile (N'E:\SQLServerData\MSSQL\Data\mydb_Data.MDF', 3)

Get a list of all files associated with the database. (original name)

Attaching the database

The sp_attach_db command allows you to re-attach your database onto the SQL Server instance. For example:

exec sp_attach_db 

N'mydb' , 

N'E:\SQLServerData\MSSQL\Data\new_aa_Data.MDF', N'E:\SQLServerData\MSSQL\Data\new_aa_Log.LDF'

The syntax is simple enough, the first being the name of the database to attach and its associated database files you have checked via the methods outlined previously. The database being attached must not already exist, you can also attach databases not previously de-attached so long as the database was closed successfully. 

Server: Msg 1801, Level 16, State 3, Line 1

Database 'mydb' already exists.

After re-attaching, especially if its on different server, you will need to fix orphaned logins via the command:

exec sp_change_users_login <see SQL Server BOL for parameter list>

Attaching a single file

The sp_attach_single_file_db command is quite powerful. It allows you to re-attach a database my specifying only its initial master data file, if your database had other data files (even in the primary file-group) they will be automatically re-attached (only to their previous destination though) for you by reading the sysfiles. This is all fine if you want the data files restored to the same location from which the database once existed along with the physical file name, but apart from that you have no control and will need to opt for sp_attach.

When re-attaching with this command, you have the ability for SQL Server to automatically recreate your log file so long as its not available for SQL Server to automatically re-attach when it looks up sysfiles. This method is handy when you want have massive log file and want to shrink it back to a manageable size. For example:

exec sp_attach_single_file_db N'MyxxDb' , N'E:\SQLServerData\MSSQL\Data\xx_Data.MDF'

<..shows the message below..>

Device activation error. The physical file name 'e:\sqlserverdata\MSSQL\data\xx_Log.LDF' may be incorrect. New log file 'E:\SQLServerData\MSSQL\Data\xxxx_log.LDF' was created.

The new file size E:\SQLServerData\MSSQL\Data\xxxx_log.LDF will be 512k.

This will not work if you have multiple log files thought, you will get an error like:

Server: Msg 1813, Level 16, State 2, Line 1

Could not open new database 'mytest'. CREATE DATABASE is aborted.

Device activation error. The physical file name 'e:\sqlserverdata\MSSQL\data\mt_Log.LDF' may be incorrect.

Device activation error. The physical file name 'e:\sqlserverdata\MSSQL\data\mt_2_Log.LDF' may be incorrect.

In this example, the database had a single data file with two log files. When I have only one file the attachment works file and re-create’s the log in the destination of the previous file (but has a modified size). Two or more files will result in the error.

Closing Statements

This article provided a basic overview of using a range of commands that the DBA should be comfortable with. Moving, copying, killing users and altering database status is something I do a lot of work as a development team DBA and hope this article bought back the confidence perhaps lost when using the GUI.