Check SQL Backups to Disk

Recently, I was tasked with a job to ensure all of our SQL Servers were configured to backup all databases to disk, and that all of these backup files existed in their backup location.

As we had over a dozen SQL server with multiple databases per SQL server this was not a task to be done manually.

So, I created the following script to get the job done. Basically it works as follows:

Check-SqlBackups -ComputerName SQLSERVER -SqlInstance INSTANCE -UserName USER -Password PASS

SQLInstance, Username, and Password are optional parameters.

This will enumerate all databases on SQLSERVER and  for each database tell me the state of the backup. Was it backed up to disk, was a Snapshot taken (e.g. via VSS) etc. An example of the result is as follows

There is still a bit more work I could do this to enumerate all SQL Instances on a SQL Server, fortunately these are few and far between.

Saved a world of pain 🙂 Hopefully it is of use to someone else, if not, it is a least in and handy place for me to get hold of again! 🙂