1

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?

1 Answer 1

2

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.

4
  • Agreed. Not sure why the shrinking is in there.
    – Tatas
    Jan 24, 2012 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.
    – Pam Lahoud
    Jan 25, 2012 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, 2012 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.
    – user47078
    Jan 25, 2012 at 14:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .