on February 9, 2016
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.