Database Reference
In-Depth Information
You should create differential backups often enough to minimize the number of log backups that need to be
restored and log records that need to be replayed in case of recovery. Differential backups are cumulative, though, and
you should avoid the situation where they store a large amount of data modified since the last full backup. It would be
better to perform full backups more often in that case.
As an example, consider a database that collects some data from external sources, keeping one week of the most
recent data and purging it on a daily basis using a sliding-window pattern implementation. In this schema, one-seventh
of the data is changing on a daily basis.
Let's assume that a full backup is taken weekly and differential backups are taken daily. If the size of the full backup
is 1TB, the incremental backups would grow at a rate of 140-150MB per day. In that case, if a disaster happened on the
seventh day after the last full backup, you would need to restore 1TB of full backup and about 850MB of differential
backups before applying log backups, which is very time consuming and redundant. It would be much more efficient
to perform full backups on a daily basis in that case.
■
Differential backups back up extents that have been changed since the last full backup. Modified extents
are backed up only once; regardless of how many times the data in those extents changed. therefore, the size of a
differential backup depends on the location of the modified data in the database in addition to the number of changes
performed since the last full backup.
Note
The location of backup files is another important factor that affects recovery time. It could be very time consuming
to copy a large amount of data over the network. Consider keeping multiple copies of backup files when it is
appropriate—off-site, on-site, and perhaps, even locally on the server.
■
When fast system recovery is crucial, you can consider striping backup across multiple local Das drives,
copying backup files to other servers and offsite locations afterwards. this will protect you from various types of failures
and provide the best performance of backup and restore processes.
Tip
I/O subsystem and network performance are usually the biggest bottlenecks during backup and restore.
Backup compression helps to reduce the size of the data that needs to be transmitted over the network or read from
disk. Always use backup compression if the database is not encrypted and the server can handle the extra CPU load
introduced by compression.
■
Backup compression affects the duration of backup and restore operations. sQL server spends extra time
compressing and decompressing data; however, that can be mitigated by a smaller size of backup file and therefore,
the amount of data transmitted over the network and/or read from disk. Make sure that you can still achieve rtO after
you implement backup compression.
Tip
Consider using third-party backup compression tools if you are using a version of sQL server that does not support
backup compression.
One of the key elements of a good backup strategy is backup validation. It is not enough to back up the database.
You should make sure that backup files are not corrupted and that the database can be restored from them. You can
validate backup files by restoring them on another server.
