2

We have a 2012 SQL Server and I am looking into how it is being backed up currently. We do have a shadow copy that runs hourly. We also have a nightly full backup that is taken. DB runs in FULL mode.

My issue is my log files seem to be growing very large quickly. I have read that log backups as often as every 5 minutes. I know that shrinking the log files often is not a good solution. I want to make sure that we have full recovery options.

If I take a nightly backup, then take a log backup right after will that leave a break in consistency if there happens to be transactions going on at that time?

Taking a log backup should free up space in the log to prevent file growth, leaving a file large enough for a days activities is ok. Or should I be taking log backups more often?

New to the SQL Server world and want to learn all the best practices I can. I am also just trying to ensure we are doing everything we can to safeguard our backups. Thanks.

4

Think of the interval between log backups as the maximum amount of data you can afford to lose.

Realize you need to restore a "FULL" backup, plus all the log backups taken since the full backup in order to get back to the most recent point in time.

Also, practice your restore strategy. This is perhaps the single most important thing you can do vis-a-vis backups. You can have all the backups in the world, but if you don't practice how to restore them, they won't do you any good in the event of disaster.

Regarding your questions:

My issue is my log files seem to be growing very large quickly. I have read that log backups as often as every 5 minutes. I know that shrinking the log files often is not a good solution. I want to make sure that we have full recovery options.

Taking a full backup and a log backup will leave your log in a state where it is pretty close to empty (unless you have something like log shipping, mirroring, always on, etc, that relies on log records). The log will subsequently continue to grow until the next log backup is taken, at which point SQL Server will begin re-using space in the log file. For some interesting reading about log growth, please see this question.

If I take a nightly backup, then take a log backup right after, will that leave a break in consistency if there happens to be transactions going on at that time?

Any transactions that have not been committed at the precise moment a log backup begins will be automatically rolled back during the recovery process. However, if you have a later log backup where those transactions were committed, they will be committed during recovery once that later log backup is restored.

Typically, a sound production level backup strategy consists of the following minimal steps:

  1. Take a full backup once per day, at some low-activity point. For instance say 2:00am.
  2. Take log backups every 15 minutes, retaining every log backup taken since the last nightly full backup.
  3. Restore the full backup, and every intervening log-backup once per day onto a non-production box to ensure the backup files and recovery procedures actually work.

If once-per-day full backups are unworkable, you can instead do:

  1. Once per week, take a full backup.
  2. Once per day, take a differential backup.
  3. Once every 15 minutes take a log backup. Retain every log backup taken since either the last full backup, or the last differential backup, whichever is most recent.
  4. Restore the full backup, and every intervening log-backup once per day onto a non-production box to ensure the backup files and recovery procedures actually work.

You may be interested in looking at Ola Hallengren's backup solution which is very highly regarded in the SQL Server community.

  • Ok so if I can afford to keep a day worth of log transactions. I can take a full backup at night, then take a log backup at night. The log file will 'mostly' empty itself so it can reuse file space and not grow larger. If I have to restore from backup say at noon I can just restore the nightly backup, then replay the current log file on the system before the crash/whatever to get back where I was. I keep nightly backups (and subsequently log backups will be the same) for a month. Does this sound correct? Once I get my plan in place I will be testing. Thanks for your response! – Toolman21 Nov 13 '15 at 20:36
  • What is the maximum amount of data-loss the business is willing to accommodate? 5 minutes? 1 hour? – Max Vernon Nov 13 '15 at 20:38
  • Ideally none, but minimized as much as possible. If I take a full backup of the DB at night and then take a log backup as well, is this not enough to safeguard data loss? – Toolman21 Nov 13 '15 at 21:05
  • you need to take log backups every "x" minutes, and you need to keep each log backup since the last full backup. The log backups contain each and every action that has occurred against the database since the last log backup. – Max Vernon Nov 13 '15 at 21:17
  • So as a general practice it is not a good idea to take a log backup nightly and store that? I would need to take log backups every 5 minutes (as an example) to another location? – Toolman21 Nov 13 '15 at 21:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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