By: Dinesh Asanka | Comments (3) | 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.
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 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.
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.
The following screen opens.
Click on the Add button 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 this is not correct, you can use the ellipsis next to each file name to select a different file.
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 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.
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
- Check out these related tips:
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
