Warning Signs That You Have a SQL Server Backup Problem

Availability Groups with infrequent log backups: what could go wrong?

Availability Groups with infrequent log backups: what could go wrong?

Your backups seem fine. They weren’t failing, the last time you checked. But trouble may be lurking.

Here’s the top 5 warning signs I’ve seen that backups haven’t been thought through:

5. Your log backups run every 30 minutes. I have yet to find a company with log backups running every 30 minutes who was actually OK with losing 30+ minutes of data. Maybe you are part of the company where it’s actually true, but if you’re not 100% sure, get someone to sign off on it. With an ink pen. Really.

4. There’s a “gap” in the log backup schedule. If you have a window where log backups don’t run, someone probably didn’t understand that log backups can run at the same time a full backup is running. Most SQL Servers these days have users who can log in around the clock, and there’s no time of day when data loss is more acceptable than others.

3. You have a job that changes the recovery model from full to simple. This shouldn’t be automated (but exists a lot of places). Switching to the simple recovery model breaks your log backup chain. This might be happening as part of the backup job, or as part of a maintenance plan or SQL Server Agent job that another DBA or even a vendor set up. A dead giveaway if this is occurring lies in the SQL Server Error log– it records whenever recovery model is changed for a database.

2. You think that having Availability Groups makes infrequent log backups OK. I know why you’d believe this, but it just isn’t true. The primary replica in an Availability Group can run “exposed” and accept write in some cases when secondaries aren’t available– even if you’re using a synchronous AG. AGs do not replace backups. Neither do failover clusters or database mirroring.

1. You’re constantly running out of space for backups. DBAs rarely cause this problem, but they fall prey to it. If you’ve ever said, “We can’t take as many backups as we need to because of space”,  you have a different problem – you’re letting a resource constraint harm the business. Even if you’re not the one who purchases storage, you ARE the one responsible for the data! You need to head back to the questions about impact to the business if you lose data or can’t restore it fast enough, and raise those questions to the right people.

6 Comments. Leave new

  • Very well said. We fight this kinda thinking all the time.

  • I was going to say I could see some edge-case scenarios where half-hour log backups could be appropriate (when using log-shipping to replicate to another server in standby mode for offloading reads, but where actual RPO isn’t important, like in a non-production environment)….but I still couldn’t do a 30 min increment on the backups, that’s too much time for a rapid-moving process or index rebuild to fill up the log, expand the file, and possibly fill a drive.

  • Matthew Holloway
    February 9, 2016 1:39 pm

    If the reason for wanting to have a break in the log shipping schedule is to allow for a full backup to be taken ( Prior DBA did just that because it caused things to be in the logs the auditors were not happy with being there about job concurrency) it is a simple enough matter to off set your transaction logs.
    E.g full backup Job runs at 1am, runs for 7 minutes.
    You want transaction logs every 10 minutes so you start your schedule at 01:08.
    Meets both the auditors requirement and keeps things smooth.
    In the real world I set the transaction logs to start at 1 and moved the backup to 00:55 because that is easier to remember than to work out when the best transaction log to restore to is when you are under the hammer desperately trying to restore to the last good copy of a DB and the alert only shows if they start at the same time, not because they run concurrently.

    My add to your list:
    IF your backup plans blindly mirror an external auditors recommendations in spite of an objection from the person who will actually be accountable.
    Auditors usually care only about ticking boxes not the real world consequences, get sign off in writing if the business wants to deviate from your plan to accommodate an auditor and you object.

  • Matthew Holloway
    February 9, 2016 1:40 pm

    Oh and ADD two:
    If you did not set up and test your backup plans yourself, it is best to assume there is none.

  • Also ensure that nothing bad is happening, like overwriting the same backup file.

  • […] Kendra Little has a few warning signs for backups: […]


Leave a Reply to Matthew Holloway Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.