Know How to Use sp_attach_db Script in SQL Database

Introduction to sp_attach_db Stored Procedure

The sp_attach_db stored procedure is used when the user wants to move the MDF file from one location to another location. You can attach a single MDF file with this script. It's not necessary to detach your database every time to move the MDF file from one location to another. You can do this by stopping the SQL server before copying files to another server. The sp_attach_db procedure works in SQL Server 2005 and SQL Server 2008 but is not recommended.

The syntax of sp_attach_single_file_db stored procedure is:

sp_attach_single_file_db [@db_name=] ‘db_name’, [@physname=] ‘physical_name’

• [@db_name =] 'db_name' is the database name. 'db_name' is nvarchar (128).
• [@physname =] 'phsyical_name' is the database file name. 'phsyical_name' is nvarchar (260).

Attaching a Single-File in SQL Server Database

A single-file database is a database that has only one data file. When a database comprises only one data file, the database can be attached to an instance of SQL Server without using the transaction log file. When the data file will be successfully attached, SQL Server instance will create a new corresponding transaction log file automatically for the attached MDF file.

Example:

This is the example to attach the database which contains two files employe.mdf and employe_log.ldf from the C:\MSSQL\Data directory:

EXEC sp_attach_db @name = ‘employe’
    @filename1 = ‘C:\MSSQL\Data\employe.mdf
    @filename2 = ‘C:\MSSQL\Data\employe_log.ldf

While attaching the .mdf & .ldf file in SQL Server database, it is possible that you may get in trouble and face the error given below:

CREATE FILE encountered operating system error 5 (error not found) while attempting to open or create the physical file ‘C:\MSSQL\Data\employe.mdf. (Microsoft SQL Server, Error: 5123)

SQL Server error 5123 occurs when the process that is currently running on server has no proper rights to run the program or attach the database. It is also possible that the database that you want to attach is corrupt due to improper shutdown or virus & malware attacks, it is also possible that the database may be corrupted due to hardware issues like insufficient storage etc.

Things We Should Consider While Attaching a Database

While attaching the database, you must specify at least the name and physical location of the primary data file. If one or more of the database files have changed location since the database was detached, you must specify the name and physical location of these files in addition to the primary file.

The sp_attach_db stored procedure should only be executed on databases that were previously detached from the database server by using an explicit sp_detach_db operation or on copied databases.

Limitations of sp_attach_db Stored Procedure

1. One cannot attach a database which includes more than 16 files (data and log files combined).
2. An attach-and-detach operation disables cross-database ownership chaining option if used by the database.
3. An attach-and-detach operation turns database’s TRUSTWORTHY setting OFF if used by the database.
4. An attach-and-detach operation disables Service Broker setting, if used by the database.
5. Only members of the sysadmin and dbcreator fixed server roles can execute this procedure.

Conclusion

The sp_attach_db stored procedure is basically used in SQL Server 2000, 2005 and 2008 for attaching the SQL Server databases. There are some aspects that we should take care of while attaching the database and there are some limitations of sp_attach_db stored procedure which is also discussed in this article.