Reliability Enhancements

Database backups are a key component of any disaster recovery plan. In most IT environments, backups are the last line of defense against data loss. A well-designed and tested backup and restoration strategy can minimize data loss and downtime in case of a loss of data access due to human error, system malfunction, or some other system failure. SQL Server 2005 introduces enhancements that allow you to reliably and efficiently back up your databases and reduce the downtime by using features such as instant file initialization and online restorations.

Mirrored Backup Media

Redundancy is one of the common techniques used to increase the reliability of a system. SQL Server 2005 uses this technique and allows writing, or mirroring, of data to multiple backup devices simultaneously. Mirroring a media set increases backup reliability by reducing the impact of backup-device malfunctions. Mirrored backup media support is provided via the new MIRROR TO clause with the BACKUP T-SQL statement. A single BACKUP statement can contain up to three MIRROR TO clauses, for a total of four mirrors (including the mirror created by the TO clause). Let's look at a couple examples of performing database and log backups, using mirrored backup media.

The following statement performs a full database backup, creating two additional mirrored backup copies on the E: and F: drives:

USE master; GO BACKUP DATABASE AdventureWorks    TO DISK='C:\AWCopy1.bak'    MIRROR TO DISK='E:\AWCopy2.bak'    MIRROR TO DISK='F:\AWCopy3.bak' WITH FORMAT; GO

This backup set consists of a single backup media family having three media sets, each having a single backup media disk file, called a device.

The following statement performs a full database backup, creating an additional mirrored backup copy on the E: drive:

BACKUP DATABASE AdventureWorks    TO DISK='C:\AWCopy1a.bak', DISK='C:\AWCopy1b.bak'    MIRROR TO DISK='E:\AWCopy2a.bak', DISK='E:\AWCopy2b.bak' WITH FORMAT, STATS = 5; GO

This time, each media set consists of two backup media disk files, or devices. Each device (for example, C:\AWCopy1a.bak or C:\AWCopy1b.bak) holds approximately half of each backup set. Up to 64 devices may be specified with the TO clause or the MIRROR TO clause. If you specify n devices in the TO clause, then the same n number of devices must also be specified with each MIRROR TO clause.

The backup set created in the preceding example consists of two media families. The devices C:\AWCopy1a.bak and E:\AWCopy2a.bak form the first media family, and the devices C:\AWCopy1b.bak and E:\AWCopy2b.bak form the second media family.

You can access the following tables in the msdb system database to view the backup history and other details:

SELECT * FROM msdb.dbo.backupset; SELECT * FROM msdb.dbo.backupmediaset; SELECT * FROM msdb.dbo.backupmediafamily; SELECT * FROM msdb.dbo.backupfilegroup; SELECT * FROM msdb.dbo.backupfile;

The following BACKUP statement fails because the second MIRROR TO clause does not have two devices like the TO and the first MIRROR TO clause:

BACKUP DATABASE AdventureWorks    TO DISK='C:\AWCopy1a.bak', DISK='C:\AWCopy1b.bak'    MIRROR TO DISK='E:\AWCopy2a.bak', DISK='F:\AWCopy3b.bak'    MIRROR TO DISK='F:\AWCopy3a.bak' WITH FORMAT, STATS = 5; GO

For mirrored backup media functionality, all the devices in a media family must be equivalent (for instance, tape drives with the same model number from the same manufacturer). Backup and restore operations impose different requirements on whether all the mirrors must be present. For a backup operation to write (that is, to create or extend) a mirrored media set, all the mirrors must be present. In contrast, a restore operation can read the media for only one mirror per media family at a time. In the presence of errors, however, having the other mirror(s) enables some restoration problems to be resolved quickly. This is because RESTORE and RESTORE VERIFYONLY support substitution of damaged media with the corresponding backup-media volume from another mirror.

Backup and Restore Media Checks

The BACKUP and RESTORE statements now support the CHECKSUM clause to enhance the reliability of the backup and restore operations. The CHECKSUM clause introduces an additional verification and error detection step during backup and restoration.

When the CHECKSUM clause is specified with the BACKUP statement, SQL Server computes a backup checksum on the backup stream and records it on the backup media. In addition, SQL Server also verifies the page-level information, such as page checksum or torn page detection, if either exists. If page checksum or torn page detection information is not available, BACKUP cannot verify the page and silently skips it. The has_backup_checksums column in the msdb..backupset table can be used to determine whether a backup checksum is present with the backup set.

Note

Generating and verifying backup checksum and page-level information may affect the performance and backup/restoration throughput. This is why the default setting for BACKUP and RESTORE is NO_CHECKSUM, which disables page validation and the generation or verification of backup checksums.

Similarly, the CHECKSUM clause can be specified with the RESTORE and RESTORE VERIFYONLY statements. If the CHECKSUM clause is specified, both the RESTORE and RESTORE VERIFYONLY operations verify the backup checksums and page checksums.

When the CHECKSUM clause is specified, if BACKUP encounters a page error during verification, the backup fails. You can specify CONTINUE_AFTER_ERROR to instruct BACKUP to continue despite encountering an invalid backup checksum. In such cases, BACKUP logs an entry in the SQL Server error log and to the msdb..backupset (is_damaged field) and msdb..suspect_pages tables. It also issues a message that the backup was successfully generated but contains page errors.

Unlike in previous releases, the RESTORE VERIFYONLY now performs a thorough analysis to ensure that the backup set is really good and reliable.

Checksum I/O Validation

You can use the new PAGE_VERIFY clause with the ALTER DATABASE statement to discover incomplete I/O transactions caused by disk I/O errors. Disk I/O errors can cause database corruption problems, and they are usually the result of power failures or disk hardware failures that occur at the time the page is actively being written to disk. The PAGE_VERIFY clause provides the following three options:

• CHECKSUM This is the default PAGE_VERIFY option. A checksum is calculated using the contents of the entire page and stored in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value stored in the page header. If the values do not match, error message 824 is reported to both the SQL Server error log and the Windows Event Viewer. The database engine distinguishes between an I/O error detected by the operating system (error 823) and an I/O error detected by the SQL Server PAGE_VERIFY CHECKSUM option (error 824). In addition, you can validate the page checksums during backup and restore operations by using the CHECKSUM clause with the BACKUP/RESTORE statement, as discussed earlier in this chapter.

• TORN_PAGE_DETECTION With this option, a bit is reversed for each 512-byte sector in the 8KB database page when the page is written to disk. If a bit is in the wrong state when the page is later read, the page is assumed to be written incorrectly; a torn page is detected.

• NONE You use this option to turn off the PAGE_VERIFY functionality. Future data page writes will not contain a checksum or torn page detection bit, and the page will not be verified at read time, even if a checksum or torn page detection bit is present.

Online, Piecemeal, and Page Restorations

Online restoration is a new feature available in SQL Server 2005 Enterprise, Evaluation, and Developer Editions. It can be used for the following:

• Databases containing multiple filegroups and using the full or bulk-logged recovery models

• Databases containing multiple read-only filegroups and using the simple recovery model

In earlier versions of SQL Server, the basic unit of availability during a restoration was the entire database. Such restorations are known as offline restorations because the database is not available while the restoration is in progress. In SQL Server 2005, filegroups are the basic unit of availability. During a restoration, a database can be online, but some filegroups may not be available. You first restore the primary filegroup, and it becomes available immediately; you then restore other filegroups by priority, and each becomes available as it is restored. Only the filegroups in the process of being restored or that are not restored yet remain offline; the rest of database is online and available. If a client application tries to access data from an unavailable filegroup, it receives an error. This type of online restore operation is known as a piecemeal restoration. Data should be carefully distributed on filegroups in order to make the best use of this feature. Piecemeal restorations can be considered an enhancement over SQL Server 2000 partial restorations. In addition to restoring critical data first, a piecemeal restoration can also be helpful in restoring a damaged file or set of files.

Note

SQL Server 2005 allows you to perform offline restorations of backups from SQL Server 7.0 and 2000. However, online restoration is not supported from backups from earlier versions of SQL Server. SQL Server 2005 does not support restorations of backups from SQL Server 6.x and earlier.

The other online restoration type introduced in SQL Server 2005 is page restorations. You can use the new PAGE clause with the RESTORE statement to restore and fix one or more pages that have been detected as corrupted by check-summing or a torn write. Like piecemeal filegroup restoration, a page restoration is by default an online restoration option, meaning that the database is online for the duration of the restore, and only the data that is being restored is offline. If you want a piecemeal or a page restoration to be an offline restoration, you can use the WITH NORECOVERY clause with the RESTORE statement.

Note

In SQL Server 2005, you can use the BACKUP and RESTORE statements to back up and restore full-text catalogs along with other database data. The backup operation treats full-text catalogs as files. During the backup, the catalog is put into a read-only mode so that the process of creating and maintaining a full-text index is suspended until the backup completes. To back up only the full-text catalog, and not the database data, you specify the FILE clause in the BACKUP command. To back up only the filegroup that stores multiple full-text catalogs, you specify the FILEGROUP clause in the BACKUP command.

The EMERGENCY Database State Option

In case of restoration errors due to damaged backups or trying to recover a corrupt database, you can change the database state to EMERGENCY by using the ALTER DATABASE statement. You can put a database in EMERGENCY state to gain limited access to the data as is. If database is marked as suspect, you can change the database state to EMERGENCY to either gain read-only access to the database or to detach a suspect database.

Emergency mode changes the database to a single-user and read-only database, disables logging, restricts access to members of the sysadmin fixed server role, and allows repair or restoration of the database.

The following script places the AdventureWorks sample database into EMERGENCY state by using the ALTER DATABASE statement:

--connect to an instance using a member of sysadmin fixed server role USE master; ALTER DATABASE [AdventureWorks] SET EMERGENCY; GO SELECT DATABASEPROPERTYEX('AdventureWorks', 'Status'); SELECT name, state, state_desc FROM sys.databases; GO USE AdventureWorks GO SELECT * FROM Sales.Store; BEGIN TRAN GO --Following statement will fail UPDATE Sales.Store SET [Name] = 'X' + [Name]; GO ROLLBACK TRAN GO --following statement will also fail BACKUP DATABASE [AdventureWorks] TO DISK='c:\temp\test.bak'; GO USE master; ALTER DATABASE [AdventureWorks] SET ONLINE; GO SELECT DATABASEPROPERTYEX('AdventureWorks', 'Status'); SELECT name, state, state_desc FROM sys.databases; GO

You can use the DATABASEPROPERTYEX function or sys.databases catalog view to view the state of a database. The preceding script illustrates that updates and database backup are disallowed if a database is put in emergency mode. Also note that databases in emergency mode cannot be moved or copied by using the Copy Database Wizard.

The ATTACH_REBUILD_LOG Clause

Consider a scenario where you have an OLTP database with a very large log file(s), and you want to copy this database on another server for mostly or only read operations. In such a case, you can avoid copying the log file. You simply shut down the database properly, copy all the .mdf and .ndf files (there's no need to copy the log .ldf files), and run the CREATE DATABASE statement on another server, along with the ATTACH_REBUILD_LOG clause. The ATTACH_REBUILD_LOG clause instructs SQL Server to rebuild the log for the database being attached. Note that this operation breaks the log backup chain. It is recommended that a full database backup be performed after the operation is completed. The sp_attach_single_file_db system stored procedure available in previous releases to perform similar tasks is being deprecated, and it is recommended that you instead use FOR ATTACH_REBUILD_LOG with the CREATE DATABASE statement.