Please see my other Database Development articles.

Ensuring Recovery from a Disaster

This section describes how to recover data lost due to a disaster.
As described in the last section, each database regularly performs the following backup types:

• Full: A complete copy of the database at a point in time.
  • This occurs once a week.
• Differential: Changes to the database since the last full backup.
  • This occurs every 4 hours.
  • Note: Since this represents all changes since the last full backup, only the last differential backup needs to be restored.
• Transaction log: All changes (transactions from CRUD operations) since the last log backup.
  • This occurs every hour.
  • Note: Since each log backup captures all changes since the last log backup, all log backups since the last differential would need to be restored to ensure all changes were restored.

Additionally, after a disaster has occurred, the database administrator may manually perform a tail-log backup to capture changes since the last transaction backup – hence, the “tail” or end of the log.

Recovery Steps

Execute the following steps to restore a database to its most recent state containing all data/changes:
1) Create a tail-log backup.
2) Restore the most recent full backup.
3) Restore the most recent differential backup.
4) Restore all transaction backups since the last differential backup.
5) Restore the tail-log backup.

Create Tail-Log Backup

This section describes how to capture changes since the last transaction log backup.

Use the following t-sql after updating the database name and path.

Restore the most recent full backup

This section describes how to perform a full backup.

Restore the most recent differential backup.

This section describes how to restore a differential backup.
Note: Since the differential backup contains all changes since the last full backup, you only need to restore the latest differential backup.

Use the following t-sql after updating the database name and path.

Restore all transaction backups since the last differential backup.

This section describes how to restore a transaction log backups.
Note: Since you already restored the latest the differential backup, you only log backups after the differential backup.

Use the following t-sql after updating the database name and path.

(repeat the above script for each log to restore)

Restore the tail-log backup.

Repeat the last step to restore the tail-log backup.