Get information about backups – SQL Server

backupset table in msdb can be used to get information about database backups. This table holds information about of type of backup being done, recovery_model, backup_size, backup_start_date, backup_finish_date etc.

Full backups are of type ‘D’ (Database Backup), log backups are type ‘L’ (Log Backup) and incremental are of type ‘I’ (Incremental Backup).

To get full backup details sorted by backup finish time:
SELECT * FROM msdb.dbo.backupset
WHERE 1=1
AND type = 'D'
ORDER BY backup_finish_date DESC

Results can be filtered based on database by including database name like this:
SELECT * FROM msdb.dbo.backupset
WHERE 1=1
AND type = 'D'
AND database_name = ''
ORDER BY backup_finish_date DESC

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>