SQL Server Backup Monitoring with PowerShell

By:   |   Updated: 2020-06-30   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | > Monitoring


Problem

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.

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.

Prerequisites

In order to use this module, you need to create the core objects found here.

Database Objects

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.)

Tables

  • 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

PowerShell Script

The PowerShell script that creates the above object and inserts data into the inventory.Backups table is called:

  • Get-MSSQL-Instance-Backups.ps1

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:

Option 1

  • Right click on Get-MSSQL-Instance-Backups.ps1 and select Run with PowerShell

Option 2

  • Open a command window and navigate to the folder where you saved the above files and run:
powershell "C:\temp\Get-MSSQL-Instance-Backups.ps1"

Option 3

  • Schedule this as a SQL Server Agent job to run the PowerShell script on a regular basis.

Option 4

  • 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.

database objects

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.

Full Differential Transaction Log
Backup Size Gigabytes Gigabytes Gigabytes
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.

query results

Then I did backups for all of the databases and ran the collection again.  Now we can see data for all of the databases.

query results

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'

Useful Cases

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 Scripts

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2020-06-30

Comments For This Article

















get free sql tips
agree to terms