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

Backing Up SQL Server To S3

David Fowler shows how to back up SQL Server directly to an AWS S3 bucket: I’ve been having a little play around with AWS recently and was looking at S3 (AWS’ cloud storage) when I thought to myself, I wonder if it’s possible to backup up an on premise SQL Server database directly to S3? […]

Read More

Why Your Transaction Log Is Full: LOG_BACKUP

Jen McCown explains why you might get the error message “The transaction log for database ‘<your database>’ is full due to ‘LOG_BACKUP'”: Your transaction log is full. Both Microsoft, and about 100 articles and blogs have covered this topic, but let’s take a quick look anyway. Because, you know, it comes up all the time. Summary: This […]

Read More

Categories

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