How to set a SQL database in Microsoft SQL Server to offline

After moving a database to a new server (via backup and restore utilities built into Microsoft SQL Server) I wanted to take the copy on the old server offline to ensure when testing the move I was only accessing the new database but due to my limited (but growing) knowledge I had no idea how to take a database offline.

The Management Studio application gives you the option to dismount the database but not set it to offline. To set a database to offline, open a new SQL query window and use the following SQL query:

ALTER DATABASE [databasename] SET OFFLINE WITH
ROLLBACK AFTER 30 SECONDS

Just replace databasename with the name of the database you would like to set to offline. This will set the database to offline after 30 seconds (to allow any transactions to close gracefully). Alternatively, if you are impatient we can kill any transactions and connections to the database immediately and set it to offline with the following:

ALTER DATABASE [databasename] SET OFFLINE WITH
ROLLBACK IMMEDIATE

Another skill for your sysadmin toolkit!

Move Quorum Disk Witness to another node

22 April 2013·226 words·2 mins

system-administration tech cluster hyper-v server-core sysadmin technology windows-server-2008-r2

I cannot say the Failover Cluster Manager is a very intuitive management interface.

Cisco Nexus 1000v virtual switch extension for Microsoft Hyper-V 3 Beta

21 April 2013·379 words·2 mins

system-administration tech cisco hyper-v nexus sysadmin technology windows-8 windows-server-2012

Exciting times for anyone in the Hyper-V camp or those looking.

How to setup and restore a object from the Active Directory Recycle Bin

15 April 2013·310 words·2 mins

system-administration tech active-directory powershell sysadmin technology

CTRL+Z, the undo button, the recycle bin, shadow copies; The human element in the world of IT can some times be our undoing; This goes along way to explain the push to automate EVERY facet of the our IT systems.

Related