Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Database Backup and Restore Failure Notifications


By:   |   Read Comments (3)   |   Related Tips: More > Backup

Attend these FREE MSSQLTips webcasts >> click to register


Problem

My 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.

 

Please click here for the T-SQL code to implement these alerts.

 

  • 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.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, October 29, 2013 - 5:24:41 PM - mike Back To Top

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

, @enabled = 1

, @delay_between_responses = 0

, @include_event_description_in = 5

, @category_name =N'[Uncategorized]'

, @job_id=N'ed75a6ec-1b7a-4870-a215-6ea694a4d8bd'

END

GO

----------------

IF (EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Restore Success - Message'))

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert@name =N'Restore Success - Message' 

BEGIN 

EXECUTE msdb.dbo.sp_add_alert@name =N'Restore Success - Message',

 @message_id = 0

, @severity=1

, @enabled = 1

, @delay_between_responses = 0

, @include_event_description_in = 5

, @event_description_keyword=N'Restore is complete on database'

, @category_name =N'[Uncategorized]'

, @job_id=N'ed75a6ec-1b7a-4870-a215-6ea694a4d8bd'

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

, @enabled = 1

, @delay_between_responses = 0

, @include_event_description_in = 5

, @category_name =N'[Uncategorized]'

, @job_id=N'ed75a6ec-1b7a-4870-a215-6ea694a4d8bd'

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

, @enabled = 1

, @delay_between_responses = 0

, @include_event_description_in = 5

, @category_name =N'[Uncategorized]'

, @job_id=N'ed75a6ec-1b7a-4870-a215-6ea694a4d8bd'

END

 

GO


Tuesday, October 29, 2013 - 5:18:09 PM - mike Back To Top

I am sorry this is late.

 

I tried using the code for the alerts when a database is restored.  I am not seeing any alerts  

Am I supposed to be doing something else?


Tuesday, April 03, 2012 - 3:11:59 PM - Nick A Back To Top

If you are running differential backups, you might want to include ID 18270 as well.

Here is the text:

Database differential changes were backed up. Database: '', creation date(time): '', pages dumped: '', first LSN: '', last LSN:'', full backup LSN: '', number of dump devices: '', device information: (FILE=20, TYPE=DISK: {''}). This is an informational message. No user action is required.


Learn more about SQL Server tools