Last active
January 13, 2026 14:59
-
-
Save ghotz/ebf1fb1a9d2cd24a5aee4fa630186538 to your computer and use it in GitHub Desktop.
Audit database backup failures for all databases, of for specific databases, pulling surrounding error details
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DECLARE @StartSearchDate datetime; | |
| DECLARE @SecondsAround int; | |
| DECLARE @Databases TABLE (DatabaseName sysname NOT NULL PRIMARY KEY); | |
| SET @StartSearchDate = GETDATE() - 2; | |
| SET @SecondsAround = 1; | |
| -- | |
| -- Get error logs | |
| -- | |
| DROP TABLE IF EXISTS #ErrorLogs; | |
| CREATE TABLE #ErrorLogs ( | |
| ArchiveNum int NOT NULL PRIMARY KEY | |
| , LogStartDate datetime NOT NULL | |
| , LogSizeBytes bigint NOT NULL | |
| ) | |
| INSERT #ErrorLogs | |
| EXEC master.dbo.sp_enumerrorlogs; | |
| -- remove out of scope errorlogs | |
| WITH cte AS | |
| ( | |
| SELECT ArchiveNum, LogStartDate, LogSizeBytes | |
| , LEAD(LogStartDate) OVER (ORDER BY LogStartDate) AS NextLogStartDate | |
| FROM #ErrorLogs | |
| ) | |
| DELETE cte | |
| WHERE | |
| LogStartDate < @StartSearchDate | |
| AND NextLogStartDate IS NOT NULL | |
| AND NextLogStartDate <= @StartSearchDate; | |
| -- | |
| -- Get database failed entries | |
| -- | |
| DECLARE @ArchiveNum int; | |
| DROP TABLE IF EXISTS #ErrorLogInfo; | |
| CREATE TABLE #ErrorLogInfo ( | |
| LogDate datetime NOT NULL | |
| , Processinfo nvarchar(200) NOT NULL | |
| , LogText nvarchar(MAX) NOT NULL | |
| , INDEX cl_ErrorLogInfo CLUSTERED (LogDate) | |
| ); | |
| -- loops error logs | |
| WHILE EXISTS (SELECT * FROM #ErrorLogs) | |
| BEGIN | |
| SET @ArchiveNum = (SELECT TOP 1 ArchiveNum FROM #ErrorLogs); | |
| IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)) = 0 | |
| -- need to read all ERRORLOG (eg. on AWS) | |
| INSERT #ErrorLogInfo | |
| EXEC master.dbo.sp_readerrorlog @ArchiveNum, 1, NULL, NULL; | |
| ELSE | |
| -- can pre-filter by date | |
| INSERT #ErrorLogInfo | |
| EXEC master.dbo.xp_readerrorlog @ArchiveNum, 1, NULL, NULL, @StartSearchDate, NULL, N'asc'; | |
| WITH cte AS | |
| ( | |
| SELECT DISTINCT E1.LogDate | |
| , DATEADD(second, - @SecondsAround, E1.LogDate) AS StartLogDate | |
| , DATEADD(second, @SecondsAround, E1.LogDate) AS EndLogDate | |
| FROM #ErrorLogInfo AS E1 | |
| WHERE E1.LogText LIKE N'%BACKUP failed%' | |
| ) | |
| DELETE | |
| FROM #ErrorLogInfo | |
| WHERE NOT EXISTS ( | |
| SELECT * | |
| FROM cte AS E1 | |
| WHERE #ErrorLogInfo.LogDate BETWEEN E1.StartLogDate AND E1.EndLogDate | |
| ); | |
| DELETE #ErrorLogs WHERE ArchiveNum = @ArchiveNum; | |
| END; | |
| SELECT * FROM #ErrorLogInfo; | |
| GO |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DECLARE @StartSearchDate datetime; | |
| DECLARE @SecondsAround int; | |
| DECLARE @Databases TABLE (DatabaseName sysname NOT NULL PRIMARY KEY); | |
| SET @StartSearchDate = GETDATE() - 2; | |
| SET @SecondsAround = 1; | |
| INSERT @Databases VALUES (N'AdventureWorks2022'), (N'AdventureWorks2025'); | |
| -- | |
| -- Get error logs | |
| -- | |
| DROP TABLE IF EXISTS #ErrorLogs; | |
| CREATE TABLE #ErrorLogs ( | |
| ArchiveNum int NOT NULL PRIMARY KEY | |
| , LogStartDate datetime NOT NULL | |
| , LogSizeBytes bigint NOT NULL | |
| ) | |
| INSERT #ErrorLogs | |
| EXEC master.dbo.sp_enumerrorlogs; | |
| -- remove out of scope errorlogs | |
| WITH cte AS | |
| ( | |
| SELECT ArchiveNum, LogStartDate, LogSizeBytes | |
| , LEAD(LogStartDate) OVER (ORDER BY LogStartDate) AS NextLogStartDate | |
| FROM #ErrorLogs | |
| ) | |
| DELETE cte | |
| WHERE | |
| LogStartDate < @StartSearchDate | |
| AND NextLogStartDate IS NOT NULL | |
| AND NextLogStartDate <= @StartSearchDate; | |
| -- | |
| -- Get database failed entries | |
| -- | |
| DECLARE @ArchiveNum int; | |
| DROP TABLE IF EXISTS #ErrorLogInfo; | |
| CREATE TABLE #ErrorLogInfo ( | |
| LogDate datetime NOT NULL | |
| , Processinfo nvarchar(200) NOT NULL | |
| , LogText nvarchar(MAX) NOT NULL | |
| , INDEX cl_ErrorLogInfo CLUSTERED (LogDate) | |
| ); | |
| -- loops error logs | |
| WHILE EXISTS (SELECT * FROM #ErrorLogs) | |
| BEGIN | |
| SET @ArchiveNum = (SELECT TOP 1 ArchiveNum FROM #ErrorLogs); | |
| IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)) = 0 | |
| -- need to read all ERRORLOG (eg. on AWS) | |
| INSERT #ErrorLogInfo | |
| EXEC master.dbo.sp_readerrorlog @ArchiveNum, 1, NULL, NULL; | |
| ELSE | |
| -- can pre-filter by date | |
| INSERT #ErrorLogInfo | |
| EXEC master.dbo.xp_readerrorlog @ArchiveNum, 1, NULL, NULL, @StartSearchDate, NULL, N'asc'; | |
| WITH cte AS | |
| ( | |
| SELECT DISTINCT E1.LogDate | |
| , DATEADD(second, - @SecondsAround, E1.LogDate) AS StartLogDate | |
| , DATEADD(second, @SecondsAround, E1.LogDate) AS EndLogDate | |
| FROM #ErrorLogInfo AS E1 | |
| JOIN @Databases AS D1 | |
| ON E1.LogText LIKE N'%' + D1.DatabaseName + N'%' | |
| WHERE E1.LogText LIKE N'%BACKUP failed%' | |
| ) | |
| DELETE | |
| FROM #ErrorLogInfo | |
| WHERE NOT EXISTS ( | |
| SELECT * | |
| FROM cte AS E1 | |
| WHERE #ErrorLogInfo.LogDate BETWEEN E1.StartLogDate AND E1.EndLogDate | |
| ); | |
| DELETE #ErrorLogs WHERE ArchiveNum = @ArchiveNum; | |
| END; | |
| SELECT * FROM #ErrorLogInfo; | |
| GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment