Database Backup Plan

A full database backup copies all the data and the transaction log to a backup device, which is usually a tape or disk drive. It is used in case of an unrecoverable failure, so that the database can be restored to that point in time. Additionally, a full database restore is required as a starting point for differential or transaction-log restores. The backup should stored offsite so that the database is not lost in the event of a disaster.

A differential database backup copies all modified extents in the database since the last full database backup. An extent is a unit of space allocation that consists of eight database pages that SQL Server uses to allocate space for database objects. Differential database backups are smaller than the full database backup, except in certain scenarios where the database is very active and every extent is modified since the last full backup. In terms of restoring from a differential database backup, a full database backup is required prior to the differential backup.

Full or differential database backup operations will not break log shipping, provided no transaction-log operations are performed that change it. There are several considerations to backup operations with log shipping, however:

• The database backup process and transaction-log backup cannot be run concurrently. Therefore, in a large, active database where the database backup can take some time, the transaction log may grow and the secondary server go out of sync, as it is not receiving the transaction-log restores in the same timely manner until the database backup completes.

• Another transaction-log backup cannot be performed in addition to log shipping, because that will break the log chain for the log-shipping process. SQL Server will not prevent an operator from creating additional transaction-log backup jobs on a log-shipping database.

• A transaction log backup that truncates the transaction log will break the log chain, and log shipping will stop functioning.

• If the database is changed to the simple recovery model, the transaction log will be truncated by SQL Server and log shipping will stop functioning.