By: Alejandro Cobar | Updated: 2022-07-06 | Comments | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > PowerShell
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.
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:
Start SQL Server instance
Make sure to run this with Administrator privileges or it might fail.
Get-Service -Name 'MSSQL$WINDOWS' | Start-Service
Output:
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:
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:
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:
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:
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:
Get list and properties for all user databases
Get-ChildItem "SQLSERVER:\SQL\localhost\DEFAULT\Databases"
Output:
Another example using a named instance:
Get-ChildItem "SQLSERVER:\SQL\DESKTOP-QUDLLRR\WINDOWS\Databases"
Output:
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.
Output:
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:
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:
Check execution history of a SQL Agent job
Get-SqlAgentJobHistory -ServerInstance "localhost" -JobName test
Output:
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!
About the author
Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-07-06
