1.888.SQL.DBA1

Call us for help finding the right option for you. We don't have sales people, so there's never a "sales pitch".



sample newsletter

enter email address    

 

Alert! Alert! Alert! Backup and Restore – Baby!

Valuable SQL Server Alerts for SQL LiteSpeed backups across your organization

Written by: Jeremy Kadlec of Edgewood Solutions - Revised April 12, 2004

Introduction

At many organizations I doubt red lights flash with the sound of blaring sirens when backups fail in production environments, but I believe at every organization backups are one of the most critical IT functions. Of all backups, database backups, by most accounts, are considered the most critical. I know this is certainly true for me as a SQL Server DBA. I am sure at one point in all of our careers we were too busy to check all of our backups across all SQL Servers, with a million responsibilities on a daily basis. I know some of us were lucky and made it unscathed, while others were not so lucky with backup failures that caused extensive data loss and long downtimes. Now when IT Departments are forced to “do more with less,” it is time to put the necessary SQL Server Alerts in place for backup and restore operations in order to more easily monitor and streamline this critical process.

SQL Server Backup Needs

With the recent introduction of SQL LiteSpeed (http://www.imceda.com/) to the SQL Server community by Imceda, the compression and encryption capabilities are mind boggling as compared to the native SQL Server backup solutions. Backup time savings are generally 50% faster, with some organizations seeing up to a 95% improvement; restore times are generally 30% faster, with some organizations seeing up to a 50% improvement; and RC2 secret key encryption capabilities are available to protect your biggest corporate asset, your data.3

These types of capabilities for SQL Server are mouth watering to DBAs for backups and restorations in Disaster Recovery scenarios, IT Pro’s responsible for Storage Management and Security Professionals fearful of the clear text SQL Server backups. With all of these advantages, how would I as the DBA, responsible for backups, obtain notification with successful or failed backups? In this article we are going to explore the valuable SQL Server Alerts for SQL Server backups and restore.

SQL Server Agent Core Components

Before we jump into database backup and restore Alerts, I want to introduce the core components of the SQL Server Agent Service, which I am certain are familiar to all DBAs. SQL Server Agent is primarily responsible for the Alerts, Operators and Jobs. Just as the Master database is primarily the storage for the “MSSQLServer” service, the MSDB database is the primary storage for the “SQLServerAgent” Service with tables such as sysalerts, sysoperators and sysjobs. To extend the functionality of the SQL Server Agent, both the Enterprise Manager and system stored procedures can be leverage to:

  • Setup SQL Server Alerts, Operators and Jobs

  • Configure SQL Server Agent Mail

  • Establish a Fail Safe Operator

  • Event Forwarding to a centralized SQL Server

An outstanding resource for the interrelationships between Alerts, Operators and Jobs is the SQL Server 2000 Books Online article - SQL Server Agent Service. This article provides a high level overview of the SQL Server Agent Service, the general architecture and interrelationships among the components1. For additional information about these items, review the following SQL Server Books Online articles:

  • Defining Alerts

  • How to set up a SQL Server database alert (Windows2000)

  • Defining Operators

  • Creating Jobs

  • SQL Mail

  • How to designate a fail-safe operator (Enterprise Manager)

  • How to designate an events forwarding server (Enterprise Manager)

The 15 Critical Backup and Restore Alerts

At Edgewood Solutions, our DBA best practices dictate that Alerts should be setup and configured for SQL Server backups. Needless to say, Alerts provide DBAs with a proactive means of validating core business processes such as backups. They also offer peace of mind for the staff on a daily basis as well as a reasonable level of assurance for the organization. Below outlines a baseline set of backup and restore Alerts that can be leveraged with SQL LiteSpeed to verify the backup and restore operations:

SQL Server Alerts – Backup and Restore2
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 Backup 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 Backup 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 Backup 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 Backup 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 Backup Failure 3145 16 The STOPAT option is not supported for RESTORE DATABASE. You can use the STOPAT option with RESTORE LOG.
13 Backup Failure 3441 21 Database '%.*ls' (database ID %d). The RESTORE statement could not access file '%ls'. Error was '%ls'.
14 Backup 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 Backup Failure 4301 16 Database in use. The system administrator must have exclusive use of the database to restore the log.

Script

Click here for a Microsoft SQL Server script to create the 15 alerts listed above.

Conclusion

For DBAs supporting critical production environments for organizations, backups are a critical component of daily operations. As the number of SQL Servers grow at your organization, it is then necessary to automate the monitoring of daily backup operations via Alerts and Operators to streamline critical daily processes. Luckily, SQL Server environments can benefit from SQL LiteSpeed with significant disk and time savings as well as protection from RC2 secret key encryption. Leverage SQL LiteSpeed as a diligent DBA, savvy Storage Administrator and protective Security Professional to gain valuable time, save critical storage and protect precious data!

Additional Information

For additional information about SQL LiteSpeed visit the following URLs:

Resources

  1. SQL Server Agent Service - SQL Server 2000 Books Online - Microsoft Corporation – Published January 2003 – Accessed - 06.16.2003

  2. Error Message Severity Levels - SQL Server 2000 Books Online - Microsoft Corporation – Published January 2003 – Accessed - 06.16.2003

  3. SQL LiteSpeed web site – www.imceda.com – Imceda – Accessed 06.16.2003

About the Author – Jeremy Kadlec

Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, (www.edgewoodsolutions.com) a technology services company delivering full spectrum Microsoft SQL Server Services on the east coast of the United States primarily in the Washington DC and Boston areas. Jeremy can be reached at 410.591.4683 or jeremyk@edgewoodsolutions.com.

Learn more about how Edgewood Solutions delivers ‘databases at their finest’ at http://www.edgewoodsolutions.com/.

Copyright © 2002-2004 Edgewood Solutions All Rights Reserved

Some names and products listed are the registered trademarks of their respective owners.