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 (www.sqllitespeed.com) to the
SQL Server community by DBAssociatesIT, 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 128 bit encryption
capabilities are available to protect your biggest corporate asset, your data.3
These types of capabilities for SQL Server are mouth watering to DBA's 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
• 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
DBA's 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:
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.
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.
restored: Database: %1, creation date(time): %2(%3), first LSN: %4, last
LSN: %5, number of dump devices: %7!d!, device information: (%8).
Database: %1, creation date(time): %2(%3), first LSN: %4, last LSN: %5,
number of dump devices: %7!d!, device information: (%8).
restored: Database: %1, creation date(time): %2(%3), file list: (%4),
number of dump devices: %6!d!, device information: (%7).
cannot be restored over the existing '%ls'. Reissue the RESTORE
statement using WITH REPLACE to overwrite pre-existing files.
option is not supported for RESTORE DATABASE. You can use the STOPAT
option with RESTORE LOG.
'%.*ls' (database ID %d). The RESTORE statement could not access file
'%ls'. Error was '%ls'.
'%.*ls' (database ID %d) was marked for standby or read-only use, but
has been modified. The RESTORE LOG statement cannot be performed.
use. The system administrator must have exclusive use of the database to
restore the log.
For a script to setup “The 15 Critical Backup and Restore Alerts” for SQL Server
2000 visit - 15CriticalBackupRestoreAlerts.sql
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 128
bit 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!
For additional information about SQL LiteSpeed visit the following URLs:
• Download a SQL LiteSpeed Trial –
• Backup and Restore – Back to Basics with SQL LiteSpeed –
• Integrating SQL LiteSpeed in your existing Backup Infrastructure -
• Spotlight - SQL LiteSpeed Return on Investment -
• DBAssociatesIT the maker of SQL LiteSpeed -
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.sqllitespeed.com – DBAssociatesIT – Accessed
Copyright © 2002-2003 Edgewood Solutions All Rights Reserved. Some names and
products listed are the registered trademarks of their respective owners.