Are Your SQL Database Backups Enough To Protect Business?

SQL Backups

Table of Contents

Are Your SQL Server Database Backups Doing Enough To Protect Your Business?

If you’ve been listening to the news lately, you must know how important it is to protect your data. Just a few weeks ago, a local hospital system fell victim to a cyberattack, making medical records inaccessible and compromising patient care. In the same timeframe, a school system had to temporarily shut down because of a data breach. And Google — yes, you read that right — accidentally deleted a customer’s account from the cloud. 

You might be thinking, “Well, I back up my data, so my business is secure.” But are those database backups scheduled correctly? Are you using the right ones? Have they been tested? And how much data would you lose in the event of an error or cyberattack? 

If you don’t know for sure, read on to learn about best practices in SQL Server database backups.

Why Back Up Your Data?

No matter what your business is, it involves some type of data. You could be dealing with thousands of customers’ information or a couple of hundred records, but either way, you’re responsible for that data, and it can easily become compromised, either through an employee error (accidentally deleting data or running a process in production rather than test mode), ransomware being installed by cyber criminals or a power outage causing a glitch between your storage and database layers. 

But it’s not enough to just “run a backup.” You need to understand the different types of backups and how to schedule them so you can adequately protect your data. 

Types of SQL Server Database Backups

Sql Server Database Backup Types

There are three types of SQL Server database backups: full backups, differential backups and transaction log backups. A full backup is just what it sounds like — a full copy of all your data stored on a server. A differential backup records anything that’s changed since the last full backup. A transaction log keeps track of operations, or transactions, on the database. This includes table creations, indexing, deletions, data updates and more. The transaction log backup, therefore, copies the logs of records specifying all transactions and when they were performed.

Full backups are absolutely necessary, but without the other backups, they’re not as useful in recovering data after an error, glitch or cyberattack. Let’s say your company runs scheduled full database backups weekly (which is typically recommended) at midnight on Sundays. Then, on Tuesday morning, there’s a power outage that affects your database. You have that database backup, but any new data, tables, indexes or other changes made on Monday and Tuesday before the outage are not recoverable.

If you run differential backups, however, once a day (also a typical recommended schedule), you can restore the database using the full backup and then, using the differential backup, restore any changes from midnight Sunday through at least Monday night (depending on when you run the differential backup). You can essentially repeat all the data additions, deletions and other changes to bring your database back to where it was at the end of the day Monday. 

Many companies use what’s called a simple recovery model, which employs only full and differential backups. Under the simple recovery model, transactions are kept in the transaction log only long enough to ensure data consistency and allow for a rollback if the transaction is interrupted or cancelled.  But there’s a third piece to the recovery model that can help recover data — the transaction log backup, which is enabled by using the full recovery model.

What about all the changes made Tuesday morning just before the outage? Well, your transaction logs backup can help with that. We typically recommend transaction log backups run at intervals throughout the day, depending on the company’s needs and how much data it can afford to lose. If your power outage happened at 11 a.m., and your log backs up every two hours, you can use it to re-perform operations that happened after the differential backup and before the outage. This turns what might have been 11 hours’ worth of lost data into one or two. 

The full recovery model is great, but it requires more maintenance. When you enable the full recovery model, the database saves all the transactions in the transaction log until you back it up, which frees up that space for re-use.  If it’s not backed up frequently, the database transaction log can grow very large and fill up your available disk space. Help sites like StackOverflow.com, Reddit.com, and SQLServerCentral.com are filled with posts from folks who enabled the full recovery model but never enabled transaction log backups and have an unusable database as a result.

Database Backup Frequency

You might be thinking, “Well, I’ll just run transaction log backups every minute, differential backups every hour and full backups every day.” That’s possible, but not advisable. Keep in mind that these backups do require resources — memory and space on your server, which ultimately translate to money. 

Consider how much data you can afford to lose. Imagine a power glitch, error or cyberattack. In those situations, could you afford to lose an hour’s worth of data? Half a day’s worth? Those losses, too, translate to money lost, but there’s a tradeoff because data storage and memory also cost money. Talk with your database administrator about what would happen in the event of data loss, and work out a schedule accordingly.

Database Backup Storage

If you’re doing all of these backups and have the right routine set up for your business, that’s great. But if you’re storing those backups on the same server where you’re working, you could easily lose them. For example, if a hardware failure corrupts your data, and your backups are on the same server, then your backups are also likely corrupted. But if you store those backups on a different server, you can access them even if your database server is wiped out.

Another important consideration is the creation of air gaps. An air gap in a fire is a gap between the fire and surrounding areas it may damage. In database administration, it’s a gap between your production data and your backup storage. Air-gapped backups help insulate you from ransomware, the same way an air gap in a fire keeps it from spreading. Learn more about air gaps.

Testing

If you’re now convinced (or were already) that you need a full recovery database backup model, that’s great! You’re taking the right steps to protect your data. But now it’s time to do some testing.

Just as a fire or disaster drill simulates what employees would do in the event of a fire, a data recovery drill simulates what would happen in the event of data corruption or loss. Schedule regular times to test your database backups — and your employees — on the process. Do they know what to do? How quickly can they bring the databases back up? Are there some glitches? Is there room for improvement? You know what to do — make changes and test again. 

Your Partner in Data Protection 

Questions? Contact SQL Tailor Consulting today at (248) 919-8086 for a free consultation about your database backup process.

Facebook
Twitter
LinkedIn
Email
Picture of Joe Fleming (Farmington, MI)
Joe Fleming (Farmington, MI)

Your SQL Server Lifeline: Immediate Fixes, Ongoing Optimization | Save Costs & Boost Efficiency | Custom Packages | Veteran Expertise

FREE CONSULTATION
Let SQL Tailor craft the perfect solution for your business.
Subscribe to Stay in Touch