How to Take SQL Server Database Offline

By: Joe Gavin   |   Updated: 2020-05-15   |   Comments (2)   |   Related: More > Database Administration

Problem

You need to take a SQL Server user database offline.

These are a few reasons you may need to do this and here are just a couple of them:

• You're 99% sure a database is no longer used, but you want to 'scream test' it for a while to be sure before dropping it
• You've moved a database to another server and think you have all the connections to it repointed to the new one, but want to guarantee a user can't access the old one

Solution

As with most problems there is more than one way to solve it. Three methods are listed here to help you decide which one to use:

• SQL Server Management Studio (SSMS)
  • Free download
  • GUI based
  • Probably the easiest and quickest way if you just need to take one database offline
  • Doesn't require you to be very comfortable using T-SQL or PowerShell
  • Gives you an easy way to see if there are connections in the database before trying to take it offline
  • You just need to check off a box to force connections out of a database
• T-SQL
  • Run in Query window that is part of Management Studio
  • Still pretty easy and quick
  • Just requires a minimal knowledge of T-SQL
  • Use if you're writing a T-SQL script to take one or more databases offline
  • Still easy to force connections out of a database with an extra line of T-SQL
• dbatools
  • Free PowerShell module
  • Still easy and quick
  • Just requires a minimal knowledge of PowerShell and dbatools
  • Very easy to script out taking one or more databases offline
  • Still easy to force connections out of a database with a switch

We'll step through each of the three methods, so you'll be able to decide which one is best for your purposes.

The following versions were used in this tip:

• SQL Server 2017 CU19 Developer Edition
• SQL Server Management Studio 18.4
• PowerShell 5.1.17763.1007
• dbatools   1.0.52

CAUTION: Backup any database you're taking offline and test restoring it somewhere else before continuing. You just never know.

Take SQL Server Database Offline with SQL Server Management Studio (SSMS)

To start SQL Server Management Studio

1. Left click Start
2. All Apps
3. Microsoft SQL Server Tools
4. Microsoft SQL Server Management Studio

Or alternatively, as the SQL Server Tools path will be appended to your users %PATH% variable:

1. Right click Start
2. Run

1. Enter ssms in 'Open'

Object Explorer will likely open automatically, but if it doesn't do the following:

1. View
2. Object Explorer (or, just F8)

Now, we'll connect to the database engine.

1. Connect
2. Database Engine…

1. Server name: (in our example I'm connecting to a named instance call SQL2017 on my local machine, so the full name of the SQL Server is .\SQL2017)
2. Authentication (presuming you're using Active Directory authentication)
3. Connect

The name of the database we're going to take offline is called MyDatabase.

1. Expand server dropdown
2. Expand Databases dropdown
3. Right click on database name - MyDatabase
4. Tasks
5. Take Offline

If the Status is 'Ready', there are no connections in the database.

1. Check Status
2. OK

But, if the status is 'Not Ready' as shown below.

1. Click on the 'Message' link

As we can see in our example, it's telling us there is one connection in the database we want to take offline. The message box tells us to close the connections or select the 'Drop All Active Connections' box. We can take care of this in one of two ways.

Option #1

1. Click 'New Query'

1. Run EXEC sp_who2 in the query window
2. F5 (or click Execute button)
3. Look under the DBName column for any referenced to the database we're taking offline and note the corresponding number under the SPID column

Next, run kill with the spid on any that are in the database. Here we only have one with a spid = 57.

1. Type in 'kill x' for each spid and highlight it
2. F5 (or click Execute)

1. Highlight 'EXEC sp_who2'
2. F5 (or click Execute)
3. Verify process has been killed

Go back to the Object Explorer.

1. Right click on database name, MyDatabase
2. Tasks
3. Take Offline

In the Take Database Offline window, do the following:

1. Check Status
2. OK

One thing to note here. We could have just checked 'Drop All Active Connections' to force connections out. But the SQL Server is keeping us from taking the database offline for a reason, which is to protect us from ourselves. It's just safer to see what connection(s) are in the database first. If you were accidentally attempting to take an active production database offline you would probably be able to catch the mistake before you made it.

Look in the Object Explorer to be sure the database shows (Offline).

If not, do the following:

1. Right click 'Databases'
2. Refresh

Take SQL Server Database Offline with T-SQL

We'll still use SSMS, but just the Query window.

1. Right click on the server
2. New Query

1. EXEC sp_who2 in the query window
2. F5 (or click Execute)
3. Look under the DBName column for any referenced to the database we're taking offline and note the corresponding number under the SPID column

Next, run the kill command with the spid on any that are in the database. Here we only have one with a spid = 55.

1. Type in 'kill x' for each spid and highlight it
2. F5 (or Execute)

1. Highlight 'EXEC sp_who2'
2. F5 (or click Execute)
3. Verify process has been killed

Take the database offline with the following T-SQL:

USE [master]
GO
ALTER DATABASE [MyDatabase] SET OFFLINE
GO			

1. Highlight the statements
2. F5 (or click Execute)

We didn't get any errors, but here's one way to verify the database is offline:

SELECT * FROM sys.databases			

1. Highlight the statements
2. F5 (or click Execute)
3. Find the database name
4. Check the state_desc column for status

Take SQL Server Database Offline with dbatools

We've seen how to take a database offline with SQL Server Management Studio and T-SQL, and now we'll see how to do it with dbatools that you download here.

To get started:

1. Right click Start
2. Run

1. powershell
2. OK

We'll run Get-DbaProcess, tell it the SQL Server name –SqlInstance (note:  .\InstanceName won't work here if you're using a named instance like I am) and pass the output to the Select command adding a pipe (|) and the three fields we want.

Get-DbaProcess -SqlInstance JGAVIN-L\SQL2017 -Database MyDatabase | Select Host, Login, Program			

As we can see, there is one connection in the database.

If we're sure we can kill these connection(s), we'll use the same switches for Get-DbaProcess, but will redirect the output to Stop-DbaProcess.

Get-DbaProcess -SqlInstance JGAVIN-L\SQL2017 -Database MyDatabase | Stop-DbaProcess			

The Status field shows we've killed the process(es).

We're now ready to take the database offline.

Use Set-DbaState with the same -SqlInstance and -Database switches. We won't pipe the output anywhere, but add the -Offline at the end.

Set-DbaDbState -SqlInstance JGAVIN-L\SQL2017 -Database MyDatabase -Offline			

You'll see the Status field shows it offline.

We've seen the safer way to check for what connections are in the database and how to kill them. And finally, here is how to force the connections out automatically by adding the -Force to the end of the previous command line.

Set-DbaDbState -SqlInstance JGAVIN-L\SQL2017 -Database MyDatabase -Offline -Force			

Next Steps

We've seen how to take a database offline using SSMS, T-SQL and the dbatools PowerShell scripts. All three of these methods do the same thing. You can pick whichever one you want based on if you just want to do it the quickest way, or how comfortable you are with T-SQL or PowerShell.

Here are some links to other MSSQL Tips with further info:

• Methods to determine the status of a SQL Server database
• Identify SQL Server databases that are no longer in use
• SQL Server Database Decommissioning Check List
• Retrieving SQL Server Database Properties with DATABASEPROPERTYEX
• Retrieve a List of SQL Server Databases and their Properties using PowerShell
• SQL Server Management Studio Overview (SSMS)

About the author

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips