“But I don’t need to back up that database.”

Backup and Recovery

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.

Menu