ARTICLES

Perform backups during periods of low database access.
Because backup is very resource effective, try to schedule it during CPU idle time and slow
production periods.

If you need to restore one or more damaged pages without restoring the
whole database, you can use the Page restore scenario.
The Page restore scenario is used when the database is offline or, if you use SQL Server 2005 Enterprise Edition,
when the database remains online. During a page restore, the pages that are being restored are always offline.
Page restore is supported only for read/write filegroups that are using the full or bulk-logged recovery models.
SQL Server 2005 Standard Edition, Express Edition, and Workgroup Edition support only offline restore, while
SQL Server 2005 Enterprise Edition supports online page restore.

Use the new SQL Server 2005 CHECKSUM option of the BACKUP operation.
When this option is turned on the backup operation verify each page for checksum and also
generate a checksum for the entire backup. You can also control the backup response to an
error during checksum operation. There are two options to response on error:
CONTINUE_AFTER_ERROR and STOP_ON_ERROR. CONTINUE_AFTER_ERROR indicates that BACKUP will
continue despite encountering an invalid backup checksum. STOP_ON_ERROR indicates that BACKUP
will fail if a checksum does not verify.

Place a database into EMERGENCY mode when a database has been marked
suspect during recovery.
The EMERGENCY mode is a new SQL Server 2005 feature. It can be used to allow members of the
sysadmin fixed server role to have read-only access to the suspected database. So members of
the sysadmin fixed server role can diagnose the problem or retrieve any available data.

Consider initializing a transactional subscription from a backup.
This is a new SQL Server 2005 feature. You can initialize a transactional subscription from a
backup when you setup replication between databases that initially contain large volumes of data.
In this case, initializing a transactional subscription from a backup can boost performance
in comparison with using a snapshot to initialize the subscription.

Consider storing the backup files on another physical disks on another computer.
Storing the backup files on the same computer where the databases stores may cause problem
with restoring databases if the physical disks were damaged.

Use the full recovery model for the most production databases that are not read-only.
The full recovery model fully logs all transactions and retains the transaction log records until after they
are backed up. This recovery model allows a database to be recovered to the point of failure
and supports restoring individual data pages.

Perform backup on multiple backup devices.
Using multiple backup devices forces SQL Server to create a separate backup thread for each
backup device, so the backups will be written to all backup devices in parallel.

Perform backup on a physical disk array, so the more disks in array
the more quickly the backup will be made.
This can improve performance because a separate thread will be created for each backup device
on each disk in order to write the backup's data in parallel.

Use full backup to minimize the time to restore databases.
The full backups take the longest to perform in comparison with differential and incremental
backups, but are the fastest to restore.

Use incremental backup to minimize the time to backup databases.
The incremental backups take the fastest to perform in comparison with full and differential
backups, but are the longest to restore.

Use differential backup instead of incremental backup when the users
update the same data many times.
Because a differential backup captures only those data pages that have changed after the last
database backup, you can eliminate much of the time the server spends rolling transactions
forward when recovering transaction logs from the incremental backups. Using differential
backup, in this case, can improve the recovery process in several times.

Consider using the piecemeal restore.
The piecemeal restore scenario is used to restore and recover the database in stages at the filegroup
level, starting with the primary and all read/write secondary filegroups. This restore scenario is
available only in the SQL Server 2005 Enterprise Edition. The piecemeal restore scenario is used only
with the full or bulk-logged recovery models.

Use SQL Server 2005 snapshot backups for the very large databases.
The SQL Server 2005 snapshot backup and restore technologies work in conjunction with third party
hardware and software vendors. The main advantages of snapshot backups and restores are that they
can be done in a very short time, typically measured in seconds, not hours, and reduce the
backup/restore impact on the overall server performance. The snapshot backups accomplished by
splitting a mirrored set of disks or creating a copy of a disk block when it is written and
required the special hardware and software.