SQL Server 2005 Database Maintenance

Exam Alert

The topics related to database maintenance represent a significant portion of the material on the 70-431 exam. You can expect to see several questions about the topics of backups, restoration, jobs, schedules, snapshots, and other related maintenance activities.

Database maintenance involves backing up and restoring data. You also need to consider automating maintenance procedures and how to use the information and tools available to ensure that maintenance processes are actually being performed as designed.

What's New in SQL Server Maintenance?

Although many of the concepts of database maintenance in a SQL Server 2005 environment remain the same as with previous SQL Server releases, you will find several new features. Like most other Microsoft exams, the 70-431 exam tests knowledge of the new features. In the data backup and recovery realm, several new features give administrators mechanisms for handling activities in a more efficient and reliable fashion.

There is significant overlap in topics between the 70-431 exam and other related exams. Many of the newer features are approached in the other exams if you continue with SQL Server 2005 certification. New features seem to be 70 to 80 percent of the actual exam material. The following are the new features you are likely to see on the 70-431 exam:

• Database snapshots Snapshots provide a way to revert to a point of consistency. A database snapshot records the current state of the data at the point when the snapshot is created. Any later active transactions are not included. You can later revert the database to the state it was in at the time of the snapshot.

• Partial database restores Users can access databases when a partial restore is performed. Partial restores can occur against the database at the file or page level, while allowing users access to any unaffected files and pages. Users cannot access the part of the database being restored until it is recovered.

• The CONTINUE_AFTER_ERROR option In SQL Server 2005, you can now continue the backup and restore operations after a failure. This option allows the process to continue even if it receives an error. If multiple problems exist, the administrator can assess the scope and then resolve the problem.

• Fast recovery SQL Server 2005 Enterprise Edition allows fast recovery during crash recovery and mirroring failover. The data becomes available during the undo phase, with partial availability during restore operations, database page checksums, and backup media mirroring. The other editions of SQL Server 2005 do not let users access the database until recovery completes.

• The RESTORE VERIFYONLY and BACKUP VERIFYONLY options If the TORN_PAGE_DETECTION or CHECKSUM database options are set, the VERIFYONLY option allows the integrity of data pages to be validated during backup and restore operations.

• Mirrored backup sets You can perform backups to mirrored sets to reduce the impact of the possible loss or corruption of one of the backups. If a backup device fails, a backup mirror can be used to restore the database.

Exam Alert

Maintenance is a central focus of the 70-431 exam, and you will therefore find numerous questions about the various maintenance features. Of course you will be tested on the newer features, and you will also be tested on features present in previous versions of the product.