How To Upload Sql To Host Youtube

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 speedily put it back online
• The database has been offline for a catamenia of time long plenty that you're comfortable it'south no longer used and you're ready to execute a backup and test restore (fifty-fifty though this should already have been done) and driblet the database
• Y'all've moved the database to another server and repointed to the new one, but want to guarantee a user can't admission the erstwhile i

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

Solution

Nosotros've seen iii unlike ways to have a database offline and we'll see three ways to bring it dorsum online. Information technology doesn't matter which method was used to take information technology offline, you tin utilize whatsoever of these 3 to bring information technology back online. Each method does the same affair.

The three methods are listed here with bullet points to help you lot make up one's mind which one to use to take a database offline or put it back online:

• SQL Server Direction Studio (SSMS)
  • Gratis
  • GUI based
  • Probably the easiest and quickest style if you just need to take 1 database offline
  • Doesn't crave you to be very comfortable using T-SQL or PowerShell
  • Gives you an easy way to run across if there are connections in the database before trying to take it offline
  • There is an easy checkbox option to strength connections out of a database
  • More Data - SQL Server Management Studio Tips
• T-SQL
  • Run in Query window that is part of Direction Studio
  • Even so pretty easy and quick
  • Just requires a minimal knowledge of T-SQL
  • Used if you're writing a T-SQL script to take ane or more databases offline
  • Nevertheless easy to force connections out of a database
  • More than Information - T-SQL Tips
• dbatools
  • Free PowerShell module
  • Still like shooting fish in a barrel and quick
  • Just requires a minimal knowledge of PowerShell and dbatools
  • Very easy to script out bringing one or more databases online
  • All the same easy to force connections out of a database
  • More than Data - PowerShell Tips

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

The post-obit versions were used in this tip:

• SQL Server 2017 CU19 Developer Edition
• SQL Server Management Studio xviii.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 Outset
2. All Apps
3. Microsoft SQL Server Tools
4. Microsoft SQL Server Direction Studio

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

1. Correct 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 pick

Or, only press F8 in SSMS.

Now, we'll connect to the database engine

1. Connect
2. Database Engine…

1. Server proper noun: (in our example I'g connecting to a named example phone call SQL2017 on my local automobile, so the total proper name of the SQL Server is .\SQL2017)
2. Authentication (presuming you're using Active Directory authentication)
3. Connect

The proper name of the database we're going to bring online is MyDatabase, that we tin come across the database proper name is appended with '(Offline)'.

1. Expand server dropdown
2. Aggrandize Databases dropdown
3. Correct click on database name, MyDatabase
4. Tasks (Take Offline is grayed out)
5. Bring Online

1. Bank check the 'Bring database online' box Condition cavalcade for 'Success'
2. Press the Close button

Wait in the Object Explorer to be certain the database no longer shows (Offline)

If non,

1. Correct click 'Databases'
2. Click on the Refresh option

Bring Database Online with T-SQL

We'll still utilise SSMS, only just the Query window to run the needed code.

1. Correct click on the server
2. Select the New Query choice

As we've seen the 'ALTER DATABASE' command to have the database offline, you lot tin approximate, 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 whatever errors, only here's one mode to verify the database is offline past 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 cavalcade for status

Bring Database Online with dbatools

Nosotros've seen how to bring a database online with SQL Server Direction Studio and T-SQL, and at present we'll see how to practise information technology with dbatools.

To get started:

1. Right click Start
2. Run

1. powershell
2. OK

Only equally the command to bring a database offline with T-SQL is the same except for ane switch, information technology'south the same thought with dbatools. All we need to do is bring the database online with Set up-DbaState with the -online switch instead of offline.

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

And we see the Condition shows online.

Side by side Steps

Nosotros'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 choice whichever one you lot desire based on if you just want to do information technology the quickest fashion, 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
• Place 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)

Related Articles

Popular Articles

Most the author

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

Commodity Concluding Updated: 2020-06-03

Source: https://www.mssqltips.com/sqlservertip/6419/how-to-bring-sql-server-database-online/

Posted by: whittendiente.blogspot.com

Share this post