You’ve got this database that you swear doesn’t need to be backed up, like:
- Read-only databases
- Schema-only databases with no data in ’em
- Archive databases that haven’t changed in years
- Maybe even system databases like master/model/msdb
And you think you’re going to save some time in your maintenance windows by skipping backups and corruption checks on those databases.
Lemme tell you a few stories. Everything’s been abstracted to protect the not-so-innocent.
The Successful Failed Deployment – RetailCo needed a software package to manage their inventory, so they bought one from SoftwareInc. RetailCo set up the server and did the backups, but SoftwareInc’s installer absolutely required SA permissions, so they got it. Over time, one of SoftwareInc’s deployment scripts had a bug: it didn’t change the default database, so they accidentally deployed some of their tables in the master database. The app continued to work perfectly – but real user data was going into master. RetailCo wasn’t backing up the master database, figuring they’d just move the user databases over to another server. You can guess what happened when the server crashed.
The Mostly Read-Only Database – The data warehouse archiving strategy involved one database per year with databases going back over a decade. The older databases were marked read-only, so the DBA figured he didn’t need to back them up often. After a migration to a set of brand-new, way-faster SQL Servers, user complaints started coming in about incorrect query results. Turns out each database had views with tax logic in it, and every time the developers did a release, they’d alter the archive databases to make them writable, change the views, and then alter them back to read-only. No big deal – just now apply the same thing to the archive databases again, right? Well, the project manager was white as a sheet and said, “I’m afraid we need to talk about how we’ve been doing something similar to correct product categories for the accounting team.”
The Truly Read-Only Database – The DBA was absolutely positive no one could write to the archive database because he’d done a great job of locking down permissions. He backed up the monster 10TB archive database once, and then faithfully ran CHECKDB on it as often as he could – not too often, given the size. Years later, the CHECKDB failed – because after all, the SAN can still write to the database regardless, and sometimes, storage gets a mind of its own. The DBA said no problem, I’ll just restore it from backup – and then found out that backups over two years old had been deleted as part of the company’s compliance projects. (Yes, in some private companies, folks actually have an obligation to delete data to stay in compliance.)
The Priceless Schema – A SaaS company came up with a creative version management solution. Customer data lived in one database – call it DataDB – but stored procedures & views lived in another, call it LogicDB. With every release, the app built a brand new LogicDB database with all the new stored procedures, views, etc – like LogicDBv1, LogicDBv2, LogicDBv3, etc. When a SaaS customer migrated to a newer version, the support team just changed the default database for that customer’s SQL login, and presto, it started using the new objects. Seemed brilliant at the time, letting customers live on different version levels while calling the same stored procedure names. The DBA didn’t bother backing up the Logic databases – there had been hundreds built up over time, and they could all be regenerated by source control, right? And the DBA had been using a script to sync logins from production to disaster recovery, but she didn’t think about syncing their default databases, too (and the support team was constantly changing them based on what version of the app they were on.) As a result, when they failed over to DR, it was more D, less R.
Brent’s rule: on a production server,
every database is a production database.
Don’t take the initiative to cut corners around data. Your first obligations are to serve and protect the data. Start there first, not with “time-saving” or “cost-cutting” ideas that don’t really accomplish either.
10 Comments.
So we really are the data police. Cool!
I would add: If you’re doing your own backups, you’re probably doing it wrong.
Great point. I like to say our job isn’t to save space or reduce costs (even though it is important). Our job is to keep things running and provide for recovery. Everything else is secondary.
We have two databases that are not backed up. They are replicated databases. The source databases are backed up…. does that count?
Can you guarantee that people never put objects in there like stored procs or views? Or I guess, more importantly, did you even read the part of the post called “The Mostly Read-Only Database”?
can’t go into details, but i’ve see someone set up a table in a replicated database for some users to work on. along with views being created in a replicated copy but not in the master copy and then replicated.
It’s like there is a right way to do things, but some people do the opposite on purpose just to look like a hero fixing unneeded issues.
No but I can guarantee that if they have put objects in there that they have no guarantee to ever see them again. That’s on their heads! I’m not sure how easy it is to lock down a replicated database so no-one can do that sort of deviant behaviour. I can see that it would be good practice to lock these replicas down so no-one but the replication process can do anything but select out the data. I’m not quite sure I’d know how to go about that though.
Yep, good luck with that. Me? I’d just back them up.
If there is one reality show to watch as a DBA, it’s Hoarders. Watch it, live it, love it.
When in doubt, back it up. Just like Hoarders, never delete. Someone always needs something a year after they tell you to delete it.
Here is a another reason: in a DR situation, you don’t want a complicated, piece-meal recovery plan tailored for each server. You want a simple consistent process to restore the *whole* production environment.