PowerShell for the DBA - Performing DBA tasks using SQL Server CMDLETs

By:   |   Updated: 2022-07-06   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | More > PowerShell


Please do not scroll away - stay informed.
Dear Database Professional,

Did you know that MSSQLTips.com publishes new SQL Server content on a daily basis as well as offers free webinars and tutorials?

We know your day is hectic and you don't necessarily have time to research new topics and solutions every day, but we can keep you informed.

Take 30 seconds to register for our newsletter and look for free educational content to help you grow your career. >> REGISTER HERE <<

Thank you,
Greg Robidoux and Jeremy Kadlec (MSSQLTips.com Co-Founders)
Problem

Throughout this series we've been learning basic elements of PowerShell that will allow you, the star SQL Server DBA, to craft useful scripts that will help you in your daily tasks. Whether it is for automating tedious repetitive tasks or to deploy a solution (or set of solutions) to keep control of different aspects of the environment under your care. Nothing that we've learned throughout the series really means anything if we don't put it into practice, so let's get to it!

Solution

In this tutorial, I'll be presenting several examples of scripts that you can create to perform SQL Server DBA related tasks.

Initial considerations

  • I will assume that at this point, you either have a general understanding of PowerShell (knowledge that you acquired by yourself or by following along with this series so far).
  • I will also assume that you have installed the SQL Server PowerShell modules in your environment. If not, you can refer to my previous article where I talk about CMDLETs.
  • I will be running all my examples against a test SQL Server Developer Edition 2017 instance.

Important note: I will be presenting all the examples using Windows, but that shouldn't be a showstopper for you to follow along in other supported operating systems of your choosing.

The examples below are not presented in any particular order. Additionally, I posted the code along with screenshots, so you can follow along on your system.

What SQL Server instances are hosted on a server?

I'm a DBA and I've been handed a server that I should take care of. How can I figure out, using PowerShell, what instances are currently installed?

Get-Service | Where-Object -Property Name -match 'MSSQL*' 

Output:

This shows I have 3 instances deployed on this server and all 3 are running.

powershell query and results

Find basic information about SQL Server instances (e.g. version, product level)

Below I am passing in three instance names. Localhost is the default instance and the other 2 (SQL2 and WINDOWS) are named instances that we got from the command above.

Get-SqlInstance -ServerInstance @('localhost','.\SQL2','.\WINDOWS')

Output:

powershell query and results

Start SQL Server instance

Make sure to run this with Administrator privileges or it might fail.

Get-Service -Name 'MSSQL$WINDOWS' | Start-Service 

Output:

powershell query and results

Create a full SQL Server database backup

The command below will create a backup file for database "db1" in the default backup directory.

Backup-SqlDatabase -ServerInstance 'localhost' -Database 'db1'

Output:

powershell query and results

If you want your backup placed in a specific directory, then use the following. Also, make sure the SQL Server service account has enough permissions to write to the specified directory, otherwise, you'll get an access denied error.

Backup-SqlDatabase -ServerInstance 'localhost' -Database 'db1' -BackupFile 'C:\Users\aleja\Downloads\db1.bak'

Output:

powershell query and results

Create Differential Backup

If you want to take a differential backup of your database, then do it like this. Be careful with the name that you give to your file, because if it matches one that already exists, then it will overwrite it without any warning.

Backup-SqlDatabase -ServerInstance 'localhost' -Database 'db1' -BackupFile 'C:\Users\aleja\Downloads\db1.diff' -Incremental

Output:

powershell query and results

Create Transaction Log Backup

If you want to take a transaction log backup of your database, then do it like this. Be careful with the name that you give to your file, because if it matches one that already exists, then it will overwrite it without any warning.

Backup-SqlDatabase -ServerInstance 'localhost' -Database 'db1' -BackupFile 'C:\Users\aleja\Downloads\db1.trn' -BackupAction Log

Output:

powershell query and results

Create full backups of all user databases

The below command will backup all user databases in the SQL Server instance.

Get-ChildItem "SQLSERVER:\SQL\localhost\DEFAULT\Databases" | Backup-SqlDatabasese 

Output:

powershell query and results

Get list and properties for all user databases

Get-ChildItem "SQLSERVER:\SQL\localhost\DEFAULT\Databases"

Output:

powershell query and results

Another example using a named instance:

Get-ChildItem "SQLSERVER:\SQL\DESKTOP-QUDLLRR\WINDOWS\Databases"

Output:

powershell query and results

Test connectivity to SQL Server instances

This works without any issue because the Invoke-Sqlcmd cmdlet is attempting to connect using my Windows credentials. For it to work with SQL Server logins, you'd have to find a way around it; for instance, having the same SQL login created in each SQL instance, with the same credentials, and passing that to Invoke-Sqlcmd. I know, it is not the most clever way to address that, I'm just showing you a possibility.

Get-SqlInstance -ServerInstance @('localhost','DESKTOP-QUDLLRR\SQL2','DESKTOP-QUDLLRR\WINDOWS')|Invoke-Sqlcmd -HostName {Where-Object -Property 'Instance Name'} -Query "SELECT @@SERVERNAME AS instance,1 AS Connected"

Output:

powershell query and results

Get a list of all SQL Agent Jobs and if enabled

As you can see, I've specified that I want to connect directly to the msdb database so that I don't have to put msdb.dbo.sysjobs in the query. Aside from that, make sure that the account you are using to connect has enough privileges to access the msdb database or this will fail. This will only work against on-premises SQL Server Standard, Developer, Enterprise Editions, Azure SQL Managed Instance, and a SQL Server instance deployed in an Azure VM.

Invoke-Sqlcmd -HostName localhost -Database 'msdb' -Query "SELECT name, enabled from sysjobs"

Output:

powershell query and results

Enable a currently disabled SQL Agent job

Make sure that the account you are using to connect to the SQL Server instance has enough privileges to perform this action on the msdb database.

Invoke-Sqlcmd -HostName localhost -Database 'msdb' -Query "EXEC sp_update_job @job_name = N'test',@enabled=1"

Output:

powershell query and results

Check execution history of a SQL Agent job

Get-SqlAgentJobHistory -ServerInstance "localhost" -JobName test

Output:

powershell query and results

Summary

I think that all the examples presented can be a solid base for you to start using them in your daily routine. I hope you learned a trick or two!

Next Steps
  • In case you missed it, check out the previous article PowerShell for the SQL Server DBA - Pipelines
  • When you start from scratch, it might seem like a ton of work for you to have a bunch of custom PowerShell scripts within your arsenal. However, most of them will likely be crafted once and used many times (with small tweaks along the way, as required of course). So, in the end, the effort is worth it.
  • In the next article we will be taking a look at how to manage multiple SQL Server instances from one script, so stay tuned!



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Alejandro Cobar Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

View all my tips


Article Last Updated: 2022-07-06

Comments For This Article





download














get free sql tips
agree to terms