Introduction

Managing databases in SQL Server requires a solid understanding of various methods for transferring and maintaining data. One essential process is attaching and detaching databases. This method allows administrators to move database files between different file systems or SQL Server instances. In this article, we’ll guide you through the steps to effectively attach and detach databases, highlighting the differences between this method and traditional backup and restore procedures. By mastering these techniques, you can streamline database management tasks and ensure efficient data handling.

Issue

How can you attach and detach databases in SQL Server? What distinguishes the backup and restore process from detach and attach?

Solution

Typically, the detach and attach method helps move database files within the file system or between servers. Many consider detaching a database, relocating the files to a new server, and then reattaching them to be the simplest approach for transferring a database to a different SQL Server instance. Alternatively, you can create a backup, transfer the backup file, and then restore the database on the new server. Your choice may depend on file sizes and network bandwidth, as these factors can influence speed. If there’s significant free space, you can enhance the efficiency of the detach and attach process by shrinking the files.

SQL Server Database Details

Before detaching a database, gather information about its underlying files. This information will be useful for transferring the files to a new location after detachment.

In SQL Server Management Studio, right-click on the database, select Properties, and navigate to the Files page. Here, you’ll find a list of database files along with the Path where those files are currently located.

To obtain this information using T-SQL, you can execute the following command for the database you wish to detach in this case, AdventureWorks2017.

USE [master]
GO
sp_helpdb N'AdventureWorks2017'
GO

This will display the path and file name details in the filename column for each file associated with the database.

Detach a SQL Server Database

A SQL Server database typically consists of at least two files: one data file (mdf) and one log file (ldf), though there can be multiple data files in some cases. When you want to transfer these files to another SQL Server instance, you cannot simply copy them, as they are tied to the current SQL Server instance and the file system recognizes them as open. Therefore, before moving these files, you need to detach the relevant database from the SQL Server instance.

You can detach a database in two ways: using SQL Server Management Studio (SSMS) or with T-SQL.

Detaching a SQL Server Database Using SSMS

First, right-click on the database in SSMS that you want to detach, then select Tasks > Detach..., as shown in the image below.

Then, you will be taken to the following screen.

.

On the screen above, you will find two checkboxes for detaching a database:

• Drop Connections: This option allows you to terminate any existing connections to the database, which is necessary for a successful detachment.
• Update Statistics: This option updates any outdated statistics before detaching the database.

In this instance, we will select both options, as shown below, and then click OK. Once the detachment is complete, the Status will change to Success.

Once the database is detached, it will no longer appear in the Databases list in SSMS, but the data and log files will remain in their original file system location.

Detaching a SQL Server Database Using T-SQL

The script below detaches the AdventureWorks2017 database using the default options. This process does not update the statistics and does not terminate 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

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

Attach a SQL Server Database Using T-SQL

Below is the T-SQL script for attaching a database along with its 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

Attaching a SQL Server Database Without a Log File

You can attach a database without a log file; in this case, a new log file will be created. If you plan to attach the database without copying the log file, ensure you execute a CHECKPOINT on the database prior to detachment.

Attaching a SQL Server Database Using SSMS

The process is similar to the one described earlier, but you will encounter 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 with a new log file generated.

Attaching a SQL Server Database Using T-SQL

You can use the following script to attach the database without a log file. When you do this, a new, empty log file will be created.

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

Upon running this, you will see the following message:

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

Conclusion

The Attach and Detach options are available only in SQL Server editions, not in Azure SQL Database or Azure SQL Data Warehouse. You cannot detach a database set up for Replication or Mirroring, nor can you detach a database that is in suspect mode. Additionally, only user databases can be detached; system databases like master, msdb, tempdb, and model are excluded. When attaching a database to a different version of SQL Server, its previous compatibility level will be preserved, and you can adjust this setting after the attachment is complete.