You may come across situations to take one of the SQL Server databases offline. For me, I took older development and test versions of databases offline to avoid developers and testers using the wrong database. A database can be taken offline either using transact-SQL or by using SQL Server management Studio (SSMS). I prefer taking database offline before dropping or deleting them. This is to avoid any mistakes or loosing data prematurely. Couple of times I’ve to take back the offline database online to refer some testing data. After keeping the database offline for a month or so, I usually drop them from the server. Below are couple of simple methods to take the database offline. The next article will be about bringing back the database online and will write another article on attaching and detaching databases and database on emergency mode.
Taking Database Offline Using T-SQL
In management studio (SSMS), open a query window and execute the below T-SQL statement.
ALTER DATABASE [Database-Name] SET OFFLINE GO
I haven’t specified any rollback transactions in this ALTER DATABASE T-SQL statement. If the rollback termination option is not specified, this statement wait for all the transactions to complete and then takes the database offline. In case if there is a transaction which is neither committed or roll-backed, then the statement wat for it indefinitely. So, if you want to rollback all the transactions immediately, then use the termination statement WITH ROLLBACK IMMEDIATE in the alter statement as below.
ALTER DATABASE [Database-Name] SET OFFLINE WITH ROLLBACK IMMEDIATE GO
Or, if you want to allow the alter statement to wait for some time (say 15 seconds…) for the transactions to complete before taking the database offline, then use the termination statement WITH ROLLBACK AFTER 15 SECONDS along with the alter statement as below. In this case, if there any active transactions against the database, the alter statement will wait for 15 seconds for the transaction to commit or rollback. After 15 seconds, the active transactions are roll backed and the database is taken offline.
ALTER DATABASE [Database-Name] SET OFFLINE WITH ROLLBACK AFTER 15 SECONDS GO
Taking Database Offline using SSMS
- Login to SQL Server Management Studio.
- In the Object Explorer, select the database you want to take offline and right-click.
- In the right-click menu go to Tasks >> Take Offline.
- In the pop-up window, choose the check box under the Drop All Active Connections and click OK.
- The database will now taken offline.
- Refresh database folder in object explorer. You will see a red down arrow on the database icon and a text (Offline) next to the database name, indicating that the database is offline.
NOTE: Follow the steps in this article to bring back the database online.
Related
- Comparison between emergency mode, offline mode and detaching a database.
Reference
- Reference about ALTER DATABASE statement in Microsoft docs.