asked
TLog Back Up strategy
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).
It doesn't matter which user takes a transaction log backup as long as those backups are available when it's time to restore. But if user A takes a backup to D:\mybackups and User B takes a backup to E:\mybackups and you don't know about both those locations when you go to restore the database, you may have a broken log chain which will prevent point in time recovery. No one should be taking manual log backups. That's something that should be automated on your system.
It doesn't matter who is running the backup, they only need rights to start a backup. But I suggest to create a job for every backup action
16 People are following this question.
