Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Citation - SQL Blog by Hemantgiri S. Goswami, SQL MVP

Hemantgiri S. Goswami is working as a Consultant at Surat, INDIA. He has been a Microsoft SQL Server MVP three years running; he also moderates multiple SQL Server community forums including http://www.sql-server-performance.com and http://www.sqlserver-qa.net. He actively participates and speaks at local user group events organized under the aegis of www.surat-user-group.org, DotNetChaps and CSI Surat chapters, of which he is a founding and active member. He regularly publishes article on his blog space http://www.sql-server-citation.com. He has recently taken up a new initiative - blogging about SQL in his native language Gujarat through his blog http://sqlservercitation-gujarati.blogspot.com. He is an Author of the book SQL Server 2008 High Availability.

SQL Server Database Backup Report using T-SQL

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.

Comments

Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...