Sql Server Database Management Studio For Mac
SQL Server Management Studio. Add SQL Server Management Studio to your desktop app catalog to allow privileged access service administrators to configure which users are allowed to connect to SQL database instances that reside on a remote application host system. Users can log in to remote desktop applications with specified credentials and without having to checkout a password.
By: Joe Gavin Updated: 2020-05-15 Comments 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
- Left click Start
- All Apps
- Microsoft SQL Server Tools
- Microsoft SQL Server Management Studio
Or alternatively, as the SQL Server Tools path will be appended to your users %PATH% variable:
- Right click Start
- Run
- Enter ssms in 'Open'
Object Explorer will likely open automatically, but if it doesn't do the following:
- View
- Object Explorer (or, just F8)
Now, we'll connect to the database engine.
- Connect
- Database Engine…
- 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)
- Authentication (presuming you're using Active Directory authentication)
- Connect
The name of the database we're going to take offline is called MyDatabase.
- Expand server dropdown
- Expand Databases dropdown
- Right click on database name - MyDatabase
- Tasks
- Take Offline
If the Status is 'Ready', there are no connections in the database.
- Check Status
- OK
But, if the status is 'Not Ready' as shown below.
- 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
- Click 'New Query'
- Run EXEC sp_who2 in the query window
- F5 (or click Execute button)
- 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.
- Type in 'kill x' for each spid and highlight it
- F5 (or click Execute)
- Highlight 'EXEC sp_who2'
- F5 (or click Execute)
- Verify process has been killed
Go back to the Object Explorer.
- Right click on database name, MyDatabase
- Tasks
- Take Offline
In the Take Database Offline window, do the following:
- Check Status
- 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.0.9, 06 May 2012)(ver. 1.5.4, 08 March 2011)(ver. 1.3.7, 15 September 2011)(ver. 2.4.8, 14 December 2011)(ver.
- Right click 'Databases'
- Refresh
Take SQL Server Database Offline with T-SQL
We'll still use SSMS, but just the Query window.
- Right click on the server
- New Query
- EXEC sp_who2 in the query window
- F5 (or click Execute)
- 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.
- Type in 'kill x' for each spid and highlight it
- F5 (or Execute)
- Highlight 'EXEC sp_who2'
- F5 (or click Execute)
- Verify process has been killed
Take the database offline with the following T-SQL:
- Highlight the statements
- F5 (or click Execute)
We didn't get any errors, but here's one way to verify the database is offline:
- Highlight the statements
- F5 (or click Execute)
- Find the database name
- 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.
Mysql-5.7.21-macos10.12-x8664.dmg MySQL for Mac is designed for enterprise organizations delivering business critical database applications. It gives corporate developers, DBAs and ISVs an array of new enterprise features to make more productive developing, deploying, and managing industrial strength applications. Mysql 5.7 macos.
To get started:
- Right click Start
- Run
- powershell
- 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.
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.
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.
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.
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
About the author
View all my tips