Taking SQL Server Database Offline

In some circumstances it may take a long time to take the SQL server Database offline for a restore, when this occurs, do the following within the SQL Server Management Studio:

• Start a New Query within the server management studio by clicking on the New Query Button
• Copy and paste the following line (replace with the name of your database):

  • USE

    GO

    ALTER DATABASE

    SET OFFLINE WITH ROLLBACK IMMEDIATE

    GO

• Click Execute
• In some rare cases the above will not work, when this occurs, delete all lines from the current query and enter the line:
  • EXEC sp_who2
• Click Execute Again
• The results will list who has a lock on which database, look for your database name and note the SPID for any entries, you will need these numbers in the next step.
• Enter the following line for each SPID you noted above, each SPID should have a separate line (replacing with the SPID numbers noted above):
  • KILL
    • You should enter this on a separate line for each SPID number
• Click Execute one more time
• If the database is still locked, use the ALTER step above again and it should work immediately.