SQL Server Attach and Detach Database Examples

Problem

How do you attach and detach databases in SQL Server? What is the difference between a SQL Server backup and restore vs. detach and attach?

Solution

Generally, detaching and attaching a SQL Server database is used to move database files in the file system or to a new server. If you want to move a database to a different SQL Server instance, in some opinions the easiest option would be detach the database, move the database files to the server and then attach the files.

The other option is to backup the database, copy the backup files and restore the database to a different server.

In this article, we look at the detach and attach method.

Preliminary SQL Server Database Information

Before detaching a database, you should get information about the underlying files supporting the database. This will be helpful when you need to copy the files to the new location after detaching.

Get Database File Information Using SQL Server Management Studio

In SQL Server Management Studio, right click on the database and select Properties and go to the Files page. You can see a list of database files and the Path where the files currently exist.

Get Database File Information Using T-SQL

To get this information using T-SQL you can run the following command for the database you want to detach, in this case AdvnetureWorks2017.

USE [master]
GO
sp_helpdb N'AdventureWorks2017'
GO

This will output path and file name information in the filename column for each file used for the database.

Detach SQL Server Database

A SQL Server database consists of at least two files: one data file (mdf) and one log file (ldf), but in some cases there can be multiple data files. When these files need to be moved to another SQL Server instance, you are unable to just copy these files since they are attached to a SQL Server instance and the file system marks the files as open. Therefore, before moving these files, you can detach the relevant database (files) from the SQL Server instance.

There are two options to detach a database: using SQL Server Management Studio (SSMS) or using T-SQL.

Detach a SQL Server Database Using SSMS

First, right click on the database in SSMS which you want to detach and select Tasks > Detach…as shown in the below image.

Then, you will be taken to the following screen.

In the above screen, there are two check boxes that you can use when detaching a database:

Drop Connections – There might be existing connections to the database that need to be dropped before the database detach can occur. If there are any open connections to the database the detach will fail.
Update Statistics – This will refresh any out-of-date statistics before detaching the database. If this option is unchecked, statistics will remain as they were at the time you detached the database.

In this case, we will select both options as shown below and select OK. After the detach occurs, the Status changes to Success.

After the database is detached, it will be removed from the Databases list in SSMS, but the data and log files for the database will remain in the same file system location.

Detach a SQL Server Database Using T-SQL

Here are options using T-SQL.

Simple Detach

The following script detaches the AdventureWorks2017 database with the default options. This does not update the statistics nor does it drop any existing connections to the database.

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks2017'
GO

Detach and Drop Existing Connections

This script detaches the AdventureWorks2017 database and first drops existing connections to the database.

USE [master]
GO
ALTER DATABASE [AdventureWorks2017] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks2017'
GO

Detach and Drop Existing Connections and Update Statistics

The following script detaches the database first dropping any connections and also updates the statistics.

USE [master]
GO
ALTER DATABASE [AdventureWorks2017] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks2017', @skipchecks = 'false'
GO

After detaching the database, the database is not available for users. However, the data and log files for the database will remain in the same file system location.

SQL Server Attach Database with Log File

After detaching the database using either of the methods above, the mdf, ldf, and ndf files (if exists, these are additional data files) need to be copied to the new location where you want to attach the database.

Attach a SQL Server Database Using SSMS

To attach the database, right-click on Databases and select Attach… on the SQL Server instance where you want to attach the database.

The following screen opens.

Click Add to find the mdf file that you want to attach, select the file, and click OK.

SSMS fill then show the associated files for the database as shown below. If it is not correct, use the ellipsis next to each file name to select a different file.

When you have the correct files, click OK. The screen shows a green checkmark first and then it will close.

Attach a SQL Server Database Using T-SQL

This is the T-SQL script for the attach database with data and log files. In this example we are attaching the database and specifying the location for each the database files. In this example the MDF and LDF files exist in the root of the D: drive.

USE [master]
GO
CREATE DATABASE [AdventureWorks2017] ON 
( FILENAME = N'D:\AdventureWorks2017.mdf' ),
( FILENAME = N'D:\AdventureWorks2017_log.ldf' )
 FOR ATTACH
GO

SQL Server Attach Database without Log File

You don’t need a log file to attach the database. In this scenario, the log file will be recreated. If you attach the database without copying the log file, make sure you execute a CHECKPOINT on the database before detaching it.

SQL Server Attach Database Using SSMS

The process is similar to the above, but you will see the message “Transaction log file not found. A new empty log file will be created”. Click OK. The database will be attached and a new log file will be created.

SQL Server Attach Database Using T-SQL

You can use this script to attach the database without a log file. When this is used, a new and empty log file is created.

USE [master]
GO
CREATE DATABASE [AdventureWorks2017] ON 
( FILENAME = N'D:\AdventureWorks2017.mdf' )
 FOR ATTACH
GO

When this is run you will get this message:

File activation failure. The physical file name "D:\AdventureWorks2017_log.ldf" may be incorrect.
New log file 'D:\AdventureWorks2017_log.ldf' was created.

Difference between SQL Server Backup and Restore vs. Attach and Detach

This is a very common interview question. Backup and Restore is the much cleaner option than the Attach and Detach. However, a backup and restore may take more time than using the attach and detach option. When a Backup and Restore is done, a record is created in the MSDB for the operations whereas for the attach and detach option there won’t be any record of the event.

Difference between SQL Server Detach and Offline

Taking a database offline leaves the database registration to the SQL Server instance intact. All you need to do is set it online to make it operational again. Basically, detaching and attaching is used to move database files between SQL Server instances, whereas with the Offline option, you are unable to move the files.

Important Notes

Attach and Detach options only work in SQL Server editions (not in Azure SQL Database and Azure SQL Data Warehouse versions of SQL Server).
Detach cannot be done when the database is set to Replication and Mirroring.
When the database is in suspect mode, you cannot detach the database.
You can only detach user databases, but not system databases such as master, msdb, tempdb and model.
When a database is attached to a different version of SQL Server, the previous compatibility will be maintained. You can change this after attach is done.

Next Steps

Check out these related tips:

Resources

Community

Subscribe