Querying MSDB For Backup Information

Lori Brown shows how to query the msdb database and get information on backups, including whether the backups are compressed:

We recently started using a third party software to do our in-house SQL backups so that the backup files are stored in a redundant and safe place. To confirm that the software was indeed compressing backups as it stated it would, we wanted to see what each backup size actually was in SQL so that we could compare that to what the software was telling us.

SQL stores lots of handy backup information in msdb in the backupset and backupmediafamily tables.

There’s some useful information in these tables, though make sure you clean them up regularly or else msdb can become enormous.

Related Posts

Backups Are Faster With SQL Server 2017

Parikshit Savjani explains how the SQL Server team was able to use indirect checkpoints to improve backup performance: In RDBMS, whenever tables get larger, one of the technique to tune and optimize the scans on the tables is by partitioning it. With indirect checkpoints, we do the same. In indirect checkpoint, for every database which […]

Read More

Using The Restore-DbaDatabase Pipeline

Stuart Moore describes the updated Restore-DbaDatabase cmdlet: The biggest change is that Restore-DbaDatabase is now a wrapper around 5 public functions. The 5 functions are: Get-DbabackupInformation Select-DbabackupInformation Format–DbabackupInformation Test–DbabackupInformation Invoke-DbaAdvancedRestore These can be used individually for advanced restore scenarios, I’ll go through some examples in a later post. Stuart then provides additional information at the […]

Read More

Categories

July 2017
MTWTFSS
« Jun Aug »
 12
3456789
10111213141516
17181920212223
24252627282930
31