Search code examples
sql-servertransaction-logdisaster-recoverylog-shipping

SQL transaction log shipping after differential backup


I'm facing a scenario:

  1. Full backup of production database is created
  2. Full backup is restored to DR database
  3. A differential backup of production database is created, BUT accidentally deleted
  4. Start transaction log backup on production database

The question is, will the first generated transaction log would be able to be restored into DR database, or the deleted differential database backup must be restored to DR database first?


Solution

  • The short answer is no, you need a/the differential backup or a new full backup. So either take a new full backup to restart the backup chain, or a new differential backup to replace the lost/deleted one.

    Simplified, a Transaction Log backup will maintain a chronological list of changes made to your database since the last backup (Any backup; either full, differential or another transaction log backup). A differential backup will save a list of all extents that changed since the last Full Backup (so including whatever any transaction log backup already backed up in the mean time). This also means that if you create a full backup, followed by a differential backup, followed by a differential backup, the second differential backup contains everything the first one contains.

    A typical scenario is where a full backup is taken, followed by a few transaction log backups. The next step then is a differential backup, and probably more transaction log backups and differential backups until a new full backup is created.

    This ensures the time to recovery is reduced by ensuring that in case of disaster, you can take a backup of the tail of the transaction log. You can then restore the full backup, followed by the latest differential backup, followed by any transaction log backups (in chronological order).