Export (0) Print
Expand All
Expand Minimize
1 out of 1 rated this helpful - Rate this topic

sp_attach_db (Transact-SQL)

Updated: 12 December 2006

Attaches a database to a server.

ms179877.note(en-US,SQL.90).gifImportant:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use CREATE DATABASE database_name FOR ATTACH instead. For more information, see CREATE DATABASE (Transact-SQL).

ms179877.note(en-US,SQL.90).gifNote:
To rebuild multiple log files when one or more have a new location, use CREATE DATABASE database_name FOR ATTACH_REBUILD_LOG.

ms179877.security(en-US,SQL.90).gifSecurity Note:
We recommend that you do not attach or restore databases from unknown or untrusted sources. Such databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database.

Topic link icon Transact-SQL Syntax Conventions


sp_attach_db [ @dbname= ] 'dbname'
        , [ @filename1= ] 'filename_n' [ ,...16 ] 
[ @dbname= ] 'dbnam '

Is the name of the database to be attached to the server. The name must be unique. dbname is sysname, with a default of NULL.

[ @filename1= ] 'filename_n'

Is the physical name, including path, of a database file. filename_n is nvarchar(260), with a default of NULL. Up to 16 file names can be specified. The parameter names start at @filename1 and increment to @filename16. The file name list must include at least the primary file. The primary file contains the system tables that point to other files in the database. The list must also include any files that were moved after the database was detached.

ms179877.note(en-US,SQL.90).gifNote:
This argument maps to the FILENAME parameter of the CREATE DATABASE statement. For more information, see CREATE DATABASE (Transact-SQL).

In the SQL Server 2005 Database Engine, any full-text files that are part of the database being attached will be attached with the database. To specify a new path of the full-text catalog, you can specify the catalog file by supplying a directory name without a file name. For more information, see Attach and Detach Full-Text Catalogs.

ms179877.note(en-US,SQL.90).gifNote:
You cannot attach a full-text catalog to the root directory.

0 (success) or 1 (failure)

The sp_attach_db stored procedure should only be executed on databases that were previously detached from the database server by using an explicit sp_detach_db operation or on copied databases. If you have to specify more than 16 files, use CREATE DATABASE database_name FOR ATTACH or CREATE DATABASE database_name FOR_ATTACH_REBUILD_LOG. For more information, see CREATE DATABASE (Transact-SQL).

Any unspecified file is assumed to be in its last known location. To use a file in a different location, you must specify the new location.

A database created by a more recent version of SQL Server cannot be attached in earlier versions.

ms179877.note(en-US,SQL.90).gifNote:
A database snapshot cannot be detached or attached.

When you attach a replicated database that was copied instead of being detached, consider the following:

  • If you attach the database to the same server instance and version as the original database, no additional steps are required.
  • If you attach the database to the same server instance but with an upgraded version, you must execute sp_vupgrade_replication to upgrade replication after the attach operation is complete.
  • If you attach the database to a different server instance, regardless of version, you must execute sp_removedbreplication to remove replication after the attach operation is complete.

For information about how permissions are handled when a database is attached, see CREATE DATABASE (Transact-SQL).

The following example attaches files from AdventureWorks to the current server.

EXEC sp_attach_db @dbname = N'AdventureWorks', 
    @filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf', 
    @filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf';

Release History

12 December 2006

New content:
  • Updated the "Remarks" section to document that a database created by a more recent version of SQL Server cannot be attached in earlier versions.

5 December 2005

New content:
  • Added the Security Note.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.