http://www.sqlservercentral.com/blogs/sql-server-citation-sql-blog-by-hemantgiri-s-goswami-sql-mvp/2012/10/17/sql-server-database-backup-report-using-t-sql/

Printed 2014/08/20 05:03AM

SQL Server Database Backup Report using T-SQL

By Hemantgiri S. Goswami, 2012/10/17

Today, I am going to share few very useful scripts which will report us on Database Backup from different view points. To get the List/History/Log of all the Successful Backups
SELECT 

b.machine_name,

b.server_name,

b.database_name as DBName,

b.backup_start_date,

b.backup_finish_date,

CASE

WHEN b.[type] = 'D' THEN 'Database'

WHEN b.[type] = 'I' THEN 'Differential database'

WHEN b.[type] = 'L' THEN 'Log'

WHEN b.[type] = 'F' THEN 'File or filegroup'

WHEN b.[type] = 'G' THEN 'Differential file'

WHEN b.[type] = 'P' THEN 'Partial'

WHEN b.[type] = 'Q' THEN 'Differential partial'

ELSE b.[type]

END Backup_Type,

b.expiration_date,

b.[user_name],

DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,

b.recovery_model,

b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,

bf.physical_device_name as Location

FROM

msdb.dbo.backupset AS b

INNER JOIN msdb.dbo.backupmediafamily AS bf

ON b.media_set_id=bf.media_set_id

ORDER BY

b.backup_start_date DESC

GO
To get a list of all successful Backups taken till date for a particular Database
DECLARE @DBName AS VARCHAR(100) = 'Your Database Name'
SELECT

b.machine_name,

b.server_name,

b.database_name as DBName,

b.backup_start_date,

b.backup_finish_date,

CASE

WHEN b.[type] = 'D' THEN 'Database'

WHEN b.[type] = 'I' THEN 'Differential database'

WHEN b.[type] = 'L' THEN 'Log'

WHEN b.[type] = 'F' THEN 'File or filegroup'

WHEN b.[type] = 'G' THEN 'Differential file'

WHEN b.[type] = 'P' THEN 'Partial'

WHEN b.[type] = 'Q' THEN 'Differential partial'

ELSE b.[type]

END Backup_Type,

b.expiration_date,

b.[user_name],

DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,

b.recovery_model,

b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,

bf.physical_device_name as Location

FROM

msdb.dbo.backupset AS b

INNER JOIN msdb.dbo.backupmediafamily AS bf

ON b.media_set_id=bf.media_set_id

WHERE

b.database_name = @DBName

ORDER BY

b.backup_start_date DESC

GO
To get the List of all Databases which are not backed up till date
SELECT

d.name [DB_Name]

FROM

master.sys.databases d

LEFT JOIN msdb.dbo.backupset b

ON b.database_name = d.name

WHERE

d.database_id IS NULL
To get the List of all Databases which are not backed up since last X days
DECLARE @LastXDays AS INT = 1

;WITH LatestBackupSet AS (

SELECT

b.database_name as DBName,

b.backup_start_date LastBackedUpOn,

b.[user_name],

ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk

FROM

msdb.dbo.backupset AS b

)

SELECT

lbs.DBName,

lbs.LastBackedUpOn,

lbs.[user_name]

FROM

LatestBackupSet AS lbs

WHERE

DATEDIFF(DAY,lbs.LastBackedUpOn ,CURRENT_TIMESTAMP) = @LastXDays

AND lbs.Rnk = 1

ORDER BY

lbs.DBName DESC

GO
To get a list of the Latest successful backups of all Databases
;WITH LatestBackupSet AS (

SELECT

b.machine_name,

b.server_name,

b.database_name as DBName,

b.backup_start_date,

b.backup_finish_date,

CASE

WHEN b.[type] = 'D' THEN 'Database'

WHEN b.[type] = 'I' THEN 'Differential database'

WHEN b.[type] = 'L' THEN 'Log'

WHEN b.[type] = 'F' THEN 'File or filegroup'

WHEN b.[type] = 'G' THEN 'Differential file'

WHEN b.[type] = 'P' THEN 'Partial'

WHEN b.[type] = 'Q' THEN 'Differential partial'

ELSE b.[type]

END Backup_Type,

b.expiration_date,

b.[user_name],

DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,

b.recovery_model,

b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,

bf.physical_device_name as Location,

ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk

FROM

msdb.dbo.backupset AS b

INNER JOIN msdb.dbo.backupmediafamily AS bf

ON b.media_set_id=bf.media_set_id

)

SELECT

machine_name,

server_name,

DBName,

backup_start_date,

backup_finish_date,

Backup_Type,

expiration_date,

[user_name],

Total_Time_in_Minute,

recovery_model,

Total_Size_GB,

Location

FROM

LatestBackupSet AS lbs

WHERE

lbs.Rnk = 1

ORDER BY

lbs.DBName DESC

GO
To get the Latest successful backup of a particular Database
DECLARE @DBName AS VARCHAR(100) = 'Your Database Name'



;WITH LatestBackupSet AS (

SELECT

b.machine_name,

b.server_name,

b.database_name as DBName,

b.backup_start_date,

b.backup_finish_date,

CASE

WHEN b.[type] = 'D' THEN 'Database'

WHEN b.[type] = 'I' THEN 'Differential database'

WHEN b.[type] = 'L' THEN 'Log'

WHEN b.[type] = 'F' THEN 'File or filegroup'

WHEN b.[type] = 'G' THEN 'Differential file'

WHEN b.[type] = 'P' THEN 'Partial'

WHEN b.[type] = 'Q' THEN 'Differential partial'

ELSE b.[type]

END Backup_Type,

b.expiration_date,

b.[user_name],

DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,

b.recovery_model,

b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,

bf.physical_device_name as Location,

ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk

FROM

msdb.dbo.backupset AS b

INNER JOIN msdb.dbo.backupmediafamily AS bf

ON b.media_set_id=bf.media_set_id

WHERE

b.database_name = @DBName

)

SELECT

machine_name,

server_name,

DBName,

backup_start_date,

backup_finish_date,

Backup_Type,

expiration_date,

[user_name],

Total_Time_in_Minute,

recovery_model,

Total_Size_GB,

Location

FROM

LatestBackupSet AS lbs

WHERE

lbs.Rnk = 1

ORDER BY

lbs.DBName DESC

GO
To get a list of Databases that were backed-up and do not currently exist
SELECT

DISTINCT b.database_name

FROM

msdb.dbo.backupset b

WHERE

DB_ID(b.database_name) IS NULL
Hope, the above given script will be of help to you. Also, I would like to request you to please add any relevant script which you feel would be useful as a comment.

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.