How to Bring SQL Server Database Online


By:   |   Updated: 2020-06-03   |   Comments (2)   |   Related: More > Database Administration

Problem

You need to bring an offline SQL Server user database back online.

These are a few reasons why the database was taken offline:

  • You were 99% sure a database was no longer used, but a user 'screamed' and now you can very quickly put it back online
  • The database has been offline for a period of time long enough that you're comfortable it's no longer used and you're ready to execute a backup and test restore (even though this should already have been done) and drop the database
  • You've moved the database to another server and repointed to the new one, but want to guarantee a user can't access the old one

To see how to take a SQL Server database offline read this tip - How to Take SQL Server Database Offline.

Solution

We've seen three different ways to take a database offline and we'll see three ways to bring it back online. It doesn't matter which method was used to take it offline, you can use any of these three to bring it back online. Each method does the same thing.

The three methods are listed here with bullet points to help you decide which one to use to take a database offline or put it back online:

  • SQL Server Management Studio (SSMS)
    • Free
    • 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
    • There is an easy checkbox option to force connections out of a database
    • More Information - SQL Server Management Studio Tips
  • 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
    • Used if you're writing a T-SQL script to take one or more databases offline
    • Still easy to force connections out of a database
    • More Information - T-SQL Tips
  • dbatools
    • Free PowerShell module
    • Still easy and quick
    • Just requires a minimal knowledge of PowerShell and dbatools
    • Very easy to script out bringing one or more databases online
    • Still easy to force connections out of a database
    • More Information - PowerShell Tips

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

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

Bring Database Online with SQL Server Management Studio (SSMS)

To start 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 click on the

  1. View menu
  2. Object Explorer option

Or, just press F8 in SSMS.

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 bring online is MyDatabase, that we can see the database name is appended with '(Offline)'.

  1. Expand server dropdown
  2. Expand Databases dropdown
  3. Right click on database name, MyDatabase
  4. Tasks (Take Offline is grayed out)
  5. Bring Online
Bring database online
  1. Check the 'Bring database online' box Status column for 'Success'
  2. Press the Close button
Online successful

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

Verify database online

If not,

  1. Right click 'Databases'
  2. Click on the Refresh option
Refresh Databases list

Bring Database Online with T-SQL

We'll still use SSMS, but just the Query window to run the needed code.

  1. Right click on the server
  2. Select the New Query option
New Query

As we've seen the 'ALTER DATABASE' command to take the database offline, you can guess, the T-SQL to bring the database online is simply a 'SET ONLINE' instead of 'SET OFFLINE' from our previous tip.

ALTER DATABASE [MyDatabase] SET ONLINE
  1. Highlight ALTER DATABASE [MyDatabase] SET  ONLINE
  2. F5 (or Execute)
Bring database online

We didn't get any errors, but here's one way to verify the database is offline by querying the sys.database view.

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 online

Bring Database Online with dbatools

We've seen how to bring a database online with SQL Server Management Studio and T-SQL, and now we'll see how to do it with dbatools.

To get started:

  1. Right click Start
  2. Run
Run
  1. powershell
  2. OK
Start PowerShell executable

Just as the command to bring a database offline with T-SQL is the same except for one switch, it's the same idea with dbatools. All we need to do is bring the database online with Set-DbaState with the -online switch instead of offline.

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

And we see the Status shows online.

Bring database online
Next Steps

We've seen how to bring a database back online 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 MSSQLTips with further information:



Last Updated: 2020-06-03


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





Comments For This Article




Wednesday, June 03, 2020 - 9:38:48 AM - Joe Gavin Back To Top

Bernard, I'm glad you appreciate that. Sometimes it's the only way.


Wednesday, June 03, 2020 - 9:10:41 AM - bernard black Back To Top

Your comment about a user "screaming" I'm sure hit home with many.  I'd say its happened about 4 times in my career.



download


Recommended Reading

How to read the SQL Server Database Transaction Log

How to rename a SQL Server database

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

How to determine SQL Server database transaction log usage

Renaming Physical Database File Names for a SQL Server Database





get free sql tips
agree to terms


Learn more about SQL Server tools