Skip to main content

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

·177 words·1 min
system-administration tech mssql sql sysadmin technology
James Pettigrove
Author
James Pettigrove
Cloud Engineer with a focus on Microsoft Azure

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!

Related

Move Quorum Disk Witness to another node
·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
·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
·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.