PASS Pro Now Available

Welcome to the next evolution of PASS. Unlock exclusive training, discounts, and networking opportunities designed to accelerate your data career. Learn More >

The official PASS Blog is where you’ll find the latest blog posts from PASS community members and the PASS Board. Contributors share their thoughts and discuss a wide variety of topics spanning PASS and the data community.

Back to the Basics: Backups

Every time I discuss this topic, I am convinced it will be the last. It’s never the last, and it’s doubtful that it ever will be. How can such a simple and critical component of managing data be so frequently disregarded? How can the harsh experiences of data loss experienced by so many get ignored? I’m taking a deep breath to talk through this today, and hopefully when done, we can agree on the importance of database backups. This is not a very technical article, but instead a sales pitch to “know thine data” and be certain that it is safe.

For many organizations, backups tend to be a hassle-task; relegated to the bottom of the pile to be dealt with by whoever has a few minutes to spare to throw something together. For others, backups are deemed unnecessary due to other high availability or disaster recovery features in their software/hardware stacks. These assumptions are often made when time and money are limited resources, and developing new features is seen as more profitable than adding infrastructure and using up precious developer time. To begin to counter these (and other) common misconceptions, it is best to start out with the reasons that backups are important.

 

Why are Database Backups Necessary?

There are some obvious reasons, such as:

  • Disaster Recovery: If your data center, server, cloud storage, or local storage are somehow corrupted, destroyed, or made unavailable, backups are critical to recovering from the disaster. Disasters include security breaches and ransomware, too!
  • High Availability: Some organizations use transaction log backups for high availability purposes, allowing them to recover within an acceptable recovery point objective.

These are the first things we think of and are the #1 reasons why backups matter. In the event that your data vanishes, having a reliable copy somewhere is critical.

That’s not all, folks! There are many more reasons to maintain database backups:

  • Point-in-time Recovery: Transaction log backups allow for recovery to any point in time. This provides great flexibility if you need to restore to a very specific time for any reason.
  • Data Forensics: Need to investigate what happened at 8:42:17.539 yesterday? Transaction log files can be read and reviewed to determine exactly what happened, when it happened, and why. Apps exist to make this much easier as well, if digging into backups isn’t your cup of tea.
  • Dev/QA/Testing: Development teams often need backups of databases for testing purposes. If you already have a solid backup process, then Development can take advantage of the backup files that already exist as needed.
  • Compliance/Contractual Obligations: Many organizations are subject to rules that specify if backups are required and the details for how long data must be retained. If you’re not sure about whether this affects you or not, then ask!
  • Corruption or Lost Data (aka: Oopsies): While rare, corruption can occur in a database. More frequent are mistakes, such as accidentally deleting or altering data. Regardless of the cause, incidents like these require the ability to restore data from a previous point in time. Transaction log backups make this easy and allow us to be precise when we choose the restore point.
  • Not Wanting Simple Recovery Model: If your databases are using the full or bulk logged recovery model, then you need to issue full or transaction log backups regularly to control transaction log size. Without these backups, your transaction log will grow indefinitely, filling up your storage as it does so.

This is a long list and it’s challenging for most organizations to meet all of these demands without thinking through a backup plan carefully first. Other technologies, such as container/VM backups, hardware snapshots, reliance on cloud infrastructure, and physical file backups provide some level of security, but often fail for a number of reasons.

  • Point-in-time restore capabilities are limited to whenever snapshots are taken and cannot be any time.
  • Cloud hardware may or may not provide backups out-of-the-box. If they do, you’ll want to configure them to ensure you’re getting the level of service needed. Also, where are your backups going? If they are ending up on the same server or even in the same data center, then your backup plan still has a single point of failure.
  • Backing up physical database files is not the same as performing database backups and you will still be restricted to restore to whatever point in time the files were copied.

 

Considerations

OK, you have backups. You’re golden, right? Not exactly. There are a lot of reasons why having backups alone isn’t good enough. When determining if a backup plan is adequate, here are some additional questions to ask:

RPO: Recovery Point Objective

In the event of a disaster, how much data loss is acceptable? Often, our initial response is to claim that no data loss should occur, but to ensure zero data loss requires some big money, time, and compromises elsewhere in infrastructure to do so. More realistically, data loss of this magnitude is rare, and most organizations are OK accepting a small amount of data loss, so long as it is well-defined.

Translated into backups, your RPO is going to be approximately equal to the frequency of backups plus whatever time it takes to write and copy those files to a safe place. If you run backups every 15 minutes and it takes 10 seconds to write the file, then your RPO will be 15:10. If a disaster occurs, you will lose at most about 15mins of data, assuming the disaster occurred right before backups were about to run.

RTO: Recovery Time Objective

This is defined as the amount of time it takes to recover from a disaster. This will be determined by how long it takes to fix whatever is broken or to stand up new hardware/software to replace it.

With regard to backups, the speed and availability of backup hardware will determine how quickly data can be restored, if needed. Also, testing recovery is equally as important as running backups. Testing a disaster recovery plan on some regular interval is an excellent way to ensure that things really work the way they should. In addition, testing will speed up your response time and allow operators to be familiar with the steps needed to mitigate a disaster.

Back Up System Databases

If you will need to restore a SQL Server to the state it was in before a disaster, then system database backups are important! Be sure to back up: Model, MSDB, Master, and ReportingServices (if the server is an SSRS server). These databases contain metadata about your server configuration, databases, SQL Server Agent jobs, and more. They are important and a server without that metadata will be incomplete.

How Long do Backups Take to Run?

Ensure that backups run fast enough so that the current backup process does not stretch into the next one. For example, if your backups run every 15 minutes, but it takes 20 minutes for the backup task to complete, then your RPO will not be 15 minutes anymore. Monitoring SQL Server for slow or missed jobs is a good way to catch this, as is a periodic review of backup tasks, regardless of whether they use SQL Server Agent or some other process to execute.

Do Not Break the Backup Chain!

A backup chain is comprised of the full backup for a database and all subsequent backups on that database that occur until the next full backup. If an operator issues a backup outside of the standard backup process and does not use COPY_ONLY as an option, then that ad-hoc backup will become part of the backup chain. If the ad-hoc backup is a FULL backup, then it will break the current backup chain and create a new one.

When a restore is needed, if those ad-hoc backup files are not available, then recovery to the desired point-in-time may either be incomplete or impossible. When running backups manually and outside of the standard backup process, be sure to use the COPY_ONLY option unless you are certain that creating a new backup in the chain is what you want.

Verifying Backups

The statement RESTORE VERIFYONLY can be used to verify a database backup, but not actually restore it. This allows you to ensure that a backup is error-free and will restore correctly if ever needed. The downside of this operation is that it is time-consuming. For an important database where backup quality is very important, using this restore option makes sense.

Note that backup verification can be run on any server and not only on the server where the database resides. This allows the server load incurred by this operation to be pushed to a less critical location, or to one with more compute available.

Disaster Recovery Testing

Backups are useless if they do not work. Have a disaster recovery plan, even if it is very simple. Test it periodically and ensure that all of the various parts needed to make it work are functional. Once this becomes a standard process, it will be quick and painless. As an added bonus, you’ll be able to sleep better knowing that your disaster recovery plan works!

Depending on your database environments, testing will include some or all of: restoring and validating system databases, and restoring and validating user databases.

Compress Backups

There is no downside here. Native backup compression can shrink backup files to a fraction of their standard uncompressed size. Make this the default server setting for backups as well.

Compressed backups write faster and use less compute, making it a win-win.

 

Now What?

This is a bigger topic than is often discussed. Backups are an integral part of security, compliance, disaster recovery, testing, and more! If you’re already doing it all, then keep doing what you are doing and watch out for change. Many of the things that backups help us with are readily changing, and as laws, obligations, and business needs change, so may the backup strategies that you implement.

If some of the items above sound new, then take the time to do some homework. If the result is securing important data, then expect only good things to come from that! Thanks for reading, and feel free to share your disaster stories, as they are always some of the most interesting, funny, or downright scary things we talk about!

Edward Pollack
About the author

Ed Pollack has over 20 years of experience in database and systems administration, which has developed his passion for performance optimization, database design, and making things go faster. He has spoken at many SQLSaturdays, 24 Hours of PASS, and PASS Summit. This led him to organize SQLSaturday Albany, which has become an annual event for New York’s Capital Region. Sharing these experiences with the community is a top priority, and encouraging everyone to take the leap into public speaking and engaging others, a passion.

2 comments on article "Back to the Basics: Backups"

5/13/2020 10:42 AM
Larry Watson

Very good, high level, article! You covered just about everything I would expect DBAs on my team to know. However, before I recommend they read your article I have a couple minor things to address.

1. Compress Backups

There can be a downside to Database Compression. If your backup storage device utilizes Data Deduplication, compressing your backups will kill your deduplication/compression ratio, reducing its overall capacity. At least that's what I have observed in our environment working with "Data Domain" storage.

2. Not Wanting Simple Recovery Model:

This might seem a little nit-picky. Technically, there's nothing incorrect with your statement. However, the thought of certain individuals in our organization reading your article makes me cringe. As they will point to it and claim it validates that others agree with their opinion that all databases should operate in in FULL RECOVERY. Maybe the next time you write this topic you could change the heading to: Controlling Transaction Log Size.

LL Sr DBA



5/27/2020 11:57 AM
Edward Pollack

Hi Larry,

Thanks for reading! As always, there are few one-size-fits all approaches and unique use-cases may call for unique configurations.

Compression is only needed once. If you already have compression or deduplication elsewhere, then it isn't needed here. This varies so wildly across hardware and software environments that it was out of scope for this article. It is worth noting, though, that the performance of compressed data and deduplicated data will vary, and therefore testing may be needed in scenarios where performance is #1.

The comment on simple-recovery model solely addresses log growth and the need to take backups if full recovery mode is used. If not, then no problem : ) In no way does this imply that simple recovery mode should not be used, but that it is required for these specific needs.

Thanks again for reading!

-Ed


Please login or register to post comments.

Theme picker

Back to Top
cage-aids
cage-aids
cage-aids
cage-aids