patternsqlModerate
Two Different Sources Taking Transaction Log Backups
Viewed 0 times
backupslogdifferenttwotransactionsourcestaking
Problem
I have inherited a database server with SQL Server 2005. The databases have full recovery mode with hourly transaction log backups. There are two sources taking full backups overnight and hourly transaction log backups: Tivoli Data Protection (TDP) which is part of IBM's Tivoli Storage Manager (TSM) as well as a regular SQL Server Agent job.
If a disaster occurred and I needed to recover the database from backup, would I be missing data?
I was thinking that if the SQL transaction log backup fires off, it will mark the committed transactions in the log file as ready to be overwritten. After that happens, users will commit more transactions. When TSM/TDP comes in, it will mark the committed transactions in the log as ready to be overwritten. If I were to do a restore from either backup (unless it's using only the full backup), then it would not include all of the data.
If a disaster occurred and I needed to recover the database from backup, would I be missing data?
I was thinking that if the SQL transaction log backup fires off, it will mark the committed transactions in the log file as ready to be overwritten. After that happens, users will commit more transactions. When TSM/TDP comes in, it will mark the committed transactions in the log as ready to be overwritten. If I were to do a restore from either backup (unless it's using only the full backup), then it would not include all of the data.
Solution
If a disaster occurred and I needed to recover the database from backup, would I be missing data?
As long as all of the backups are in tact, no.
The transaction log chain is not broken, and point-in-time recovery is possible.
It's just that the backups that constitute a complete transaction log chain are not all in the same location.
Having said that, I would recommend consolidating the systems so that only one of them is responsible for taking backups. While it sounds like your situation was set up that way unintentionally, if you want to create a redundant copy of a backup at an alternate location, there are many options to do so, but that's beyond the scope of the question here.
If I were to do a restore from either backup (unless it's using only the full backup), then it would not include all of the data.
Correct, you would not be able to restore from the TDP backups alone, or the SQL Agent backups alone.
After restoring the latest full backup, you would need to restore all of the log backups between that point-in-time and the current point-in-time. This will involve combining the set of backups from both locations, and restoring them in the correct sequence as each log backup will contain a distinct portion of the backed up transaction log.
As long as all of the backups are in tact, no.
The transaction log chain is not broken, and point-in-time recovery is possible.
It's just that the backups that constitute a complete transaction log chain are not all in the same location.
Having said that, I would recommend consolidating the systems so that only one of them is responsible for taking backups. While it sounds like your situation was set up that way unintentionally, if you want to create a redundant copy of a backup at an alternate location, there are many options to do so, but that's beyond the scope of the question here.
If I were to do a restore from either backup (unless it's using only the full backup), then it would not include all of the data.
Correct, you would not be able to restore from the TDP backups alone, or the SQL Agent backups alone.
After restoring the latest full backup, you would need to restore all of the log backups between that point-in-time and the current point-in-time. This will involve combining the set of backups from both locations, and restoring them in the correct sequence as each log backup will contain a distinct portion of the backed up transaction log.
Context
StackExchange Database Administrators Q#49021, answer score: 11
Revisions (0)
No revisions yet.