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

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

Posted by getzeeshan on 24 April 2013

Hi There,

The above scripts are great!! but I have one quick query that I need help for you DBA's

I'm not very experienced with SQL Server but I'm looking for a solution.

My question is

--> we have 30 different Instances and some named instances on 30 different servers. On a daily basis we get 30 emails of backups being successful and its tedious for my team to go over each email most of them end up deleting it.

--> All I need is to get one consolidated backup report email for all 30 different instances instead of getting 30 emails.

--> for this I'm trying to run some queries from one of our central monitoring server - since this is the only sql server which can communicate to all 30 instances.

Please let me know the solution

getzeeshan@gmail.com

--Z

Posted by vinaypugalia on 24 April 2013

Hi,

A quick solution which I can think of now is -

1. Upon successful backup or backup failure, just log the details in the central server DB by - SSIS, scheduled jobs , any 3rd part applicaiton, etc.

2. Configure email on this central server which will send a consolidated report.

Hope, this helps!

Posted by w2bsqldba on 2 January 2014

Nice scripts.. Thanks..

but I found problem with and correct script is below:

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

 b.database_name IS NULL

Leave a Comment

Please register or log in to leave a comment.