| title | sp_attach_single_file_db (Transact-SQL) | Microsoft Docs | ||
|---|---|---|---|
| ms.custom | |||
| ms.date | 03/14/2017 | ||
| ms.prod | sql-non-specified | ||
| ms.reviewer | |||
| ms.suite | |||
| ms.technology |
|
||
| ms.tgt_pltfrm | |||
| ms.topic | language-reference | ||
| f1_keywords |
|
||
| dev_langs |
|
||
| helpviewer_keywords |
|
||
| ms.assetid | 13bd1044-9497-4293-8390-1f12e6b8e952 | ||
| caps.latest.revision | 68 | ||
| author | JennieHubbard | ||
| ms.author | jhubbard | ||
| manager | jhubbard |
[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx_md]
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.
Important
[!INCLUDEssNoteDepFutureAvoid] We recommend that you use CREATE DATABASE database_name FOR ATTACH instead. For more information, see CREATE DATABASE (SQL Server Transact-SQL). Do not use this procedure on a replicated database.
Important
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 [!INCLUDEtsql] 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.
| Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssKatmai] through current version). |
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.
Note
This argument maps to the FILENAME parameter of the CREATE DATABASE statement. For more information, see CREATE DATABASE (SQL Server Transact-SQL).
When you attach a [!INCLUDEssVersion2005] database that contains full-text catalog files onto a [!INCLUDEssCurrent] server instance, the catalog files are attached from their previous location along with the other database files, the same as in [!INCLUDEssVersion2005]. For more information, see Upgrade Full-Text Search.
0 (success) or 1 (failure)
None
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 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 (SQL Server Transact-SQL).
The following example detaches [!INCLUDEssSampleDBobject] and then attaches one file from [!INCLUDEssSampleDBobject] 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';
Database Detach and Attach (SQL Server)
sp_detach_db (Transact-SQL)
sp_helpfile (Transact-SQL)
System Stored Procedures (Transact-SQL)