Backup script is something which is a very common need of a DBA in day to day life.This article will provide you with a base query to retrieve database backup information.
I am not able to give credit to anybody as I have seen lot of similar queries in the internet and so could not find the original writer.
Hmm! then what is the need of having another one in this blog? Well, I have experienced that sometimes a simple query suffice your need rather than a fancy one.
The below queries will help answering the following questions:
- Do you have recent database backups?
- What type of backup is being taken for particular databases?
- What is the backup size?
- Who has taken the backup or which application login id is being used to take backup?
- What is the Recovery model of the databases being backup up?
- How long it took to backup?
Backup details of all the databases of all backup types:
SELECT server_name, database_name, CASE WHEN type='D' THEN 'Full Backup' WHEN type='L' THEN 'Log Backup' WHEN type='I' THEN 'Diff Backup' END AS BackupType, backup_start_date, backup_finish_date, DATEDIFF(SS, backup_start_date, backup_finish_date)/60 AS [Duration(Minutes)], CAST(backup_size/1024/1024 AS NUMERIC(10,2)) AS 'BackupSize(MB)', user_name AS UserName FROM msdb..backupset ORDER BY backup_finish_date DESC
The following screen shot is to show you an example output:
You can use this as a base and tweak it here and there as per your requirements. Few examples are given below.
Retrieve the same information for particular database(s) and for a particular type:
SELECT server_name, database_name,
CASE
WHEN type='D' THEN 'Full Backup'
WHEN type='L' THEN 'Log Backup'
WHEN type='I' THEN 'Diff Backup'
END AS BackupType,
backup_start_date, backup_finish_date, DATEDIFF(SS, backup_start_date, backup_finish_date)/60 AS [Duration(Minutes)],
CAST(backup_size/1024/1024 AS NUMERIC(10,2)) AS 'BackupSize(MB)', user_name AS UserName FROM msdb..backupset
WHERE database_name IN ('master','model','msdb')
AND type='D'
ORDER BY backup_finish_date DESC
Include recovery model of the databases in the same output list:
SELECT b.server_name, b.database_name,
CASE
WHEN type='D' THEN 'Full Backup'
WHEN type='L' THEN 'Log Backup'
WHEN type='I' THEN 'Diff Backup'
END AS BackupType,
d.recovery_model_desc AS RecoveryModel,
b.backup_start_date, b.backup_finish_date, DATEDIFF(SS, b.backup_start_date, b.backup_finish_date)/60 AS [Duration(Minutes)],
CAST(b.backup_size/1024/1024 AS NUMERIC(10,2)) AS 'BackupSize(MB)', b.user_name AS UserName FROM msdb..backupset b
JOIN sys.databases d
ON b.database_name = d.name
WHERE database_name IN ('master','model','msdb')
AND type='D'
ORDER BY backup_finish_date DESC
Please let me know if it helps by leaving your comments below.
Further more you can go through the below link for some addition scripts:
You might also want to look at the below scripts:
Script to get Cluster Failover Time and Node Name
Script to List All Users with db_owner role in SQL 2000
