sp_attach_single_file_db (Transact-SQL)

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.

Security 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.

Transact-SQL Syntax Conventions

Syntax

sp_attach_single_file_db [ @dbname= ] 'dbname'
        , [ @physname= ] 'physical_name'

Arguments

• [ @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.

  Note

  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.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

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.

Do not use this procedure on a replicated database.

Permissions

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

Examples

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

USE master;
GO
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';

See Also

Reference

sp_detach_db (Transact-SQL)
sp_helpfile (Transact-SQL)
System Stored Procedures (Transact-SQL)

Other Resources

Detaching and Attaching Databases
Attach and Detach Full-Text Catalogs
Securing Data and Log Files

Help and Information

Getting SQL Server 2005 Assistance

Change History