Two Different Sources Taking Transaction Log Backups

Question

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.

Answer 1

I realized that there are two sources taking full back ups overnight and hourly transaction log backups: TDP (part of IBM's TSM) and a regular SQL job.

If this is the case then, you will end up breaking your log chain and a point in time recovery would not be possible.

Either use TDP (part of IBM's TSM) or a regular SQL job to back-up Transaction logs.

Also, I would suggest you to simulate a recovery scenario on a test server with either TDP or using SQL Native backups (Full backup, differential (if any) and then subsequent log backups) to ensure that in an unfortunate situation of disaster, you would be able to recover your databases.

Note: Full backup wont break the log chain.

Answer 2

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.