SQL Server Database Backup and Restore Error or Failure Notifications
ProblemMy backups and restores fail infrequently, but when I find out it is too late. How can I find out sooner?
SolutionSQL 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.
|
Please check below for the T-SQL code to implement these alerts.
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
|