I am new to Database Management. I want to know something about transaction log backup in sql server 2008 r2. I took a full backup of my database in the first day. Then took tlog back up in second day. And tlog in third day too. On fourth day I took full back up. And on fifth day i took third tlog back up. So when time recovery came, I recovered full and all tlog back up files skipping second full backup. It worked. But my doubt is that how last tlog backup was able to restore with the second tlog file. Without the second full back up.? How tlog keeps its LSN number ? How it keeps the tlog chain even though its chain had broken at third time?
Hi AnujKC, if the database remains in FULL recovery or Bulk Logged, every transaction log since the first full could be applied to recovery the database. The LSN stays in sync for every log backup until you break the LSN by certain methods such as truncating the log or switching to simple recovery. It is not advised to rely on having to restore lots of days of transaction logs because if any of the logs are damaged, you can't restore the logs past the damaged one. Typically a good strategy is weekly full backups, daily differential backups and log backups based on your service level agreement for the acceptable recovery point objective. Most organizations I have found this to be 15 minutes (acceptable data loss of up to 15 minutes in this scenario).