One of my colleague was looking for a script to identify the backup details for one his databases. Here is a very simple script to identify database backup details.
1. To confirm all the backup jobs are running fine at the defined intervals
2. To confirm the necessary backup strategies are in place For ex: For FULL recovery model requires frequent log backups to avoid the space issues
3. To identify the backup startdate and finishdate for backups
4. To identify the device name to which backups are initiated
5. To identify who initiated the backups(username)
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, BkpSet.server_name, BkpSet.database_name, BkpSet.recovery_model, BkpSet.backup_start_date, BkpSet.backup_finish_date, BkpSet.expiration_date, CASE BkpSet.type WHEN 'D' THEN 'Database(FULL)' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Log' END AS backup_type, BkpSet.backup_size, BkpMF.logical_device_name, BkpMF.physical_device_name, BkpSet.name AS backupset_name, BkpSet.description, user_name FROM msdb.dbo.backupmediafamily BkpMF INNER JOIN msdb.dbo.backupset BkpSet ON BkpMF.media_set_id = BkpSet.media_set_id WHERE (CONVERT(datetime, BkpSet.backup_start_date, 102) >= GETDATE() - 7) ORDER BY BkpSet.backup_finish_date desc
Read more on SQL Server Job details: