1

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

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 advance.

What I have tried:

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```

1
  • 3
    You might want to look at sp_BlitzBackups. It grabs a lot of the stuff you're after, and a whole lot more. – Erik Darling Dec 23 '20 at 22:49
0

I was able to fix my own problem. Below is the script I used to complete my report:

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, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS int))/3600 AS varchar) + ' hours, ' 
  + CAST((CAST(DATEDIFF(s, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '
  + CAST((CAST(DATEDIFF(s, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS int))%60 AS varchar)+ ' seconds'
  AS [Duration],
CASE msdb..backupset.type 
WHEN 'D' THEN 'Full' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.backup_size,  
msdb.dbo.backupmediafamily.physical_device_name as 'Physical Location'
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.database_name, 
msdb.dbo.backupset.backup_finish_date ```

0

To get the path in the query to the backup file, you need to use the [msdb].[dbo].[backupmediafamily] table.

Such a request should satisfy your conditions:

DECLARE @dbname VARCHAR(255) = NULL --set this to be whatever dbname you want

SELECT bs.[database_name] AS 'Database Name',
       CASE bs.type
           WHEN 'D' THEN 'Full'
           WHEN 'L' THEN 'Log'
       END AS 'Type of backup',
       bs.[name] AS 'Backup set name',
       bs.[description] AS 'Backup set Description',
       bs.[backup_size] AS 'Size of each DB backup',
       bs.[backup_start_date] AS 'Start time of backup',
       bs.[backup_finish_date] AS 'End time of backup',
       convert(varchar,CAST((bs.[backup_finish_date] - bs.[backup_start_date]) AS time(0))) AS 'Duration of backup',
       bmf.[physical_device_name] AS 'Location of the backup files'
FROM [msdb].[dbo].[backupmediafamily] AS bmf
INNER JOIN [msdb].[dbo].[backupset] bs ON bmf.[media_set_id] = bs.[media_set_id] 
WHERE bs.[database_name] = @dbname or @dbname is null
-- Databases that have been backed up in the last 24 hours
-- AND DATEDIFF(hour, bs.[backup_start_date] , GETUTCDATE()) < 25
ORDER BY bs.[database_name],
         bs.[backup_finish_date]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.