SQL Server Database Backup and Restore Failure Notifications

By: Jeremy Kadlec | Updated: 2023-06-02 | Comments (3) | Related: > Backup

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
ID	Category	Error	Severity	Description
1	Backup Success	18264	10	Database 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).
2	Backup Failure	18204	16	%1: Backup device '%2' failed to %3. Operating system error = %4.
3	Backup Failure	18210	16	%1: %2 failure on backup device '%3'. Operating system error %4.
4	Backup Failure	3009	16	Could 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.
5	Backup Failure	3017	16	Could not resume interrupted backup or restore operation. See the SQL Server error log for more information.
6	Backup Failure	3033	16	BACKUP DATABASE cannot be used on a database opened in emergency mode.
7	Backup Failure	3201	16	Cannot open backup device '%ls'. Device error or device off-line. See the SQL Server error log for more details.
8	Restore Success	18267	10	Database restored: Database: %1, creation date(time): %2(%3), first LSN: %4, last LSN: %5, number of dump devices: %7!d!, device information: (%8).
9	Restore Success	18268	10	Log restored: Database: %1, creation date(time): %2(%3), first LSN: %4, last LSN: %5, number of dump devices: %7!d!, device information: (%8).
10	Restore Success	18269	10	Database file restored: Database: %1, creation date(time): %2(%3), file list: (%4), number of dump devices: %6!d!, device information: (%7).
11	Restore Failure	3142	16	File '%ls' cannot be restored over the existing '%ls'. Reissue the RESTORE statement using WITH REPLACE to overwrite pre-existing files.
12	Restore Failure	3145	16	The STOPAT option is not supported for RESTORE DATABASE. You can use the STOPAT option with RESTORE LOG.
13	Restore Failure	3441	21	Database '%.*ls' (database ID %d). The RESTORE statement could not access file '%ls'. Error was '%ls'.
14	Restore Failure	3443	21	Database '%.*ls' (database ID %d) was marked for standby or read-only use, but has been modified. The RESTORE LOG statement cannot be performed.
15	Restore Failure	4301	16	Database 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.

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-06-02