SQL SERVER 2005 - Take database Offline and close active connections

Aug 31

SQL SERVER 2005 - Take database Offline and close active connections

Working for a hosting company one of the things I have to do every so often is restore customers database backups.

When there is an existing database alrerady in place and in use, you will not be able to do this while there are still active connections open, and you will therefore need to close those conenctions first.

With SQL 2000 this was easy, from enterprise manager you could choose "detach database", and then clear the open connections, then cancel the detach, then restore your backup.

As with many things, this is not so easy with SQL Server 2005 as the "SQL Server management Studio Express" doesn't have this option, so here is how to do this with script.

EXEC sp_dboption N'mydb', N'offline', N'true'
or
ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK AFTER 30 SECONDS
or
ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK IMMEDIATE

Using the alter database statement (SQL Server 2k and beyond) is the preferred method. The rollback after statement will force currently executing statements to rollback after N seconds. The default is to wait for all currently running transactions to complete and for the sessions to be terminated. Use the rollback immediate clause to rollback transactions immediately.
After you restore your database, it will automatically come back online.