In this article we will learn how to take SQL server database offline/online. There are some situations when we need to offline the database like
- When you have moved this DB to another server and you want to sure that no application is using this DB.
- When you want to take back of DB and active connection to this are not allowing you to take back up. You can take it offline and then bring online and then can take backup easily (not suggested for PROD environments).
- Before dropping DB you want to sure that no one is using this DB.
Table of Contents
How to make your DB offline
Open your SQL server by typing SSMS in run window. Select your DB and right click and then select ‘Tasks’ and then ‘Take offline’.
Now below window will open & yellow highlighted area indicates that some user is using this DB. Now you can either kill this user session or you can select “DROP All Active Connections” check box which will internally drop all active connections for you.
On running “exec Sp_who2”, it will list all active user and you can check your database name in ‘DBName’ column and kill this SPID, as shown in below image.
You as admin can kill the session of other users but if session is created by the same id with which you currently have logged in then following error may come on executing ‘KILL 54’.
Once you KILL all active connections ‘READY’ message will appear as shown in below image. If still someone using this DB then select the check box and move ahead, it will drop active connections and you will be good to go ahead to offline your DB.
Once offline done, DB starts appearing as show below.
How to bring DB online again
It is pretty east to make it online again. Right click on this DB and select ‘Tasks’ and the “Bring Online”. That’s it. Your DB will become online again for your use.
Details of all SQL database States
| ONLINE | The primary file group is online and Database is available for access. |
| OFFLINE | Database is unavailable. A database becomes offline by explicit user action and remains offline until it is again made online by same or different SQL user. |
| RESTORING | One or more files of the primary file group are being restored, or one or more secondary files are being restored offline. The database is unavailable. |
| RECOVERING | Database is getting recovered. The recovering process is a transient state. The database will automatically become online if the recovery succeeds and if the recovery fails, the database will become suspect. The database is unavailable. |
| RECOVERY PENDING | During recovery SQL Server has encountered a resource-related error. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. |
| SUSPECT | In this primary file group is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem. |
| EMERGENCY | EMERGENCY is primarily used for troubleshooting purposes. It happens when user change the database and set the status to EMERGENCY. Database become with single-user mode and may be repaired or restored additionally it is available with READ_ONLY & logging remains disabled. Only members of the “sysadmin” can set a database to the EMERGENCY state. |
I hope you have learned how to take SQL server database offline/online and then back to online. I believe you’ll be able to use this whenever required.
For more detail you can refer Microsoft Docs and also find such interesting articles in our SQL Section.
You may also like:
- SQL server Column Encryption using symmetric key.
- How to Update using Select statement in SQL Server.
- SQL Server Interview Questions and Answers.
- Import One Database to another in SQL server.
- Generate database scripts in SQL server.
- Different ways to search SQL objects in database.
- SQL CASE statement
- SQL IIF function
- QUOTENAME function in SQL
- How to Take SQL Server Database Offline/Online
- How to use SQL ORDER BY in Your Queries
- SQL Paging using OFFSET and FETCH
- How is data physically stored in a SQL database
- Fill Factor in SQL Server
- How to Search SQL Object in Complete Database
- How to generate SQL table script with data
- Delete Truncate and disk storage.