Detaching and Attaching SQL Server Databases: A Step-by-Step Guide for New DBAs

A Beginner's Guide to Detaching and Attaching a SQL Server Database

As a new DBA, you'll often need to move, backup, or restore databases. One common task is detaching a database from a SQL Server instance and then attaching it again, perhaps after moving its data files. In this blog post, we’ll walk through a practical example using a sample database called sampledatabase.

---

Table of Contents

1. Viewing Database File Information
2. Detaching the Database
3. Attaching the Database from New File Paths
4. Putting It All Together
5. Key Points for New DBAs

---

1. Viewing Database File Information

Before you detach a database, it’s crucial to know where its data and log files are stored. You can query the system view sys.master_files to see details about the files for a specific database.

Query:

SELECT * 
FROM sys.master_files 
WHERE database_id = DB_ID('sampledatabase');

What This Does:

• sys.master_files: A system view that stores information about all the physical files (data and log) associated with each database.
• DB_ID('sampledatabase'): Returns the database ID for sampledatabase.
• Result: You’ll see file names and their paths. For example:
  • Data file: C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\sampledatabase.mdf
  • Log file: C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\sampledatabase_log.ldf

Knowing these paths is essential because you’ll need to reference them when attaching the database later.

---

2. Detaching the Database

Detaching a database removes it from the current SQL Server instance without deleting the physical files. However, before detaching, you need to ensure that no other users are connected to the database.

Step 1: Set the Database to Single-User Mode

Setting the database to single_user mode ensures that no other sessions can interfere with the detach process. The WITH ROLLBACK IMMEDIATE option forces any active transactions to roll back immediately.

Command:

ALTER DATABASE sampledatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

What This Does:

• ALTER DATABASE: Changes the state of the database.
• SINGLE_USER: Limits access to one user at a time.
• WITH ROLLBACK IMMEDIATE: Terminates any other active connections to the database, ensuring that the detach process can proceed without issues.

Step 2: Detach the Database

After setting the database to single-user mode, you can detach it using the stored procedure sp_detach_db.

Command:

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

What This Does:

• USE [master]: Switches context to the master database, which is where the detach command is executed.
• sp_detach_db: Detaches the specified database (sampledatabase).
• Result: The database is detached, and its files remain on disk, ready to be moved or reattached.

---

3. Attaching the Database from New File Paths

Once the database is detached, you can attach it again—this is useful if you’ve moved the physical files to a new location or are restoring them on another server.

Command:

USE [master];
GO
CREATE DATABASE [sampledatabase] ON 
( FILENAME = N'C:\bittu\sampledatabase.mdf' ),
( FILENAME = N'C:\bittu\sampledatabase_log.ldf' )
FOR ATTACH;
GO

What This Does:

• CREATE DATABASE [sampledatabase] ON: Initiates the creation of a database using existing files.
• FILENAME: Specifies the new paths of the database's data and log files. In this example, the files are located at C:\bittu\sampledatabase.mdf and C:\bittu\sampledatabase_log.ldf.
• FOR ATTACH: Tells SQL Server to attach the database using the existing files rather than creating new ones.
• Result: The database is reattached to the SQL Server instance, now using the new file locations.

---

4. Putting It All Together

Let’s summarize the entire process with all the commands:

1. View File Information:

   SELECT * 
   FROM sys.master_files 
   WHERE database_id = DB_ID('sampledatabase');
   

   This confirms where the files are located before making any changes.

2. Set the Database to Single-User Mode:

   ALTER DATABASE sampledatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
   

3. Detach the Database:

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

4. Attach the Database from a New Location:

   USE [master];
   GO
   CREATE DATABASE [sampledatabase] ON 
   ( FILENAME = N'C:\bittu\sampledatabase.mdf' ),
   ( FILENAME = N'C:\bittu\sampledatabase_log.ldf' )
   FOR ATTACH;
   GO
   

By following these steps, you have successfully moved the database files from one location to another and reattached the database to your SQL Server instance.

---

5. Key Points for New DBAs

• Always Verify File Locations:
  Use the query on sys.master_files to ensure you know exactly where your database files are stored. This is especially important when planning to move or attach databases.

• Ensure No Active Connections:
  Before detaching, setting the database to single-user mode with WITH ROLLBACK IMMEDIATE is crucial. It prevents any interference by terminating active connections.

• Use the Correct Commands in Sequence:
  Always detach the database before trying to attach it again. The sp_detach_db procedure cleanly removes the database from SQL Server without deleting the files.

• Plan File Paths for Attach:
  When attaching the database, specify the correct new file paths. This is vital if you have moved the database files to a different folder or drive.

• Test in a Non-Production Environment:
  If you’re new to detaching and attaching databases, practice these commands in a development or test environment first. This will build your confidence and help avoid potential issues in production.

---

Conclusion

Detaching and attaching a database are fundamental skills for a SQL Server DBA. Whether you're moving a database, performing maintenance, or troubleshooting, understanding these processes will help ensure your databases remain available and performant. By following the step-by-step guide in this blog post, you can confidently perform these tasks, knowing you’re using the correct commands and best practices.

If you have any questions or need further clarification on any of these steps, feel free to leave a comment below. Happy database managing!

---

Remember: Practice is key. The more you work with these commands, the more natural they will become. Good luck on your DBA journey!