How to Bring SQL Server Database Online

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

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

1. View menu
2. Object Explorer option

Or, just press F8 in SSMS.

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

1. Check the ‘Bring database online’ box Status column for ‘Success’
2. Press the Close button

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

If not,

1. Right click ‘Databases’
2. Click on the Refresh option

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

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)

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

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

1. powershell
2. OK

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.

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:

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