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:
- Take a full backup once per day, at some low-activity point. For instance say 2:00am.
- Take log backups every 15 minutes, retaining every log backup taken since the last nightly full backup.
- 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:
- Once per week, take a full backup.
- Once per day, take a differential backup.
- 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.
- 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.
