One of the most important aspects for a database environment is ensuring reliable backups are being executed and a dependable recovery plan is established in the event of a system failure or data corruption. Several options are available for defining your backup and recovery model and your choices will determine the reliability and the amount of data loss your company can acceptably incur.

This document analyzes the various options available for your backup and recovery process with SQL Server 2000 as well as an enhancement to your SQL Server backup and recovery process using a highly efficient backup and restore utility that provides significant time and disk space savings called SQL LiteSpeed.

Recovery Model

Which recovery model is best for the databases in your environment? This setting depends on the critically of the data and the acceptable data loss in the event of a system failure. SQL Server 2000 offers three recovery models that can be implemented for your databases. The appropriate choice depends on your applications and the criticality of your data. These settings can be configured either through Enterprise Manager or through T-SQL using the ALTER DATABASE command.

The three database recovery model options are:

Simple

With the Simple Recovery model, data is recoverable only to the most recent full database or differential backup. Transaction log (or incremental changes) backups are not available. The Simple Recovery model is easier to manage than the Full or Bulk-Logged models, but at the expense of higher data loss because the contents of the database transaction log are truncated each time a checkpoint is issued for the database.

Full

The Full Recovery model uses database backups and transaction log backups to provide complete protection against failure. If one or more data files are damaged, restoring the backups permits recovery of all committed transactions using a combination of the database and transaction log backups.

Full Recovery provides the ability to recover the database to the point of failure or to a specific point in time. All operations, including bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data, are fully logged and recoverable.

Bulk Logged

The Bulk-Logged Recovery model provides protection against failure combined with the best performance. In order to get better performance, the following operations are minimally logged and not fully recoverable: SELECT INTO, Bulk load operations, CREATE INDEX as well as text and image operations.

Under the Bulk-Logged Recovery model, a damaged data file can result in having to redo work manually based on the operations above that are not fully logged. In addition, the Bulk-Logged Recovery model only allows the database to be recovered to the end of a transaction log backup when the log backup contains bulk changes. Point-in-time recovery is not supported.

SQL Server 2000 Enterprise Manager directions to configure the database Recovery Model:

In SQL Server Enterprise Manager, open the ‘Databases’ folder. Once the database folder is expanded, right click on the database and select the ‘Properties’ option. The ‘Database Properties’ window will open. Click on the ‘Options’ tab and the recovery model will be listed in the middle of the screen. Click on the drop down box to select the needed recovery model. On the bottom of the screen click ‘OK’ to save the Recovery Model. 

SQL Server 2000 Transact-SQL directions for ALTER DATABASE commands to configure the database Recovery Model:

ALTER DATABASE Northwind
SET RECOVERY FULL
GO

In this example the Northwind database is set to ‘Full’ Recovery Model.

Backup Options

Once the database recovery model has been identified, it is necessary to decide which backup method needs to be instituted for your backup and recovery procedures. There are several options and each has advantages and disadvantages. The backup options can be configured with either the Maintenance Plan Wizard, Enterprise Manager or through the use of T-SQL commands. Below outlines the available backup options:

Database

This option creates a full copy of the database. A complete snapshot of your database is created at the time the backup occurs.

Transaction

This option provides a copy of the active transaction log. Transaction log backups operate in conjunction with database backups to allow you to append transactions that have occurred since the last database backup. If successive logs are created, each log creates a set of the new transactions since the last transaction log backup.

Differential

This option copies only the database pages which have been modified after the last database backup. If successive differential backups are created, only the most recent differential backup is required for the recovery process. Differential backups are leveraged in combination with full backups. It is necessary to execute a full backup first and then execute the Differential backups on the needed interval. In addition, it is possible to use transaction log backups with differential backups based on the backup schedule.

File or Filegroup

For very large databases, an option is available for executing database file or filegroup backups. These backups allow you to backup a single data file at a time. One of the drawbacks with this option is that it requires more effort in planning the backup and recovery process as well as your overall database design. In most instances you only have one data file and one log file for each database and therefore this option does not make sense. Also, in order to use filegroup backups you must use transaction log backups in conjunction with this backup method.

Snapshot Backups

Using third party tools, such as Storage Area Network (SAN) solutions, you have the ability to capture file level snapshots of the database to replicate the complete database files to other disk drives on the SAN. Unfortunately, this method is expensive and not an option for most database installations.

Backup Commands

There are primarily two options when constructing backup commands, either backing up the database or the transaction log. In conjunction with these commands, there are several options which can be specified when constructing your backup commands. These additional options can be found in SQL Server Books Online in an article entitled ‘BACKUP’.

In the commands below, the {device} reference can specify either a logical or physical device. In constructing the commands you can reference the name of a physical file or you can specify a logical device that has been setup through Enterprise Manager or T-SQL. More information about this can be found in SQL Server Books Online.

DATABASE

This option specifies backing up the data portion of the database. For this command there are options to specify the full database, a list of files/filegroups or differential backups. The backup commands are constructed as follows:

Database

BACKUP DATABASE {databasename} TO {device}

Differential

BACKUP DATABASE {databasename} TO {device}. WITH DIFFERENTIAL

Filegroup

BACKUP DATABASE {databasename} FILE = {filename}, FILEGROUP = {filegroup} TO {device}

LOG

This option specifies a backup of the active transaction log. The log is backed up from the last successfully executed LOG backup to the end of the log. The command is constructed as follows:

BACKUP LOG {databasename} TO {device}

Tracking Tables

Several tables exist in the msdb database that track the backup operations which occurred on the server. These tables include:

• backupfile - Contains one row for each data or log file that is backed up
• backupmediafamily - Contains one row for each media family
• backupmediaset - Contains one row for each backup media set
• backupset- Contains a row for each backup set

Restore Commands

The restore commands are equivalent to the backup commands in terms of syntax. You have the option to execute database or transaction log restores. In addition, there are more commands available that permit checking the validity of the backup file as well as read the contents of the backup file prior to executing a restore.

DATABASE

Specifies the complete restore of the database from a backup device. This can either be a full database, differential or a filegroup restoration. If a list of files and filegroups is specified, only those files and filegroups are restored.

Database

RESTORE DATABASE {databasename} FROM {device}

Database and Differential

RESTORE DATABASE {databasename} FROM {device} WITH NORECOVERY

RESTORE DATABASE {databasename} FROM {device}

Filegroup

RESTORE DATABASE {databasename} FILE = {filename}, FILEGROUP = {filegroup} FROM {device} WITH NORECOVERY

RESTORE LOG {databasename} FROM {device}

LOG

Specifies a transaction log restore is to be applied to the database. Transaction logs must be applied in sequential order from the oldest backup to the most recent backup. SQL Server checks the backed up transaction log to ensure that the transactions are being loaded in the correct database and in the correct sequence. To apply multiple transaction logs, use the NORECOVERY option on all restore operations except the last restore command where the database recovery is needed. In addition, a transaction log restore must be executed following the database restore.

RESTORE DATABASE {databasename} FROM {device} WITH NORECOVERY

RESTORE LOG {databasename} FROM {device} WITH NORECOVERY

RESTORE LOG {databasename} FROM {device}

VERFIYONLY

Verifies the validity of the backup, but does not restore the backup. This process confirms that the backup set is complete and that all volumes are readable for SQL Server to restore the backup in the future. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. If the backup is valid, the following message is returned: "The backup set is valid."

RESTORE VERIFYONLY FROM {device}

FILELISTONLY

Returns a result set with a list of the database and log files contained in the backup set.

RESTORE FILELISTONLY FROM {device}

HEADERONLY

Retrieves the backup header information for all backup sets on a particular backup device.

RESTORE HEADERONLY FROM {device}

Tracking Tables

Several tables in the msdb database house all of the restore operations that occurred on the server. These tables are as follows:

• restorefile - Contains one row for each restored file, including files restored indirectly by filegroup name
• restorefilegroup - Contains one row for each restored filegroup
• restorehistory - Contains one row for each restore operation

Best Practices

Selecting the recovery model and backup options can be simple for your SQL Server implementation. The best scenario is to select the options that provide the most flexibility. The following are some guidelines that can be used for selecting the appropriate backup and recovery model as well as some additional considerations to institute.

Recovery Model Selection

If you are unsure what recovery model to use, the best bet is to implement the FULL recovery model. This option is the most flexible and gives you the most options. It allows recovery for the most amount of data in the event of a failure. Even if the FULL recovery model is selected, you are still free to choose the individual implementation backup options.

Backup Options

The best method is to perform full backups as often as possible depending on the size of your database, along with differential backups and lastly with a combination of transaction log backups. The frequency is dependent on your environment and the acceptable data loss for your company. If you have extremely large databases it will be difficult to execute frequent full backups, so you need to look at a combination of options.

A good starting point might be the following backup schedule:

- Execute a full database backup on a daily basis

- Perform transaction log backups every 15 minutes during the business day

- Complete differential backups every 4 hours

Rationale - The differential backups will minimize the number of transaction log restores needed. If you backup the transaction logs every 15 minutes, a maximum of 15 transaction logs would need to be restored. The worse case scenario would be 18 restorations in order to bring your database online and running. The 18 restorations would be one full, one differential, the 15 transaction log restores and one last transaction log. This last log would be from your attempt to backup your active transaction log if possible, before you begin your restores.

Backup to Disk First

Backing up databases to disk first gives you the fastest means for performing database backups and restores. In the event that a database restore is needed, the most recent backups are on disk instead of having to request tapes to complete the restoration.

Disk backups give the DBA the most control. As a DBA you will have more control over the backup schedule. You know exactly when backups are going to start and exactly when they should finish. You do not need to worry about other variables outside of your database server to determine when and if good backups occurred. It is still necessary to coordinate with your Backup Operator in your organization to make sure the tape backup occurs after the database disk backup, so the latest backup is stored on tape.

When backing up the databases to disk, ensure you are backing up the database to a different physical drive on a different controller card. In the event of a disk failure, if you use the same drives for the database and backup you risk the loss of your databases and backups.

If you have the ability to backup to a different machine or a network appliance, this option allows for superior level of redundancy in the event of a failure.

Archive to Tape

Several backup vendors in the marketplace can assist with managing and verifying the success of your tape backups. These products should be part of your backup strategy, but should read the backup files from disk instead of executing backups directly from your databases. Several vendors offer external agents to read the SQL Server databases directly, but the recommendation is to write the backup to disk first and then to tape second. This method also gives you two full sets of backup files.

Test

Irregardless of the backup method, it is advantageous to periodically test the validity of the backups. It is in your best interest to randomly select databases to restore onto test servers to ensure the restore functionality works properly and is meeting your expectations. The more frequently restoration testing is conducted, the better you will prepared for a real recovery.

Verify

Take the time to verify the backup is valid. The verify option listed above allows you to have peace of mind that the backup was successful. The last thing you want is to find out that the backup will not successfully perform the restoration. Take the extra time to run the RESTORE with VERIFYONLY option to ensure the backup was successful and is available when needed.

System and User Databases

Ensure the backup procedures include all system and user databases. In the event of a complete system failure, it will be necessary to restore the system databases along with your user databases in order to recreate the SQL Server environment.

Faster and Smaller Backups

Tight on time and disk resources for your SQL Servers? SQL LiteSpeed is a superior tool to address both of these issues, backup/restore time and minimal disk space. SQL LiteSpeed delivers the same functionality as the native BACKUP and RESTORE functions that ship with SQL Server, but with an incredible amount of time and disk savings.

SQL LiteSpeed mimics the commands used for all BACKUP and RESTORE functionality through a set of SQL Server extended stored procedures. Basically all of the functionality mentioned above is available through these extended stored procedures. The only difference is how the commands are constructed and the significant time and disk space savings.

This product is available for SQL Server 2000 and 7.0 and runs on both Windows NT and Windows 2000.

Time Savings

The following chart demonstrates the time savings by leveraging SQL LiteSpeed compared to traditional backup commands. As you can see in the chart below the time is reduced by 50% or more. Actual time savings depends on your hardware and your databases, but in most cases you will realize 50% or more reduction in the amount of time to complete the backup.

SQL LiteSpeed offers the first available configuration options to improve the speed for the backup and restore process not available from any other vendor in the SQL Server marketplace. The optimal configurations are accomplished by setting the number of threads, priority and latency for the SQL LiteSpeed backups and restorations. The configurations are dependant on the server resources to include the number of CPUs and memory which can be devoted to the backup process in conjunction with the remainder of the SQL Server processing. These configuration options enable a DBA to schedule backups as needed and be able to determine the appropriate amount of resources for the server based on the processing load. This performance tuning feature is unprecedented and is not available with other backup utilities.

In addition to the time savings for backups, there is also a time savings for restores. During a critical failure, every second is valuable!

Source - SQL LiteSpeed - Advanced SQL Server Backup - http://www.sqllitespeed.com/slsdefault.asp

Disk Savings

The following chart demonstrates the disk savings by implementing SQL LiteSpeed compared to traditional backup commands. As you can see in the chart, the disk space needs are reduced by 60% or more. You can see that even with small databases there is still a large space savings. If you add up all of your 1GB and smaller databases you can save a significant amount of disk space. In addition, these smaller files will benefit you when you need to copy these files around your network.

There are also options to allow you to further compress the size of the backup, but our testing shows that the increased time it takes for the backup with further compression does not offset the space savings enough. The product is fairly well optimized using the default settings for compression, but you should test different options in your environment.

Source - SQL LiteSpeed - Advanced SQL Server Backup - http://www.sqllitespeed.com/slsdefault.asp

Encryption Capabilities

SQL LiteSpeed utilizes 128 bit encryption which provides one of the strongest levels of protection against data misuse either on site or while a tape is stored off site for disaster recovery purposes. Unfortunately, if the encryption key is lost or forgotten it is not possible to restore the backup. Therefore, having the information stored in a secure area with secure staff is crucial for the recovery process.

Command Comparison

The following chart displays a sample of how the native SQL Server commands compare to the SQL LiteSpeed commands. As you can see, the commands are very similar and all the functionality is replaced by using the SQL LiteSpeed extended stored procedures. 

(To simplify the illustration parts of the command that are very similar have been reduced to …)

Summary

Based on this analysis, a few items must be addressed when developing the backup and recovery procedures for your SQL Server environment. It is necessary to think about what you are trying to recover from, how much downtime users can endure and the acceptable amount of data loss in the event of a failure. 

Several options are available that can be leveraged when constructing the BACKUP and RESTORE commands. These additional options can be found on SQL Server 2000 Books Online.

Along with the BACKUP settings, ensure a comprehensive Disaster Recovery (DR) plan has been developed, tested and implemented. Executing backups and working through a restore process is a good start for a SQL Server Disaster Recovery plan. Documentation is a key component to a SQL Server DRplan and should begin with the choices you have selected for your recovery model.

For installations where disk space is limited and time is of the essence take a look at SQL LiteSpeed. The enhancements you get by using this product can benefit every SQL Server installation. You can find more information about SQL LiteSpeed at http://www.sqllitespeed.com/.

Greg Robidoux Overview

Greg Robidoux is the founder of Edgewood Solutions a database solutions company in the United Statesand is currently the Vice Chair of the PASS DBA Special Interest Group. He has 14 years of IT experience and has been working with databases for the last 10 years with the past four years of that with SQL Server. Greg’s primary areas of focus are setting standards, disaster recovery, security and change management controls. In addition to these areas he has experience with replication, storage areas networks and SQL Server upgrades. Greg can be reached at gregr@edgewoodsolutions.com.

Edgewood Solutions Overview

Edgewood Solutions is focused on supporting the Microsoft SQL Server platform and delivering database solutions to further enhance the investments companies have already made based on this product platform. We recommend and implement processes that are often overlooked, but should be part of every database installation. In addition, we have partnered with the following companies that we feel enhance the SQL Server platform: Lumigent, DBAssociates and Precise Software Solutions.

Our employees have taken an active role in the SQL Server community. We are members of PASS and one of our employees is the Vice Chair of the PASS DBA Special Interest Group. We also had the distinct opportunity to provide two presentations at the PASS Summitin Seattle, ‘Change Management for SQL Server’ and ‘Successful Project Management for Database Administrators’.

Edgewood Solutions SQL Server services include:

• Change Management
• Security
• Disaster Recovery
• Performance Analysis and Tuning
• Project Management
• Database Maintenance
• Database Upgrades

For additional information about Edgewood Solutions visit http://www.edgewoodsolutions.com/.