MSSQL Script for display backup history of selected database

Following script will display the history of selected database with important parameters like backup server name, backup size, backup duration, backup date, backup type (Full/Differential/Transaction Log), Recovery Model (Full/Bulk-logged/Simple) and backup path with the help of backupset table. The backupset table of msdb contains backup sets. The backup set maintains backup information for a successful operation.

The script doesn’t display all information in the tables. It contains enough backup set information – to make some decisions. Such as,
• Which Differential Base backup to restore? Can the differential be restored?
• How many differentials to roll forward?
• What backups are available to SQL database restore?
• Has there been a SQL backup?


SELECT bus.server_name as 'server',
bus.database_name as 'database',
CAST(bus.backup_size /1024/1024/1024 AS DECIMAL(10,2)) as 'Backup_Size_GB',
CAST(DATEDIFF(ss, bus.backup_start_date,bus.backup_finish_date) AS VARCHAR(4)) as 'Backup_Duration_Sec',
bus.backup_start_date as 'Backup_DateStart',
CAST(bus.first_lsn AS VARCHAR(50)) as LSN_First,
CAST(bus.last_lsn AS VARCHAR(50)) as LSN_Last,
CASE bus.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
WHEN 'F' THEN 'File\FileGroup'
WHEN 'G' THEN 'Differential File'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential Partial'
END AS Backup_Type,
bus.begins_log_chain ,
FROM msdb.dbo.backupset bus
INNER JOIN msdb.dbo.backupmediafamily bume ON bus.media_set_id = bume.media_set_id
WHERE bus.database_name = DB_NAME()
ORDER BY bus.backup_start_date DESC, bus.backup_finish_date DESC

For an example, consider following screenshot:

MSSQL Script for display backup history of selected database


<<Click here to see all posts>>



Leave a Reply

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

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.