SQL Server Backup Monitoring with PowerShell
To continue this tip series, this PowerShell script will collect information to build an inventory of SQL Server database backups for all of the servers that have been registered for this monitoring solution.
This module will gather information about the current status of the very last database backups taken in each SQL Server instance. It will connect to each server in inventory.MasterServerList and capture the data for each of these instances. The connection to each SQL Server instance will be based on the settings in the inventory.MasterServerList table.
Note: this solution will focus on full, differential and transaction log backups. If your use case involves something like filegroup backups, then this solution has to be tweaked to fit your requirements.
In order to use this module, you need to create the core objects found here.
For this particular module, only one table will be created (in case it doesn’t exist), and it is the table to store the information of the database backups from each instance.
This is the structure of the table, so you can get an idea of what data will be stored. (If you want to add more fields to this table, make sure to adjust the structure within the PowerShell script and adapt the respective logic that will handle that additional field. Like, for instance, adding the capability to track filegroup backups.)
- inventory.Backups- this will store the latest backup information
for each database
- serverId - serverid ties back to inventory.MasterServerList
- database - database name
- state - current state of the database
- recovery_model - database recovery model
- last_full - date and time of last full backup
- time_since_last_full - time since last full backup
- full_backup_size - size of full backup
- full_backup_seconds_to_complete - time it took to complete
- full_backup_path - location of backup file
- last_diff - date and time of last differential backup
- time_since_last_diff - time since last differential backup
- diff_backup_size - size of differential backup
- diff_backup_seconds_to_complete - time it took to complete
- diff_backup_path - location of differential backup file
- last_tlog - date and time of last transaction log backup
- time_since_last_tlog - time since last transaction log backup
- tlog_backup_size - size of transaction log backup
- tlog_backup_seconds_to_complete- time it took to complete
- tlog_backup_path - location of backup transaction log backup file
- data_collection_timestamp - when data was last collected
The PowerShell script that creates the above object and inserts data into the inventory.Backups table is called:
The script has some validations that will help you check if some key elements are missing for the script to run successfully. For instance, it will confirm that the inventory.MasterServerList table exists and that it has at least 1 active instance registered to be able to have something to work with.
The PowerShell script should be stored in the centralized location. For this series we have been using "C:\Temp".
How to Use
Navigate to the folder where you created the files and you can run the PowerShell script as follows:
- Right click on Get-MSSQL-Instance-Backups.ps1 and select Run with PowerShell
- Open a command window and navigate to the folder where you saved the above files and run:
- Schedule this as a SQL Server Agent job to run the PowerShell script on a regular basis.
- Schedule this as a Windows Task Scheduler job to run the PowerShell script on a regular basis.
Check Creation of Database and Objects
After running the PowerShell script, we can see the objects that are created which will just be the new table inventory.Backups.
The script will store the information for the following fields in the described scale. Feel free to modify the script to adapt to different scales that your particular use case might require.
|Time since last backup||Days||Days||Minutes|
The tables below are a bit wide, so for the sake of legibility, I have trimmed it to show information about the full backups.
This is the first run, where there was only a backup for the master database.
Then I did backups for all of the databases and ran the collection again. Now we can see data for all of the databases.
Important note: the PowerShell script is crafted in a way that it will not store information from previous executions. This is something that would have to be modified by the end user if such capability is desired, the script only stores information from the latest execution.
Checking for Errors
To check for errors query the monitoring.ErrorLog table using the following query:
SELECT * FROM monitoring.ErrorLog WHERE script = 'Get-MSSQL-Instance-Backups'
If you’d like to know the SQL Server instance that had errors, you would have to issue the query like this:
SELECT CASE WHEN msl.instance = 'MSSQLSERVER' THEN msl.server_name ELSE CONCAT(msl.server_name,'\',msl.instance) END AS instance, e.script, e.message, e.error_timestamp FROM monitoring.ErrorLog e JOIN inventory.MasterServerList msl ON msl.serverId = e.serverId WHERE e.script = 'Get-MSSQL-Instance-Backups'
If you regularly collect this information from the infrastructure under your support, you can have visibility over things like:
Databases with no Full backup or Full backup older than 7 days
Which databases have either never had a full backup or the very last full backup took place more than 7 days ago?
The query would also apply for differential and transaction log backups, just change the fields in the WHERE clause and you’re good to go. With this information, you should be able to build a notification mechanism around it to let you know when this is happening on your system.
SELECT * FROM inventory.Backups WHERE time_since_last_full IS NULL OR time_since_last_full > 7
Database with the biggest backup size
You can order your complete result set to know which databases are the largest.
SELECT * FROM inventory.Backups ORDER by full_backup_size DESC
Databases that took longest time to backup
Also, which ones took the longest.
SELECT * FROM inventory.Backups ORDER by full_backup_seconds_to_complete DESC
- Download the PowerShell script to collect data for SQL Server Agent Jobs.
- Check out the other parts of this series
About the author
View all my tips
Article Last Updated: 2020-06-30