顯示備份資訊（成功和失敗）的查詢 SQL Server

我有兩個備份兩個不同數據庫的工作。

作業 1 備份 DB1

作業 2 備份 DB2

DB1 由於驅動器 1 上的空間不足導致作業 1 失敗而無法備份。為了解決這個問題，我只需要添加空間。沒什麼大不了的。今天這個問題已經發生了一個月，我被告知了這一點。是的，我知道這很瘋狂，但它是開發者

我想獲得 DB1 的完整備份歷史記錄。我知道我可以從 msdb.dbo.backupset 表中檢索成功的備份資訊，但我想知道是否有顯示數據庫備份失敗的查詢。

我下面的查詢顯示了從 12/31/13-1/27/14 特定數據庫的備份歷史記錄。資訊包括伺服器、數據庫名稱、備份開始和結束時間、備份數據庫所需的總時間、數據庫大小和備份集名稱。

SELECT  
  distinct 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((DATEDIFF(second,  msdb.dbo.backupset.backup_start_date,msdb.dbo.backupset.backup_finish_date)) AS varchar)+ ' secs  ' AS [Total Time] ,

  Cast(msdb.dbo.backupset.backup_size/1024/1024 AS numeric(10,2)) AS 'Backup Size(MB)',   
  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   
--Enter your database below
--and database_name = 'db_name_here'
and msdb.dbo.backupset.backup_start_date>'2013-12-31' and msdb.dbo.backupset.backup_start_date<'2014-01-27 23:59:59'
ORDER BY  
  msdb.dbo.backupset.database_name, 
  msdb.dbo.backupset.backup_start_date

有沒有辦法通過修改我的程式碼來獲取這些資訊？我可以通過執行針對 sysjobhistory 和 sysjob 表執行的 sql 語句來檢索 JOB1 的歷史記錄。這可能是一個長鏡頭有沒有辦法可以使用 msdb 中的 sysjobhistory、sysjob、backupset 和 backupsetmediafamily 表來產生我想要的結果？

可悲的是，backupset不包含失敗的備份，我不知道msdb這些中可能儲存的其他任何地方，除非您可以依賴sysjobhistory，它不包含所有時間（取決於您的保留設置），並且會忽略任何在作業上下文之外進行的備份嘗試，並且 - 在備份許多數據庫的作業的情況下 - 不會區分哪個數據庫實際失敗，除非它恰好發生在作業的早期 - 這是因為消息非常冗長但被截斷。

如果您絕對知道Job n只備份一個數據庫，並且該作業的每次失敗都意味著該數據庫未備份（因為備份成功後該作業也可能失敗，例如嘗試縮小或執行其他維護），那麼你可以使用這樣的查詢：

DECLARE @job sysname, @db sysname;

SELECT @job = N'Job 1', @db = N'db_name';

SELECT  
  bs.database_name,  
  bs.backup_start_date,  
  bs.backup_finish_date, 
  [Total Time] = CAST((DATEDIFF(SECOND, bs.backup_start_date,bs.backup_finish_date))
    AS varchar(30))+ ' secs',
  CAST(bs.backup_size/1024/1024 AS decimal(10,2)) AS 'Backup Size(MB)',   
  h.[message]
FROM msdb.dbo.sysjobhistory AS h
INNER JOIN msdb.dbo.sysjobs AS j
ON h.job_id = j.job_id
AND h.step_id = 0
LEFT OUTER JOIN msdb.dbo.backupset AS bs
ON bs.database_name = @db
AND 
ABS(DATEDIFF(SECOND, bs.backup_start_date, CONVERT(DATETIME,convert(char(8),h.run_date) 
  + ' ' + STUFF(STUFF(RIGHT('0'+CONVERT(char(6),h.run_time),6),3,0,':'),6,0,':')))) < 5
WHERE j.name = @job
ORDER BY bs.backup_start_date;

是的，它真的很難看，因為sysjobhistory即使在 SQL Server 2014 中，它仍然儲存run_date和run_time作為單獨的整數。我敢打賭，做出這個決定的人仍然是 35 號樓的飛鏢背景。它還假設備份是工作的第一步，因此比較不科學的日期/時間來確保我們已經正確將作業的正確實例與備份的正確實例相關聯。哦，我多麼希望我可以重新設計備份和作業的架構。

如果您希望作業之外的範圍更廣，您可以在 SQL Server 錯誤日誌中查找失敗的備份（如果它們沒有被循環刪除）：

EXEC sp_readerrorlog 0, 1, 'BACKUP failed'; -- current
EXEC sp_readerrorlog 1, 1, 'BACKUP failed'; -- .1 (previous)
EXEC sp_readerrorlog 2, 1, 'BACKUP failed'; -- .2 (the one before that)
....

（但我不知道將輸出合併到現有查詢中的好方法。）

您還可以從預設跟踪中關聯“失去”的成功備份，例如

DECLARE @path nvarchar(260);

SELECT 
  @path = REVERSE(SUBSTRING(REVERSE([path]), 
  CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT dt.DatabaseName, dt.StartTime, bs.backup_start_date, bs.backup_finish_date, 
 [Status] = CASE WHEN bs.backup_start_date IS NULL 
   THEN 'Probably failed'
   ELSE 'Seems like success'
 END
FROM sys.fn_trace_gettable(@path, DEFAULT) AS dt
LEFT OUTER JOIN msdb.dbo.backupset AS bs
ON dt.DatabaseName = bs.database_name
AND ABS(DATEDIFF(SECOND, dt.StartTime, bs.backup_start_date)) < 5
WHERE dt.EventClass = 115 -- backup/restore events
AND UPPER(CONVERT(nvarchar(max),dt.TextData)) LIKE N'BACKUP%DATABASE%'
--AND dt.DatabaseName = N'db_name' -- to filter to a single database
--AND bs.database_name = N'db_name'
ORDER BY dt.StartTime;

當然，這也依賴於預設跟踪中的數據循環消失，數據庫名稱沒有更改等。不幸的是，預設跟踪不區分成功和失敗的備份，並且開始時間不會精確匹配 MSDB數據，但只要您不循環執行備份，這對於目測應該沒問題。我試圖將這些問題合併到查詢中。

最後，您可能希望使用FULL OUTER JOIN那裡，以防備份集的歷史比預設跟踪更長。[Status]這稍微改變了語義。

您可能還想嘗試一下這個討厭的東西，儘管我沒有太多運氣。我只能看到目前或最近的狀態，因此只有在作業上次執行失敗時才有幫助，並且 - 就像sysjobhistory- 無法獲取有關已嘗試但未通過作業的任何備份的資訊。

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