Detach And Attach Database In SQL Server

In my earlier articles, I’ve explained about taking database offline and bringing it online. In this article, I’ll explain about detach and attach database techniques. In several situations like moving a database from one server to another or one instance to another you may need to detach the database and attach is back to the same server instance or to another server. Another usage detach and attach database is to upgrade a database to a higher version of SQL Server. Below I’ve explained few of the methods to detach and then attach a database using Transact-SQL and SQL Server management Studio.

Detach And Attach Database Using T-SQL

Detach Database

You can detach a database by using system stored procedure sp_detach_db. The first parameter of the system procedure is @dbname. As the parameter name suggests, it’s the name of the database. The second parameter is @skipchecks. Providing a value of true to the parameter @skipchecks will skip the UPDATE STATISTICS while detaching the database.

Use master
GO
Exec dbo.sp_detach_db 'Database-Name', 'true';
GO

OR

Use master
GO
Exec dbo.sp_detach_db @dbname = N'Database-Name', @skipchecks = N'true';  
GO

Attach Database

Using CREATE DATABASE (SQL Server Transact-SQL)

The best way to attach an already detached database back to the same SQL Server or to a different server is by using a CREATE DATABASE statement along with FOR ATTACH. Here is an example statement:

--Syntex:
USE [master]
GO
CREATE DATABASE [Database-Name] 
	ON
		(FILENAME = 'Location-Of-MDF-File'),
		(FILENAME = 'Location-Of-LDF-file')
	FOR ATTACH;
GO

--Example:
USE [master]
GO
CREATE DATABASE [Test.MyTecBits.com] 
	ON
		(FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Test.MyTecBits.com.mdf'),
		(FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Test.MyTecBits.com_log.ldf')
	FOR ATTACH;
GO

Using SP_ATTACH_DB (Transact-SQL)

Using sp_attach_db system stored procedure to attach a detached database is another way. But, this method is deprecated from SQL Server 2008 onward. Even though sp_attach_db works in the current version of SQL Server (2016), it is not recommended. Instead use the create database method. Anyway, below is how to use the sp_attach_db procedure to attach a database. This may be helpful for those who are still using SQL Server 2005.

--Syntex:
EXEC sp_attach_db @dbname = N'Database-Name',
	@filename1 = N'Location-Of-MDF-File',
	@filename2 = N'Location-Of-LDF-file';  

--Example:
EXEC sp_attach_db @dbname = N'Test.MyTecBits.com',
	@filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Test.MyTecBits.com.mdf',
	@filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Test.MyTecBits.com_log.ldf';  

Using sp_attach_single_file_db (Transact-SQL)

Another deprecated method of attaching a database is by using the system stored procedure sp_attach_single_file_db. This method can be used of database file which are detached only by sp_detach_db method. In this method, we can attach a database using only the mdf file. T corresponding ldf log file will be created automatically. Here is an example.

--Syntex:
EXEC sp_attach_single_file_db 
	@dbname = 'Database-Name', 
    @physname = N'Location-Of-MDF-File';

--Example:
EXEC sp_attach_single_file_db 
	@dbname = 'Test.MyTecBits.com', 
    @physname = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Test.MyTecBits.com.mdf';

Detach And Attach Database Using Management Studio

Detach Database

1. Login to SQL Server Management Studio or SSMS.
2. From the Object Explorer, select the database you want to detach and right-click.
3. In the right-click menu go to Tasks >> Detach.
   
4. In the Detach Database pop-up window, select the check box under Drop Connection. You can either select the check box under Update Statistics or leave it. Finally press the OK button.
   
5. The database will be detached from the server.
6. To verify, refresh database folder in object explorer. The database is not visible in the object explorer.
7. If you go to the location where the mdf and ldf file are stored, you can see the files intact. You can use these files to attach the database back to the same server or to a different server.

Attach Database

1. From the Object Explorer, select the folder Database and right-click.
2. In the right-click menu, select Attach….
   
3. In the Attach Databases pop-up window,  press Add… button.
4. It will bring up a file search window. Go to the location of mdf file and select it. Once the file is selected, press OK.
   
5. In the Attach Databases window, the selected file is listed in the Databases to attach section and under the database details section. The log file details and location is also listed in the database details section. If you are attaching the database from the same server and the same file location, then you may not worry about the ldf file location. In-case, if you have moved the ldf file to another location, then you need to verify the file path and correct it if needed.
   
6. Finally press the OK button and refresh the Databases folder in object explorer. You will notice that the database is attached.

Related

• Difference between offline, detach and attach mode of databases.
• Moving a database to emergency status for troubleshooting.
• Drop a database or other objects inly if exists.

Reference

• Details about detaching and attaching database at Microsoft Docs.