SQL SERVER 詳細備份報告

我需要創建一個腳本，為以下資訊提供報告：

• 備份作業名稱
• 過去 24 小時內已備份的數據庫
• 每個數據庫備份的大小
• 備份開始時間
• 備份結束時間
• 備份持續時間
• 備份文件的位置

我仍然需要包括日誌備份資訊、備份大小和儲存的備份文件的位置。有人可以指出我如何更好地查詢的正確方向嗎？提前致謝。

我試過的：

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

我能夠解決我自己的問題。以下是我用來完成報告的腳本：

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

要在查詢中獲取備份文件的路徑，您需要使用該[msdb].[dbo].[backupmediafamily]表。

此類請求應滿足您的條件：

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]

引用自：https://dba.stackexchange.com/questions/282074