How To Take SQL Server Database Offline/Online (2025) -

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.

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’.

How to Take SQL Server Database Offline/Online

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: