Detach and Reattach a Database

Overview

Occasionally you may want to detach a database while you perform some operation on the SQL Server instance, then re-attach the database after you’ve finished.

One such example might be when modifying the SQL Server instance collation without affecting any user-databases, by running sqlsrvr.exe -q, as described by Solomon Rutzky in this article.

This post provides a simple script that generates the T-SQL commands to detach and attach a database. It creates a SQL Server Agent job for each action. I prefer to use a SQL Server Agent Job to do both the detach and the attach since SQL Server will adjust the filesystem security of the detached files based on the Active Directory account that does the detach operation. Using SQL Server Agent means anyone with proper access can run the detach, and someone else can run the attach later, without any “Access Denied” errors.

The script

Check the Microsoft Documentation for details about sp_detach_db.

See the rest of our SQL Server Tools here.