Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

In simple terms, I need point-in-time restore between full backups - what are the consequences in the following plan - the database is in full recovery mode:

  • 1:00 AM Transaction Log Backup (no Truncation or Shrinking)
  • 1:30 AM Full Backup
  • 2:00 AM Transaction Log Backup (Truncate and Shrink)
  • Then every 30 Minutes Transaction Log Backup (no Truncation or Shrinking)

Will this provide me with the ability to point-in-time restore the database at any point except for the time between 1:30am and 2am?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

That is a good many log backups to keep up with, if from 2:00 AM till 1:30 AM the next morning you are taking log backups. You might through in a differential backup if you have the window to do so during the day.

Your plan will allow for no more than 30 minutes of data loss. If the database goes down between those 30 minute log backups that is the amount of data you can loose. If your business or application can handle that much data loss then your plan is good for your environment.

Why truncate/shrink? If you are doing log backups that is what helps control the log file growth so there should be no reason to truncate the log. Are you shrinking just the log file or the complete database? Neither is suggested or really needed if you are maintaining a sufficient log backup schedule to control log growth.

share|improve this answer
    
Agreed. Not sure why the shrinking is in there. –  Tatas Jan 24 '12 at 15:18
    
Agreed. In fact, if I'm reading correctly the scenario proposed by the question won't actually work. If you truncate the log AFTER the full backup, you're going to break the log sequence and every subsequent transaction log backup will be unusable. So you'll be able to do point in time recovery between 1:30AM and 2:00AM, but not thereafter. –  bluefooted Jan 25 '12 at 0:03
    
Thanks, have removed shrinking. On the Truncation feedback, I have changed the transaction log backup to no init without truncation etc, presumably that will still control log growth? –  Chris Jan 25 '12 at 10:33
    
Actually I just remembered now from reading that WITH TRUNCATE_ONLY and WITH NO_LOG have been discontinued and do not work after SQL Server 2008. --Yes to your question a log backup on a regular basis will help control the log file growth. That is not to say it may not still grow during heavy activity, but it keeps it from going out of control. –  Shawn Melton Jan 25 '12 at 14:20
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.