Attaching and Detaching Databases on SQL Server

Introduction

If you need to move a database, or database file to another server or
disk, and you do not want to backup the database, copy the backup on
another server, and then re-create the database from the backup, you can
use the sp_detach_db and sp_attach_db system stored procedures to detach
the database and then attach it again. Detaching and attaching databases can
be used when you want to move the database files to a different physical
disk from the disk that has run out of disk space and you want to expand the
existing file rather than add a new file to the database on another disk.

To move a database using detach and attach, you should make the following
steps:

  • Detach the database.
  • Move the database file(s) to the desired location on another server or disk.
  • Attach the database specifying the new location of the moved file(s).

After detaching, the database will be removed from SQL Server but will be
intact within the data and transaction log files that compose the database.
You can use these data and transaction log files to attach the database to
any instance of SQL Server, including the server from which the database
was detached. After attaching, the database will be available in exactly
the same state it was in when it was detached.

Detaching a Database

To detach an SQL Server 2000 database, you can use the sp_detach_db system
stored procedure. This stored procedure can also run UPDATE STATISTICS on
all tables before detaching. The syntax for sp_detach_db system stored
procedure is as follows:


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

In the above, the parameters to the stored procedure are as follows:

  • [@dbname =] ‘dbname’ is the database name.
    ‘dbname’is nvarchar(128), a default value is NULL.
  • [@skipchecks =] ‘skipchecks’
    The ‘skipchecks’ parameter indicates will be can UPDATE STATISTICS run
    or skipped. The ‘skipchecks’ is nvarchar(10), a default value is NULL.
    If ‘skipchecks’ is true, UPDATE STATISTICS is skipped. If ‘skipchecks’
    is false, UPDATE STATISTICS is run.

The following example detaches the pubs database and runs UPDATE STATISTICS
on all tables before detaching:


EXEC sp_detach_db ‘pubs’, ‘false’

Attaching a Database

When you attach a database, you must specify at least the name and
physical location of the primary data file. If one or more of the database
files have changed location since the database was detached, you must
specify the name and physical location of these files in addition to
the primary file.

To attach SQL Server 2000 database, you can use the sp_attach_db system
stored procedure. The syntax for sp_attach_db system stored procedure
is as follows:


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

In the above command:

  • [@dbname =] ‘dbname’ is the database name. dbname is nvarchar(128),
    a default value is NULL.
  • [@filename1 =] ‘filename_n’ is the database file name. filename_n
    is nvarchar(260), a default value is NULL. There can be up to 16 file
    names specified.

This is the example to attach the pubs database which contain two files
pubs.mdf and pubs_log.ldf from the C:MSSQLData directory:


EXEC sp_attach_db @dbname = ‘pubs’,
@filename1 = ‘C:MSSQLDatapubs.mdf’,
@filename2 = ‘C:MSSQLDatapubs_log.ldf’

Attaching a Single-File Database

A single-file database is a database that has only one data file. When
a database comprises only one data file , the database can be attached
to an instance of SQL Server 2000 without using the transaction log file.
When the data file will be attached, SQL Server will create a new
transaction log file automatically.

To attach a single-file database, you can use the sp_attach_single_file_db
system stored procedure. The syntax for sp_attach_single_file_db system
stored procedure is as follows:


sp_attach_single_file_db [ @dbname = ] ‘dbname’
, [ @physname = ] ‘physical_name’
  • [@dbname =] ‘dbname’ is the database name. ‘dbname’ is nvarchar(128),
    a default value is NULL.
  • [@physname =] ‘phsyical_name’ is the database file name.
    ‘phsyical_name’ is nvarchar(260), a default value is NULL.

This is the example to attach only one data file of the pubs database
from the C:MSSQLData directory:


EXEC sp_attach_single_file_db @dbname = ‘pubs’,
@physname = ‘C:MSSQLDatapubs.mdf’

Troubleshooting Lost Users

When you move a database to another server and there are existing users
in the detached database, you can lose users after attaching the database
on the new server. For example, if you move the Sales database from the
Product server to the Test server (for the test purposes) and the user
Alex exists in the Sales database, you should manually link the
relationship between the Alex user and the appropriate login on the
Test server.

You can use the sp_change_users_login system stored procedure to link
the specified user in the current database to the appropriate login.
The following example links the user Alex in the current database to the
Alex login:


EXEC sp_change_users_login ‘Update_One’, ‘Alex’, ‘Alex’

See SQL Server Books Online to get more information about the
sp_change_users_login stored procedure.

»


See All Articles by Columnist
Alexander Chigrik

Alexander Chigrik
I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

Latest Articles