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.
