Professional Microsoft® SQL Server® 2008 Administration

19.8. 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. A full database restore is also required as a starting point for differential or transaction-log restores. The backup should be 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. To restore 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. Note several considerations with backup operations using log shipping, however:

• The database backup process and transaction-log backup cannot be run concurrently. Therefore, in a large, active database for which the database backup can take some time, the transaction log may quickly grow, causing the secondary server to become out ...