SQL Server Database Backup and Restore Failure Notifications

Problem

My SQL Server backups and restores fail infrequently, but when I find out it is too late. How can I find out sooner?

Solution

SQL Server ships with native alerts that can be configured to notify an operator when a backup or restore failure occurs. Reference the chart below for the SQL Server backup and restore alerts as well as the link to the T-SQL code below to implement these alerts.

SQL Server Backup and Restore Alerts
IDCategoryErrorSeverityDescription
1Backup Success1826410Database backed up: Database: %1, creation date(time): %2(%3), pages dumped: %4!d!, first LSN: %5, last LSN: %6, number of dump devices: %9!d!, device information: (%10).
2Backup Failure1820416%1: Backup device ‘%2’ failed to %3. Operating system error = %4.
3Backup Failure1821016%1: %2 failure on backup device ‘%3’. Operating system error %4.
4Backup Failure300916Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
5Backup Failure301716Could not resume interrupted backup or restore operation. See the SQL Server error log for more information.
6Backup Failure303316BACKUP DATABASE cannot be used on a database opened in emergency mode.
7Backup Failure320116Cannot open backup device ‘%ls’. Device error or device off-line. See the SQL Server error log for more details.
8Restore Success1826710Database restored: Database: %1, creation date(time): %2(%3), first LSN: %4, last LSN: %5, number of dump devices: %7!d!, device information: (%8).
9Restore Success1826810Log restored: Database: %1, creation date(time): %2(%3), first LSN: %4, last LSN: %5, number of dump devices: %7!d!, device information: (%8).
10Restore Success1826910Database file restored: Database: %1, creation date(time): %2(%3), file list: (%4), number of dump devices: %6!d!, device information: (%7).
11Restore Failure314216File ‘%ls’ cannot be restored over the existing ‘%ls’. Reissue the RESTORE statement using WITH REPLACE to overwrite pre-existing files.
12Restore Failure314516The STOPAT option is not supported for RESTORE DATABASE. You can use the STOPAT option with RESTORE LOG.
13Restore Failure344121Database ‘%.*ls’ (database ID %d). The RESTORE statement could not access file ‘%ls’. Error was ‘%ls’.
14Restore Failure344321Database ‘%.*ls’ (database ID %d) was marked for standby or read-only use, but has been modified. The RESTORE LOG statement cannot be performed.
15Restore Failure430116Database in use. The system administrator must have exclusive use of the database to restore the log.

SQL Server Backup and Restore Alerts

   -- source: https://www.MSSQLTips.com
--
-- https://www.mssqltips.com/sqlservertip/939/sql-server-database-backup-and-restore-failure-notifications/
SELECT GETDATE()
GO
SELECT @@SERVERNAME
GO
SELECT DB_NAME()
GO
USE MSDB
GO
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Backup Success - 18264'))
   ---- Delete the alert with the same name.
   EXECUTE msdb.dbo.sp_delete_alert @name = N'Backup Success - 18264' 
BEGIN 
   EXECUTE msdb.dbo.sp_add_alert @name = N'Backup Success - 18264', 
      @message_id = 18264, 
      @severity = 0, 
      @enabled = 1, 
      @delay_between_responses = 60, 
      @include_event_description_in = 5, 
      @category_name = N'[Uncategorized]'
END
GO
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Backup Failure - 18204'))
   ---- Delete the alert with the same name.
   EXECUTE msdb.dbo.sp_delete_alert @name = N'Backup Failure - 18204' 
BEGIN 
   EXECUTE msdb.dbo.sp_add_alert 
      @name = N'Backup Failure - 18204', 
      @message_id = 18204, 
      @severity = 0,
      @enabled = 1, 
      @delay_between_responses = 60,
      @include_event_description_in = 5, 
      @category_name = N'[Uncategorized]'
END
GO
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Backup Failure - 18210'))
   ---- Delete the alert with the same name.
   EXECUTE msdb.dbo.sp_delete_alert @name = N'Backup Failure - 18210' 
BEGIN 
   EXECUTE msdb.dbo.sp_add_alert 
      @name = N'Backup Failure - 18210', 
      @message_id = 18210, 
      @severity = 0, 
      @enabled = 1, 
      @delay_between_responses = 60,
      @include_event_description_in = 5, 
      @category_name = N'[Uncategorized]'
END
GO
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Backup Failure - 3009'))
   ---- Delete the alert with the same name.
   EXECUTE msdb.dbo.sp_delete_alert @name = N'Backup Failure - 3009' 
BEGIN 
   EXECUTE msdb.dbo.sp_add_alert 
      @name = N'Backup Failure - 3009', 
      @message_id = 3009, 
      @severity = 0, 
      @enabled = 1, 
      @delay_between_responses = 60, 
      @include_event_description_in = 5, 
      @category_name = N'[Uncategorized]'
END
GO
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Backup Failure - 3017'))
   ---- Delete the alert with the same name.
   EXECUTE msdb.dbo.sp_delete_alert @name = N'Backup Failure - 3017' 
BEGIN 
   EXECUTE msdb.dbo.sp_add_alert 
      @name = N'Backup Failure - 3017', 
      @message_id = 3017, 
      @severity = 0, 
      @enabled = 1, 
      @delay_between_responses = 60, 
      @include_event_description_in = 5, 
      @category_name = N'[Uncategorized]'
   END
GO
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Backup Failure - 3033'))
   ---- Delete the alert with the same name.
   EXECUTE msdb.dbo.sp_delete_alert @name = N'Backup Failure - 3033' 
BEGIN 
   EXECUTE msdb.dbo.sp_add_alert 
      @name = N'Backup Failure - 3033', 
      @message_id = 3033, 
      @severity = 0, 
      @enabled = 1, 
      @delay_between_responses = 60, 
      @include_event_description_in = 5, 
      @category_name = N'[Uncategorized]'
END
GO
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Backup Failure - 3201'))
   ---- Delete the alert with the same name.
   EXECUTE msdb.dbo.sp_delete_alert @name = N'Backup Failure - 3201' 
BEGIN 
   EXECUTE msdb.dbo.sp_add_alert 
      @name = N'Backup Failure - 3201', 
      @message_id = 3201, 
      @severity = 0, 
      @enabled = 1, 
      @delay_between_responses = 60, 
      @include_event_description_in = 5, 
      @category_name = N'[Uncategorized]'
END
GO
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Restore Success - 18267'))
   ---- Delete the alert with the same name.
   EXECUTE msdb.dbo.sp_delete_alert @name = N'Restore Success - 18267' 
BEGIN 
   EXECUTE msdb.dbo.sp_add_alert 
      @name = N'Restore Success - 18267', 
      @message_id = 18267, 
      @severity = 0, 
      @enabled = 1, 
      @delay_between_responses = 60, 
      @include_event_description_in = 5, 
      @category_name = N'[Uncategorized]'
END
GO
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Restore Success - 18268'))
   ---- Delete the alert with the same name.
   EXECUTE msdb.dbo.sp_delete_alert @name = N'Restore Success - 18268' 
BEGIN 
   EXECUTE msdb.dbo.sp_add_alert 
      @name = N'Restore Success - 18268', 
      @message_id = 18268, 
      @severity = 0, 
      @enabled = 1, 
      @delay_between_responses = 60, 
      @include_event_description_in = 5, 
      @category_name = N'[Uncategorized]'
   END
GO
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Restore Success - 18269'))
   ---- Delete the alert with the same name.
   EXECUTE msdb.dbo.sp_delete_alert @name = N'Restore Success - 18269' 
BEGIN 
   EXECUTE msdb.dbo.sp_add_alert 
      @name = N'Restore Success - 18269', 
      @message_id = 18269, 
      @severity = 0, 
      @enabled = 1, 
      @delay_between_responses = 60, 
      @include_event_description_in = 5, 
      @category_name = N'[Uncategorized]'
END
GO
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Restore Failure - 3142'))
   ---- Delete the alert with the same name.
   EXECUTE msdb.dbo.sp_delete_alert @name = N'Restore Failure - 3142' 
BEGIN 
   EXECUTE msdb.dbo.sp_add_alert 
      @name = N'Restore Failure - 3142', 
      @message_id = 3142, 
      @severity = 0, 
      @enabled = 1, 
      @delay_between_responses = 60, 
      @include_event_description_in = 5, 
      @category_name = N'[Uncategorized]'
END
GO
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Restore Failure - 3145'))
   ---- Delete the alert with the same name.
   EXECUTE msdb.dbo.sp_delete_alert @name = N'Restore Failure - 3145' 
BEGIN 
   EXECUTE msdb.dbo.sp_add_alert 
      @name = N'Restore Failure - 3145', 
      @message_id = 3145, 
      @severity = 0, 
      @enabled = 1, 
      @delay_between_responses = 60, 
      @include_event_description_in = 5, 
      @category_name = N'[Uncategorized]'
END
GO
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Restore Failure - 3441'))
   ---- Delete the alert with the same name.
   EXECUTE msdb.dbo.sp_delete_alert @name = N'Restore Failure - 3441' 
BEGIN 
   EXECUTE msdb.dbo.sp_add_alert 
      @name = N'Restore Failure - 3441', 
      @message_id = 3441, 
      @severity = 0, 
      @enabled = 1, 
      @delay_between_responses = 60, 
      @include_event_description_in = 5, 
      @category_name = N'[Uncategorized]'
END
GO
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Restore Failure - 3443'))
   ---- Delete the alert with the same name.
   EXECUTE msdb.dbo.sp_delete_alert @name = N'Restore Failure - 3443' 
BEGIN 
   EXECUTE msdb.dbo.sp_add_alert 
      @name = N'Restore Failure - 3443', 
      @message_id = 3443, 
      @severity = 0, 
      @enabled = 1, 
      @delay_between_responses = 60, 
      @include_event_description_in = 5, 
      @category_name = N'[Uncategorized]'
   END
GO
/*
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Restore Failure - 4301'))
   ---- Delete the alert with the same name.
   EXECUTE msdb.dbo.sp_delete_alert @name = N'Restore Failure - 4301' 
BEGIN 
   EXECUTE msdb.dbo.sp_add_alert 
      @name = N'Restore Failure - 4301', 
      @message_id = 4301, 
      @severity = 0, 
      @enabled = 1, 
      @delay_between_responses = 60, 
      @include_event_description_in = 5, 
      @category_name = N'[Uncategorized]'
   END
GO
*/

Next Steps

  • Get up to speed on Alerts, Operators and SQL Server Mail technologies.
  • Consider building a distribution list to receive the alerts.
  • Review the T-SQL alerts code to determine if any modifications are necessary.
  • Setup Alerts, Operators, SQL Server Mail and SQL Server Agent Mail for your key environments.
  • Review the Alerts as they are raised to determine the root cause of the issue.
  • Ensure the root cause is corrected (i.e. insufficient disk space, corruption, etc.) so that the problem does not persist.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *