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

Posted by Blake on 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