SQL Server - Take a database offline and detach it, then re-attach it and bring it online

In Programming Tags #sql-server Published 9/4/2013

The following two snippets will allow you to take a database offline then detach it and attach a database and bring it back online. It should be noted that while this would work Ok in a single user or controlled instance it could be problematic in production environments where multiple connections maybe active to the database. In this example I will use a database called “test_database”.

Take offline and detach:

SQL

-- Take the database offline, then detach it
ALTER DATABASE test_database SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
sp_detach_db test_database, 'true', 'false'
GO

Attach and bring online:

SQL

-- Attach the database then bring it back online
sp_attach_db N'test_database', N'C:Datatest_database.mdf', N'C:Datatest_database_log.ldf'
GO
ALTER DATABASE test_database SET ONLINE
GO

The syntax above may slightly differ between the mechanism you use to execute it. The above works for me in SQL Server Management Studio (SSMS). However, if I wanted to execute it from a .Net program using a SqlConnection/SqlCommand I would probably need to be semi-colons after each individual statement and then remove the “GO” lines.

Also, you may run into an instance where one or both of these commands fail with an error like “Exclusive access could not be obtained because the database is in use". To remedy this you can put the database in single user mode before the restore and then change it after. The following scripts should help with that.

SQL

-- Set the database to single user mode:
ALTER DATABASE test_database SET SINGLE_USER WITH ROLLBACK IMMEDIATE

SQL

-- Set the database back to multi user mode:
ALTER DATABASE test_database SET MULTI_USER

Leave a comment

Please note that we won't show your email to others, or use it for sending unwanted emails. We will only use it to render your Gravatar image and to validate you as a real person.