SQL Server Detailed Backup Report

Question

I need to create a script that gives the report for the following information:

• Backup Job Name
• Databases that have been backed up in the last 24 hours
• Size of each DB backup
• Start time of backup
• End time of backup
• Duration of backup
• Location of the backup files

What I have tried:

DECLARE @dbname sysname
SET @dbname = NULL --set this to be whatever dbname you want
SELECT 
  bup.database_name AS [Database],
  bup.server_name AS [Server],
  bup.backup_start_date AS [Backup Started],
  bup.backup_finish_date AS [Backup Finished]
  ,CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, ' 
  + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '
  + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds'
  AS [Total Time]
FROM msdb.dbo.backupset bup
WHERE bup.backup_set_id IN
  (SELECT MAX(backup_set_id) 
   FROM msdb.dbo.backupset
   WHERE database_name = ISNULL(@dbname, database_name) --if no dbname, then return all
   AND type = 'D' --only interested in the time of last full backup
   GROUP BY database_name) 
/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */
AND bup.database_name IN (SELECT name FROM master.dbo.sysdatabases)
ORDER BY bup.database_name

I need to still include Log backup information, backup size and the location of the backup files stored. Could someone point me in the right direction of how to better my query? Thanks in adavance.

Answer 1

Hi @SQLLOVER21 ,

Please try below scripts;

SELECT   
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,   
msdb.dbo.backupset.database_name,   
msdb.dbo.backupset.backup_start_date,   
msdb.dbo.backupset.backup_finish_date,   
CAST((CAST(DATEDIFF(s, backup_start_date, backup_finish_date) AS int))/3600 AS varchar) + ' hours, '   
  + CAST((CAST(DATEDIFF(s, backup_start_date, backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '  
  + CAST((CAST(DATEDIFF(s, backup_start_date, backup_finish_date) AS int))%60 AS varchar)+ ' seconds'  
  AS [Total Time],  
CASE msdb..backupset.type   
WHEN 'D' THEN 'Database'   
WHEN 'L' THEN 'Log'   
When 'I' THEN 'Differential database'  
END AS backup_type,   
msdb.dbo.backupset.backup_size,   
msdb.dbo.backupmediafamily.physical_device_name,   
msdb.dbo.backupset.name AS backupset_name  
FROM msdb.dbo.backupmediafamily   
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id   
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE()-1 )   
ORDER BY   
msdb.dbo.backupset.backup_finish_date desc  

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.