This documentation is archived and is not being maintained.

sp_attach_single_file_db (Transact-SQL)

Updated: 5 December 2005

Attaches a database that has only one data file to the current server. sp_attach_single_file_db cannot be used with multiple data files.

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). Do not use this procedure on a replicated database.,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_single_file_db [ @dbname= ] 'dbname'
        , [ @physname= ] 'physical_name'

[ @dbname = ] 'dbname'

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.

[ @physname = ] 'physical_name'

Is the physical name, including path, of the database file. physical_name is nvarchar(260), with a default of NULL.

This argument maps to the FILENAME parameter of the CREATE DATABASE statement. For more information, see CREATE DATABASE (Transact-SQL).

In the Microsoft 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.

0 (success) or 1 (failure)

Use sp_attach_single_file_db only on databases that were previously detached from the server by using an explicit sp_detach_db operation or on copied databases.

sp_attach_single_file_db works only on databases that have a single log file. When sp_attach_single_file_db attaches the database to the server, it builds a new log file. If the database is read-only, the log file is built in its previous location.

A database snapshot cannot be detached or attached.

Do not use this procedure on a replicated database.

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

The following example detaches AdventureWorks and then attaches one file from AdventureWorks to the current server.

USE master;
EXEC sp_detach_db @dbname = 'AdventureWorks';
EXEC sp_attach_single_file_db @dbname = 'AdventureWorks', 
    @physname = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf';

Release History

5 December 2005

New content:
  • Added the Security note.