Detaching and attaching a database in SQL Server is a powerful technique that can be used for various purposes, such as transporting a database, recovering from a disaster, or upgrading from MSDE to SQL Server. In this article, we will explore the uses of detaching and attaching a database and provide a step-by-step guide on how to perform this trick.
Why Detach and Attach a Database?
The detach and attach procedure allows you to detach a database from one server and attach it to a different server or even the same server. There are several reasons why you might want to do this:
- Moving a database from one physical disk to another: If a database has grown beyond the capacity of its current disk, you can use the detach and attach procedure to move it to a new drive.
- Transporting the database to a client location or a new home: Detaching and attaching a database is much faster than backing up a database and restoring it to another server, which was the traditional method of moving databases.
- Upgrading an MSDE database to SQL Server: If you are using MSDE and want to upgrade to SQL Server, you can detach the database from MSDE and attach it to SQL Server.
- Rebuilding the master database in the event of a disaster: If your SQL Server crashes and the master database becomes corrupt, detaching and attaching the databases can help you recover from this situation.
The last point is particularly important. Imagine a scenario where your SQL Server crashes and you are unable to get the server operational due to a corrupt master database. After exhausting all other methods of recovering the master database, you may have to rebuild it. The best way to recover from this is to restore the last known good backup. As a last resort, you can bring up your SQL Server by following these steps:
- Rebuild the master database with the same sort order, code page, and collation.
- Attach the databases. Do not reinstall SQL Server in this scenario, as it may delete your old physical files that you will use to attach.
Detaching a Database
To detach a database, you can use the sp_detachdb system stored procedure. This stored procedure removes all entries of the database in the master database and makes the database portable. However, it is important to note that detaching a database should only be done in a crisis situation and not on a production database.
Here is an example of detaching a database:
sp_detach_db
@dbname = 'Northwind',
@skipchecks = 'false'
When the @skipchecks parameter is set to true, UPDATE STATISTICS won’t automatically be run before detaching the database.
Another method, which is not recommended but can be used, is to shut down SQL Server, copy the database and log files to a separate directory, and then start SQL Server again. This is done to avoid a sharing violation. After SQL Server is started, you can delete the database if needed and attach it back or move it from the file in the new directory.
Attaching a Database
Attaching a database can be done using the sp_attach_db system stored procedure. This procedure allows you to attach a database to the SQL Server instance.
Here is an example of attaching a database:
sp_attach_db
@dbname = 'Northwind',
@filename1 = 'C:\MSSQL7\DATA\Northwind_Data.MDF',
@filename2 = 'C:\MSSQL7\DATA\Northwind_Log.LDF'
After attaching the database, it is recommended to run sp_removedbreplication if you are running replication to remove replication settings. Additionally, it is a good practice to back up the attached database and run dbcc checkdb to ensure the database is in a healthy state.
Conclusion
The detach and attach feature in SQL Server is a valuable tool that can help you in various situations, such as moving databases, recovering from disasters, or upgrading from MSDE to SQL Server. By following the steps outlined in this article, you can safely detach and attach databases, ensuring the smooth operation of your SQL Server environment.
For more in-depth information on SQL Server inner workings, you can explore the book “Inside SQL Server”.