Some of my earliest memories involve trips to the dentist when I was maybe 3 or 4 years old. To this day, my blood pressure rises whenever I go in for a simple cleaning. There’s a particular poster I can’t seem to get out of my head, like a bad 80’s song. The intent was to encourage us kiddies to floss our teeth. I remember thinking “Well, I don’t need ALL my teeth…” Hmmm, this doesn’t do much to dispel the stereotypes of being from West Virginia, but I digress!
I propose that there is a direct corollary here for the database world –
You Only Need to Backup the Databases You Want to Keep
You generally won’t get any arguments from the IT world about this. Where the confusion sets in is around the concept of what a ‘production’ database is. What happens in a lot of shops is that only the production databases have regular backup and maintenance plans.
What Does ‘Production’ Mean?
A system is usually considered ‘production’ if it’s used to support the business. This usually rules out databases used for testing and development. From yourdictionary.com –
A computer system used to process an organization’s daily work. It implies a real-time operation and the most mission critical computer system in the enterprise.
Contrast with a system used only for development and testing or for ad hoc inquiries and analysis. See data warehouse.
I’ve bolded the most important terms. However, for some ‘customers’, the data warehouse and development instances might be considered ‘production.’ For example, if the data warehouse instance goes down, and the analysts can’t make forecasts or the supply chain folks can’t run their models, then that’s a production data warehouse. There aren’t any real-time operations going on, but it’s sure as heck mission critical!
Business, Professional, & Personal Pains
A few years ago, Quest paid to send me to sales training. The course was based on the Sandler Method. One of the techniques we were taught was to use the ‘pain funnel’ to determine the ‘level’ of pain being experienced by the prospect. If you could validate the problem affected them personally and professionally, then you would have a good shot of selling them your wares, as they would be properly motivated.
If a database goes down, and my business, my career, or I suffer as a result, I consider that ‘production.’
My Production Instances
I have as many 3 instances of Oracle running on my laptop via VMWare. These images are quite large, and would take me a LONG time to rebuild. They are also quite volatile in nature, based on the demos and labs I run on them. If I were to lose 1 or all 3 of them, my life would not be over, but it would surely suck for 3 or 4 days. I would personally suffer. I would professionally suffer. And my company would suffer as well (yes, I know, my ego IS quite healthy.)
Most people would not consider these databases as ‘production’, but I sure do! That’s why I have them backed up to an external disk. If they become corrupted, I’m only an hour or so way from being up and running again.
Do I backup these instances every day? No…and unfortunately I can’t claim to floss my teeth as often as I should. But I have been able to get away with it. For now.
Don’t Hide from the Dentist or DBA
The dentist doesn’t come around to your house and demand to see your teeth. Neither does the DBA with your databases. If you’ve spun up an instance of Oracle or SQL for a short-term project, and you want to be protected, then you need to consult with your production DBA team. It’s not fair to circumvent them when you build the database and then expect them to bail you out when your system crashes.
7 Comments
My 2 cents as a dba :
PROD : online backup
TEST : weekly cold backup in noarchivelog OR weekly clone from production
Unless specified differently by the project
I like that, unless specified otherwise, Test gets backed up once a week. Have you ever been told NOT to back something up, and then later be asked to help recover lost data?
Pingback: ORA-03113: end-of-file on communication channel
Couple of thoughts
Firstly, dev and test databases, by their nature, are more likely to corrupted than prod. They are running software that hasn’t been fully tested. They are also likely to run on a less resilient hardware platform.
Secondly, demo databases (and, in some cases, test) can be much less volatile. You may need a backup once a week or once a month.
These databases also have a lower uptime requirement and often don’t require point in time recovery. Cold backups, or exports, might be sufficient.
Finally, the bit about not needing all your teeth reminds me of a joke. Mum catches teenage son engaged in a certain activity and tells him that if he carries on, he’ll go blind. After a few moments thought he says, “Maybe I carry on just until I need glasses”.
I approve of your joke 🙂
And how many times I have been told that the database doesn’t need a backup because the data can be recreated from it’s source. Oh yeah?! And what are you going to put the source data into because you had no backup (of structure) sigh. Hear ‘it doesn’t need to be backed up’ as I am not prepared to think about or pay for backup strategy but I expect the DBA to bs able to rebuild it when I break it. Garrrr
What goes unsaid is the amount of time it takes even to stage an empty database, dump all the data back in, rebuild indexes, stats, jobs, privs…especially when that data is spread out to multiple sources. You need a recovery SLA that documents the expectations so you can tailor the backup schemes.
On the flip-side, do you back up a DW that’s terabytes large and can be re-built from existing production instance?
Not prepared to pay for backup strategy…like buying a luxury automobile, but not willing to buy premium petrol or regular service visits!