Data is the lifeblood of businesses, and protecting it is incredibly important. One of the ways DBAs help ensure the continuous availability of data is through database backups, a process involving copying the data and schema from a database and saving them elsewhere for retrieval later. But like any other process in IT, database backups have costs, benefits, and risks associated with them.
Why Is It Important to Back Up Databases?
The short answer to this question is “data recovery.” Without a database backup, it’s impossible to recover data. If something goes wrong and a business can’t recover its data, it can be disastrous. And as dangerous as not being able to recover is, it’s not an uncommon problem—scroll through enough forums, and you’re bound to find people all over the internet asking where their data has gone and how they can get it back. Without a recent data backup, the answer is “you can’t.”
Data security is another reason why having a database backup strategy is so important. If a company suffers a ransomware attack, for example, it’s important to draw a hard line to separate important data before and after the attack. Recovery offers a clean slate moving forward—through recovery, it’s possible to go back to before the attack ever happened. If a ransomware attack encrypts an organization’s data and there’s a skull-and-crossbones on the screen, recovering from backups to brand-new systems offers a way to circumvent the attack. However, without a backup, organizations can’t recover—and if they can’t recover, they can’t keep the business running.
RTO and RPO
Of course, generating database backups isn’t as easy as snapping your fingers, and different businesses have different backup and restore requirements. Here are two important business objectives to know about:
- Recovery time objectives (RTOs): RTO refers to the amount of time in which the business must be able to recover its data.
- Recovery point objectives (RPOs): RPO refers to the point in time to which they must be able to recover.
For example, the business might need DBAs to recover data to a backup made within the last day (the RPO) and may need it to be done within an hour of a disaster (the RTO). RPO is the point they need to go back to, and RTO describes how long it should take to do so.
The business sets these requirements, and DBAs have to make it work.
But one problem businesses often face is losing track of the expanding data in their databases. If DBAs don’t constantly test the restores, they might find themselves unable to recover the databases on time when disaster strikes, as the accumulated data can cause restores to take longer. For the business to be able to set reasonable RPOs and RTOs—and for DBAs to be able to meet them—DBAs must periodically restore databases. Otherwise, they risk missing the business’s RTO and RPO objectives and suffering critical data loss and disruptions to business operations.
How Does Cost Factor In?
Cost can mean different things for different people when it comes to database backups.
Say, for example, the DBA in charge of a system learns they didn’t have any backups running for the first three weeks of the year. If something goes wrong, the DBA might have to go to the business and explain why they need to re-enter three weeks’ worth of transactions—they can only go back to December 31, after all. Businesses have to consider the cost of re-entering potentially massive amounts of data if DBAs don’t have backup copies ready when things go wrong.
This same incident has a cost for the DBA, too. If it’s their responsibility to take frequent database backups, failing to do so could have professional consequences. Whether dealing with an angry manager or losing their job, the personal cost of not having a database backup process is steep.
Another cost to consider is the cost of the data storage, the medium the business uses for database backups. Whether the business is paying for offsite or cloud storage, each has its own associated cost. On top of this, organizations must consider the cost associated with their RTO objectives—if an important part of the business is down for an hour, how much does it cost the business? How many sales will they lose out on during this time?
It’s a delicate balancing act. Paying more for better storage and taking more frequent database backups can help ensure the organization doesn’t lose as much business while it recovers, but is it worth the initial cost? It’s something all businesses should consider, especially as the amount of data they have grows. Thankfully, there are some practices organizations can employ to cost-effectively manage database backups.
Restoring Your Databases From Backup
One important thing DBAs can do is restore databases periodically to make sure they can do it when it matters. But what should a DBA do when they’re responsible for thousands of databases? It’s not feasible—or cost-effective—to restore every database all the time.
Think about it this way. I have a friend who sets the price for king crab when the boats bring it into port. To set the price, he has to sample the crab—but by sampling the crab, he ruins it, and he can’t sample every single crab. My friend uses a formula to calculate how many crabs to take from the catch to get the most accurate price while wasting the least amount of crab.
It’s the same way with database backups. Since it’s impossible to restore them all, DBAs must figure out how many databases to restore periodically to minimize cost and maximize the chance they’ll be able to restore all databases in case of disaster. With statistical sampling, restoring a small number of randomly selected databases—perhaps only a few dozen—on any given day can allow DBAs to be 95% confident all the backups can be restored.
Now, there’s a cost associated with this—it takes time to figure out this process and periodically test those random restores. But what would the cost to the business be if the DBA doesn’t build out this process? If things go wrong without working backups, the cost to the business can be enormous. DBAs are often rightly paranoid—for them, the worst thing that could happen is they lose all the business’s data and can’t do anything about it because they don’t have a backup.
Common vs. Best Database Backup Practices
Some common database backup practices aren’t best practices—sometimes, people take shortcuts, and other times, they don’t know the steps they should be taking in the first place. Though they’re not always commonplace, here are a few best practices organizations can employ:
One best practice DBAs can employ is encrypting or password-protecting their database backup files. But this isn’t something DBAs commonly do—I’d be willing to bet most IT pros taking backups are just doing native backups.
Still, encrypting backup data is extremely important—if someone gets access to a backup, they can take all the data and restore it to another system. At a minimum, password-protecting the file helps keep data more secure. Of course, encrypting backup files has its own associated cost. The business must decide if paying this cost now is worth avoiding the headache of a stolen backup later on.
Another best practice to keep in mind is ensuring backups don’t interfere with other workloads. In theory, backups shouldn’t impact any other operations happening inside the engine. But backups are also being written to a file—and if this file is in storage with other shared systems, all this write activity can be a major bottleneck.
Avoiding Bandwidth Throttling
Backups can also take up network bandwidth. If DBAs run their backups at the same time every day, it can slow things down for everyone else. Staggering backups instead—backing up some servers at 1 a.m., others at 2 a.m., and so on—can help DBAs avoid throttling the network and causing issues for other systems.
Understanding the Benefits of Backup vs. Costs and Risks
Like everything else in IT, there will always be costs, benefits, and risks associated with database backups. Taking backups and restoring them periodically comes with monetary costs, and failing to take backups at all can have monetary and reputational impacts on the business. If things go wrong, DBAs don’t want to be caught without a backup in their back pocket and risk having lost data—understanding the importance of data backup solutions is often a careful balancing act of costs and risks every business has to consider.