SQL Server Attach and Detach Database Examples


By:   |   Updated: 2020-01-08   |   Comments (2)   |   Related: More > Database Administration

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 between servers. 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, copy the backup file and restore the database to the new server.  Depending on the file sizes and network bandwidth dictates which option is the fastest.  One option to improve the time needed for the database detach \ attach would be to shrink the files, if they have a significant amount of free space.

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.

database properties

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.

detach sql database

Then, you will be taken to the following screen.

detach sql database

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 which needs 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.

detach sql database

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

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

The following 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

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, but 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 (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.

attach sql database

The following screen opens.

attach sql database

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

attach sql database

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

attach sql database

When you have the correct files, click OK and you will see the screen show a green checkmark and then the screen will close.

attach sql database

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 to a different path.

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 are planning to attach the database without copying the log file, make sure you execute a CHECKPOINT on the database before detaching the database.

SQL Server Attach Database Using SSMS

The process is similar to above, but you will see the message "Transaction log file not found. A new empty log file will be created".  You can then click OK and the database will be attached and a new log file will be created.

attach sql database

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, there is a record 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, so 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 option works only in SQL Server editions, not in Azure SQL Database, Azure SQL Data Warehouse version 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 and you can change this after attach is done.
Next Steps


Last Updated: 2020-01-08


get scripts

next tip button



About the author
MSSQLTips author Dinesh Asanka Dinesh Asanka is a 10 time Data Platform MVP and frequent speaker at local and international conferences with more than 12 years of database experience.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, January 30, 2020 - 10:40:20 AM - Greg Robidoux Back To Top

Thanks Scott, this has been updated.


Thursday, January 30, 2020 - 6:01:47 AM - Scott Back To Top

I know this is picky, but "..." is an ellipsis, not an ellipse. And I'm picky.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools