I’ve seen things
Terrible things. I mean, not just working here (which, as I write this, I’m three days away from the two year mark, fingers crossed!), but also out there on the internet.
Once you leave the safe confines of Brent Ozar Unlimited, you’re subject to the ravages of space. Strangers. Radiation. Strangiation. Your bones will scream. It’s bad!
So let’s talk about backup strategies
First and foremost, they need to match your RPO and RTO goals. So, go ahead and get those in writing first. The ‘in writing’ part is important, so no one can turn around and say GOT YOUR NOSE when things get wonky.
Backup schedules are like carefully woven spiderwebs, engineered to be safe, stable, and catch any potential bugs. We all know what happens to spiderwebs when our eight legged friends are exposed to less-than-legal substances.
Let’s play a game
I’m going to list out some of the worst backup schedules I’ve seen, and you get to tell me what you think caused such a lapse of cognizant reality. Please try to limit it to what’s in the medicine cabinet or liquor cabinet, or at least what’s under the kitchen sink.
Oh, forget it. I know you people, and I don’t wanna know what’s in your medicine cabinets.
Assume all the recovery models are Full.
Web 1
- Full backups every four hours, log shrink job every morning that failed
Web 2
- VSS snaps every 15 minutes that froze I/O for 30 seconds
Web 3
- Full backups every night, log backups every 30 minutes that stop between 5pm and 8am
Web 4
- Set database to single user, take a full, diff, and log, set database to multiuser (daily)
Web 5
- Set database to Simple, take a full backup, shrink the log, set database to Full (weekly)
This place has everything
If any of these apply to you, now’s the time to start fixing them. If not, put down the Endust filled paper bag and let me know what you think was on someone’s mind when they designed these.
Thanks for reading!
21 Comments. Leave new
I got this:
The sysadmin boys in Mumbai do scripted backups. Every day a full, dif every hour. Full is kept 7 days. Older get deleted.
Backups are never tested. There is no hardware available to restore the backups to.
The boys in Mumbai have 2 hours to respond to a restore ticket.
They have 21 days to respond if we want to just see yesterdays data.
Na, it’s all good! We just have big customers and BI all taped on our DB. And we just do ETL anyway. Plus all our DBs are set to simple, too.
Good thing, I don’t drink alk. I eat sweets though.
Twenty one days! Wow. You could probably print out and data enter a backup in that time.
Web 1 – I’m thinking that it’s an old job that used to be run against a SIMPLE recovery mode database, and there was some crazy ETL job that needed more space than the database. The author seems more worried about disk space than anything else. But the job fails? Did it target a non-existent file or run under the wrong permissions?
Web 2 – Because VSS was thought to be a way to not have to take database backups? (they’re complicated)
Web 3 – That sounds like someone was worried that the log backups would possibly complicate the full backup job and just didn’t want to have to care whether or not that was true. And/or the system was expected to be dormant “after hours”, so why bother with log backups?
Web 4 – Someone came from a system where it couldn’t handle backups with others in the database (perhaps a file-based one? Access?), and they also didn’t think it important to figure out what the types of backups were, so they took one of each to cover all the bases?
Web 5 – This is v1 of the Web 1 situation, isn’t it?
1 – Backup schedule on vodka: I know it’s failing but I also know that I don’t know why …
2 – Backup schedule on cocaine: I’m stronger than you are!!! (The sys guy to the DBA … who probably doesn’t exist).
3 – Backup schedule on sleeping pills: Nothing happend at night nooooothing.
4 – Backup schedule on ritalin: That dumb thing can’t focus on one single thing
5 – Backup schedule on placebo: Useeeeeeeeeeeeeeee ……… less.
So what kind of music do you listen to?
At the moment, I’m between the disapointment from Jamiroquai last album and the comfort of French RAP classics. What’s yours?
I’ve been listening to a lot of Plastic Bertrand lately 😉
That the most unexpected answer ever hehehe 😀
How can you even know about Plastic Bertrand?
Well … that’s the most unexpected discussion on a SQL Blog … ever.
Something I don’t think gets enough attention is users/applications/random processes taking backups outside of a backup schedule without using copy_only. Full or log backups that are made to random, unknown (to the DBAs) locations and then moved/deleted invalidating subsequent diffs or breaking the log chain.
Also, can’t count the number of times I’ve encountered people wondering why their daily shrink job doesn’t work for the fully logged DBs.
Good point!
I’ve actually run a shop with something resembling Web 3. It’s not such a bad thing even in production IF you have it in writing. They were probably thinking that it’s a relatively small database at a shop with normal business hours running on minimum-spec hardware. If they run their full backups at say 8pm each night and similarly run CHECKDB and perhaps run index maintenance (Ola’s scripts perhaps) as part of that then it makes more sense. When I was in a similar situation, I just made sure I had it in writing that they were aware of the possibility of extended data loss due to their strategy.
Web 2 is what NetApp’s SnapManager for SQL will do.
That can lead to some interesting performance problems.
If you use Veeam to do your database backups, the first and default option is to truncate logs. It does this by doing a log backup to NUL after it does a full backup. This makes any subsequent tail or log backup useless.
I was at a remote site, deploying a new database for a custom application. Day 1, I installed SQL Server and said “You need to work out how to fit this into your backups. Seriously.” The onsite IT guy’s (a loner doing everything from PC support to web design to networking…) said “Nah, this is a VM. I can just snapshot it. That’s all I need.” I tried to explain how that was a REALLY BAD IDEA.™ That night, without any help at all from me (I swear!) the server died and his “snapshots” were useless.
He then proceeded to blame me for the crash. Because Windows + SQL Server + the free version of VMWare all crashing was somehow my fault.
I lost money on that gig. It also taught me to write better service contracts, even for single-weekend jobs. Especially for single-weekend jobs.
Charles – ouch! Sorry to hear about that. Consulting can be pretty rough alright, being dependent on what others are doing at the same time you’re working.
Do a SQL backup to the local drive and let them use whatever method to backup those backup files. Usually this will be a daily backup by some method. If they need better RPO then they can robocopy the transaction log backups as they happen.
This is also handy when you need to restore some data but don’t have access to their backup system.
If everything is on the same drive, then you do need to be careful of space. Ola’s scripts are good for this.
Yeah, Ola is the first thing I install on any SQL Server box that I touch. Followed quickly by the Blitz scripts. They’re definitely part of my standard build for databases. The fact that there are no tools like these at all in the Oracle world is just one reason why I will always love SQL Server over Oracle (I manage both).
Hi Brent..I would like to know to know yo views about using VEEAM For SQL Backups.Is it something you can recommend DBA’s use..Any drawbacks of using it
Spencer – that’s a little beyond what I can answer fast in a comment, but it’s something I’ll think about for a future blog post. Thanks!
Some of our clients use it. It is ok, but you need to be careful which settings you use. The default option to truncate logs does a log backup to device nul. This makes any subsequent log backup completely useless until the next full backup. This will be a nasty surprise if you ever need to do a point it time restore.
Also recently had an issue with the upgrade to 3.0.1. The licence got changed to the free edition and it started to attempt to truncate the logs. Thankfully the system account doesn’t have backup permissions. Once the licence was set back to server edition (required for the application aware settings) it was running normally again.