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.
|Applies to: SQL Server (SQL Server 2008 through current version).|
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.
When you attach a SQL Server 2005 database that contains full-text catalog files onto a SQL Server 2016 server instance, the catalog files are attached from their previous location along with the other database files, the same as in SQL Server 2005. For more information, see Upgrade Full-Text Search.
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.
Do not use this procedure on a replicated database.
For information about how permissions are handled when a database is attached, see CREATE DATABASE (SQL Server Transact-SQL).
The following example detaches AdventureWorks2012 and then attaches one file from AdventureWorks2012 to the current server.
USE master; GO EXEC sp_detach_db @dbname = 'AdventureWorks2012'; EXEC sp_attach_single_file_db @dbname = 'AdventureWorks2012', @physname = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks2012_Data.mdf';