Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
Thank you, Kin! –  Eric Enger Aug 30 '13 at 15:08
add comment

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.

share|improve this answer
    
Well put. Not an impossible scenario, just not optimal! –  Max Vernon Aug 30 '13 at 23:34
    
And thus a much better answer than the one accepted, which is technically wrong (as it IS Possible to restore point in time). –  TomTom Aug 31 '13 at 9:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.