Database backups and transaction log backups are used to provide full recoverability from media failure. All operations, including bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data, are fully logged.
Logging for all SELECT INTO, CREATE INDEX, and bulk loading data operations is minimal and therefore requires less log space.
The database can be recovered only to the last full database backup or last differential backup.
Detection of Incomplete I/O Operations
This group can be used to specify three options to discover incomplete I/O transactions caused by disk I/O errors:
A checksum is taken over the contents of the entire page and stored in the page header when a page is written to disk. When a page is read from disk, the checksum is recomputed and compared to the checksum value stored in the page header.
A bit is reversed for each 512-byte sector in the 8-kilobyte (KB) 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 was written incorrectly; a torn page is detected.
PAGE_VERIFY is set to OFF and future data page writes will not contain a CHECKSUM or TORN_PAGE_DETECTION bit and the page will not be verified at read time.