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:
-- Take the database offline, then detach it
ALTER DATABASE test_database SET OFFLINE WITH ROLLBACK IMMEDIATE
sp_detach_db test_database, 'true', 'false'
Attach and bring online:
-- 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'
ALTER DATABASE test_database SET ONLINE
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.
Set the database to single user mode:
ALTER DATABASE test_database SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Set the database back to multi user mode:
ALTER DATABASE test_database SET MULTI_USER