PowerShell – Script to Listing all Backups Taken with SQL Server

I always like to keep a number of scripts handy in my USB drive with my Laptop. These scripts are used for my internal use when I am suddenly looking for something interesting in customer environments. One of the scripts that I use is shown below. Many a times I have seen customers don’t really have any backup strategy. And when I start working with them, I call out some of the basic backup techniques that they need to be doing. That is why this script for listing all backups taken stays on my USB drive.

I have learnt and advised on a number of occasions that the backup strategy is not to be thought about in isolation but needs to be built keeping the restore strategy in mind. Your restore strategy and the RTO, RPO conversation with the stakeholders will decide how a typical backup plan needs to be done.

Let me get back to the fundamental problem statement that I wanted to share. The below powershell script is used to identify the backups taken for each of the databases.

PowerShell Script to Listing all Backups

Import-Module SQLPS
$ServerName = "BigPinal"    #"Replace with your servername"
$InstanceName = "Default"  #"Default"
$ConnectionString = "SQLSERVER:\SQL\"+$ServerName+"\"+$InstanceName+"\Databases"
dir $ConnectionString | %{$_.EnumBackupSets()} | ft

The output for the same would look like below:

You might be of the opinion on why you would use. This is a simple yet powerful script you can use to initially take a dump to understand the various backups. I take a quick look at this output with the list of databases to understand if there are databases which are not being backed up or if there is a plan for backups or not.

I know this is a simple task, but you will be surprised to how many still make basic mistakes in their environments. Do let me know what have you got from your environments and what were some of your findings? I am sure want to learn from you too.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.