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 LocationFROM msdb.dbo.backupset AS bINNER JOIN msdb.dbo.backupmediafamily AS bf
ON b.media_set_id=bf.media_set_idORDER BY
b.backup_start_date DESCGO
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 LocationFROM msdb.dbo.backupset AS bINNER JOIN msdb.dbo.backupmediafamily AS bf
ON b.media_set_id=bf.media_set_idWHEREb.database_name = @DBName
ORDER BY
b.backup_start_date DESCGO
To get the List of all Databases which are not backed up till date
SELECTd.name [DB_Name]
FROMmaster.sys.databases d
LEFT JOIN msdb.dbo.backupset b
ON b.database_name = d.nameWHERE d.database_id IS NULLTo 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 lbsWHEREDATEDIFF(DAY,lbs.LastBackedUpOn ,CURRENT_TIMESTAMP) = @LastXDays
AND lbs.Rnk = 1ORDER BY
lbs.DBName DESCGO
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 bINNER 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 lbsWHERElbs.Rnk = 1
ORDER BY
lbs.DBName DESCGO
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 bINNER JOIN msdb.dbo.backupmediafamily AS bf
ON b.media_set_id=bf.media_set_idWHEREb.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 lbsWHERElbs.Rnk = 1
ORDER BY
lbs.DBName DESCGO
To get a list of Databases that were backed-up and do not currently exist
SELECT DISTINCT b.database_nameFROMmsdb.dbo.backupset b
WHERE DB_ID(b.database_name) IS NULLHope, 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.
