SQL Server Database Backup and Restore Failure Notifications
By: Jeremy Kadlec | Updated: 2023-06-02 | Comments (3) | Related: More > 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.
View all my tips
Article Last Updated: 2023-06-02