Backup history for a single SQL Server database

12 June,2012 by Tom Collins

Use this script to display the sql backup history for a single database.

For Backup history read  SQL Server - Display restore history for a single database

The backupset table 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:

a)       Which Differential Base backup to restore? Can the Differential be Restored?

b)       How many Differentials to roll forward?

c)       What backups are available to SQL database Restore?

d)       Has there been a SQL Backup ?



--Display backup history for a single database 
use [myDatabase]
SELECT bus.server_name as 'server',
bus.database_name as 'database',
CAST(bus.backup_size /1024/1024/1024 AS DECIMAL(10,2)) as 'buSize_GB',
CAST(DATEDIFF(ss, bus.backup_start_date,bus.backup_finish_date) AS VARCHAR(4)) as 'buDuration_Sec',
bus.backup_start_date as 'buDateStart',
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 buType,
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

 Read More

SQL Server - Display restore history for a single database

SQL Server - BackUp All Databases

SQL Server BACKUP folder in windows registry

SQL Server - Predict SQL BACKUP DATABASE finish time with sys.dm_exec_requests

Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Backup history for a single SQL Server database | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer