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

Testing Backups With dbatools

Constantine Kokkinos shows off a dbatools cmdlet to test the last full backup: This: Defines a list of two servers (PowerShell lists are as easy as “”,””) Pipes them to the Test-DbaLastBackup command. Which then: Gathers information about the last full backups for all of your databases on that instance. Restores the backups to the Destination with […]

Read More

Considerations With Third-Party Backup Tools

Gianluca Sartori gives us a laundry list of potential problems with third-party database backup solutions: 2. Potentially dangerous separation of duties Backup tools are often run and controlled by windows admins, who may or may not be the same persons responsible for taking care of databases. Well, surprise: if you’re taking backups you’re responsible for […]

Read More

Categories

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