How to Take SQL Server Database Offline


By:   |   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
Start SQL Server Management Studio (SSMS) from Shortcut

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

  1. Right click Start
  2. Run
Run
  1. Enter ssms in 'Open'
Start SQL Server Management Studio (SSMS) from executable

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

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

Now, we'll connect to the database engine.

  1. Connect
  2. Database Engine…
Connect
  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
Connect  to Database Engine

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
Start Database Offline

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

  1. Check Status
  2. OK
Database Offline screen

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

  1. Click on the 'Message' link
Shows if there are active connections in database

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'
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
Look for connections in database

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)
Kill connection(s)
  1. Highlight 'EXEC sp_who2'
  2. F5 (or click Execute)
  3. Verify process has been killed
Verify there are no more connections

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
Take database offline

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

Verify database offline

If not, do the following:

  1. Right click 'Databases'
  2. Refresh
Refresh Databases list

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
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
Look for connections in database

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)
Kill connection(s)
  1. Highlight 'EXEC sp_who2'
  2. F5 (or click Execute)
  3. Verify process has been killed
Verify there are no more connections

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)
Take database offline

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
Verify database offline

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
Run
  1. powershell
  2. OK
Start PowerShell executable

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			
Look for connections in database

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			
Kill connections

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			
Take database 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			
Force database offline
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:



Last Updated: 2020-05-15


get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips
Related Resources




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, May 22, 2020 - 8:40:10 AM - Joe Gavin Back To Top

Hi Gordon. Thanks for the question.

Afraid I'm unable to reproduce this. Are you using different PowerShell and / or dbatools versions per chance?   


Wednesday, May 20, 2020 - 5:50:52 AM - Gordon Feeney Back To Top

Hi Joe, I found your tip useful however I seem to need to kill processes in two stages rather than one. If I try and pipe a process to Stop-DbaProcess it fails. For example:

This works:

Stop-DbaProcess -SqlInstance MyInstance -Database MyDatabase

This fails:

Get-DbaProcess -SqlInstance MyInstance -Database MyDatabase | Stop-DbaProcess 

The error is "Cannot bind argument to "sqlserver" ..... At least one login, spid, host, program or database must be specified". 

I think I underatand the error - the database is missing from what's being piped to Stop-DBaProcess - but can't figure out why. If the Get-DbaProcess works then surely Stop-DbaProcess should as well. Any ideas?

Regards,

Gordon.



download


Recommended Reading

How to read the SQL Server Database Transaction Log

How to rename a SQL Server database

How to Attach a SQL Server Database without a Transaction Log and with Open Transactions

Different ways to determine free space for SQL Server databases and database files

How to determine SQL Server database transaction log usage





get free sql tips
agree to terms


Learn more about SQL Server tools