Selecting a Recovery Model

The trunc. log on chkpt and select into/bulk copy options have been replaced with three recovery models in SQL Server 2000. To simplify recovery planning and backup and recovery procedures, you can select one of the recovery models shown in Table 1.3 from the database Properties dialog box on the Options tab.

Table 1.3 SQL Server 2000 Recovery Models


Recovery Capabilities

Previous Version Settings

Simple Recover up to the last successful backup. Remaining changes must be redone.

Full Recover to any point in time.

Bulk-Logged Recover up to the last successful backup. Remaining changes must be redone.

Trunc. log on chkpt: True Select into/bulkcopy: True or False

Trunc. log on chkpt: False Select into/bulkcopy: False

Trunc. log on chkpt: False Select into/bulkcopy: True

Each recovery model offers certain advantages for performance, space requirements, and data loss recovery. The simple recovery model requires the least amount of resources; log space is reclaimed because it is no longer needed for server recovery, similar to the trun. log on chkpt. option in previous versions of SQL Server. The disadvantage here is that data recovery to a particular point beyond the latest backup is not possible. Simple recovery should not be used where data recovery is critical and reentry is not possible.

Full and bulk-logged recovery models offer greater data recovery capabilities. Full recovery supports recovery to any point in time given that the current transaction log is not damaged. Bulk-logged recovery provides similar capabilities to the full recovery model, but bulk operations such as SELECT INTO, bulk loads, CREATE INDEX, image, and text operations are minimally logged, increasing your exposure to data loss in the event of a damaged data file. Selecting between full and bulk-logged recovery is dependent on your database structure and data operations. If complete data recovery is essential and the performance of bulk operations is not critical to your application, you should select the full recovery model for your database.

Computer Hard Drive Data Recovery

Computer Hard Drive Data Recovery

Learn How To Recover Your Hard Drive Data After A Computer Failure.

Get My Free Ebook

Post a comment