solving sql server problems for millions of dbas and developers since 2006

SQL Server Database Backup and Restore Failure Notifications

By: Jeremy Kadlec | Read Comments (1) | Print

Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009.

Related Tips: More

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 2000 backup and restore alerts as well as the link to the T-SQL code below to implement these alerts.

SQL Server 2000 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.

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 Tips: More | Become a paid author

Last Update: 6/12/2006

Share:

Free SQL Server Learning

Comments and Feedback:

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

Read The Tip

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.

Sponsor Information

"SQL doctor is the best SQL product on the market, by far. All of Idera's tools are great, but this is the icing on the cake!"

SQL Monitor, server monitoring so easy, your boss could do it. Try it online.

SQL Servers keeping you up at night? Contact the Edgewood SQL Server consultants for a Health Check.

Find and Fix SQL issues with Foglight Performance Analysis. Get a free copy.

Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now.

Demystify TempDB Performance and Manageability