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.

The Setup:

Say we have a Database in Full Recovery Mode that does a nightly Database Backup, Transaction Log Backup, and Transaction Log Shrink.

Now the Transaction Log grows so quickly during the day, so we need to perform a Transaction Log Backup + Transaction Log Shrink every 6 hours to keep it small.

The Question:

Given these sequence of events (specifically the every-six-hours Transaction Log Backup/Shrink), will we still be able to perform an "Up-to-the-Minute" Restore? (That is, using the last DB Backup + Transaction Log to get the most recent data).

share|improve this question
    
You say "nightly Transaction Log backup", 'Transaction Log backup every 6 hours", and then "hourly Transaction Log Backup"...which one is it? –  Shawn Melton Oct 5 '12 at 0:00
    
@ShawnMelton: There are 2 Transaction Log backups. 1 Nightly and 1 every 6 Hours. I've edited the question to make it more clear. –  Onion-Knight Oct 5 '12 at 12:09
    
Ah. What is the purpose of the Tlog backup at night? Do you do it right after the full, or is the every 6 hr backup only during a certain period of the day? –  Shawn Melton Oct 5 '12 at 14:03
    
@ShawnMelton: We do the Nightly TLog Backup after the DB Backup. We then do TLog backups every 6 hours (until the next Nightly Backups). –  Onion-Knight Oct 5 '12 at 14:45
add comment

1 Answer

up vote 9 down vote accepted

Shrinking your transaction log should not be part of your routine. A transaction log backup clears the log file automatically; shrinking it afterward just causes it to have to grow again afterward. Read this for more information.

Yes, you should be able to perform point-in-time restores using your latest full backup and any subsequent transaction log backups. Anything more recent than the latest transaction log backup will require that the server still be operational enough to take a tail-log backup

The critical point is to test it. Backups are not reliable until you know from experience that you can restore the data successfully. This article and the links at the bottom of that article have some good information about SQL backup strategies.

share|improve this answer
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.