Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
SQL Server
Attaches a database that's only one data file to the current server. sp_attach_single_file_db can't be used with multiple data files.
Important
This feature will be removed in a future version of 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. Don't use this procedure on a replicated database.
Don't 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 = ] N'dbname'
, [ @physname = ] N'physname'
[ ; ]
Arguments
[ @dbname = ] N'dbname'
The name of the database to be attached to the server. @dbname is sysname, with no default.
[ @physname = ] N'physname'
The physical name, including path, of the database file. @physname is nvarchar(260), with no default.
This argument maps to the FILENAME parameter of the CREATE DATABASE statement. For more information, see CREATE DATABASE.
Note
When you attach a SQL Server 2005 (9.x) database that contains full-text catalog files onto a newer server instance of SQL Server, the catalog files are attached from their previous ___location along with the other database files, the same as in SQL Server 2005 (9.x). For more information, see Upgrade Full-Text Search.
Return code values
0 (success) or 1 (failure).
Result set
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.
Note
A database snapshot can't be detached or attached.
Don't use this procedure on a replicated database.
Permissions
For information about how permissions are handled when a database is attached, see CREATE DATABASE.
Examples
The following example detaches AdventureWorks2022 and then attaches one file from AdventureWorks2022 to the current server.
USE master;
GO
EXEC sp_detach_db @dbname = 'AdventureWorks2022';
EXEC sp_attach_single_file_db @dbname = 'AdventureWorks2022',
@physname =
N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks2022_Data.mdf';