Select Page

Making the Case for Native SQL Backups

Author: Andy McDermid | | December 13, 2016

It’s 8AM…

I’ve configured alerts for missing SQL Server database backups. When I review my emails first thing in the morning I can relax knowing each database has been backed-up the night before. How do I know? Because I didn’t get a missed-backup alert email! Ok, fair enough, but is that good enough?

What’s really going on in the wee maintenance-window hours of the morning so that my inbox is clear of missed backup alerts? Who or what is taking those SQL Server database backups? How, exactly, are they being taken? Where are they going? And why?

Three flavors

Let’s begin by zooming way out and looking at three flavors of database backup solutions:

  1. Native SQL Server Database Backups – part of the SQL Server product, initiated via a TSQL BACKUP statement. You have a lot of options here. Backups can go to a local drive, or to a remote share, or both, backups can be encrypted, striped, compressed, copy-only, scheduled via SQL Agent, and so on…
  2. Third-party SQL Server Database Backups – any of a number of services or utilities with a ‘hook’ into SQL Server that facilitates scheduling database backups to remote centralized storage. A few common tools include NetBackup, Red-Gate, Idera, Veritas, etc. (for more google SQL backup tools).
  3. OS-level File Snapshots – any number of services, applications, or tools that grab an image of the entire system and/or the files on a system and store them on remote storage. A handful of examples include Windows or Azure Server Backup, Avamar, Cabonite, etc. (for more google OS backup tools).

 
Each of these different solutions have their strengths and each of these could be viable for backing up SQL Server data. But what about when we need to recover the data? That’s when things get complicated.

Alphabet Soup

We backup data in order to be able to recover it for these reasons:

  • to refer to historical data – let’s call it BCE,
  • to rebuild systems after infrastructure failures – DR of course,
  • and to repair an unintended data modification – DOH!

 
A data restore plan is quantified by its Recovery Point Objective (RPO). RPO is the gap between the most up-to-date data recovered and the time of an incident, and its Recovery Time Objective (RTO) – the time it takes to restore the data. Practically speaking, RTO and RPO mostly apply to DOH situations. For example, it’s not typical that the RTO requirement is difficult for BCE restores – the restore can be planned out well ahead of time. Similarly, in a DR situation (or, thankfully, and more familiar to many of us, a test-DR situation) the RPO requirement is not critical. Oftentimes it is one day. On the other hand, when the call comes in to fix a DOH – for instance a table is accidently dropped or corruption is discovered – minimal RTO and RPO are key.

Take this line of thinking back to the backup flavors.

The case for Native SQL Backups

The way to minimize outages and loss for DOH situations is to keep the backups as near to the data as possible. The closer the backups are to the data, the simpler the restore plan, and the lower the RPO and RTO. That usually means native SQL backups to a local drive. (They should also be copied to a remote location but that is beside the point).

Third party SQL Server database backups make ease of management the priority. They may be fine for DR and BCE, but if it’s complicated or slow to recover data, RTO lags. OS level file backups are probably critical to the OS team but due to relatively high RPO and RTO values they are not ideal for BCE, DOH, or DR for SQL databases.

Getting back to the original questions, all of these options do what they’re supposed to do, namely backup the databases and keep alerts out of the inbox. But do they meet the business requirements for RPO and RTO?

I know there are a lot of generalizations here – each environment is different and any combination of these flavors might make good sense. But the take away is summed up by the old DBA truism: database backups are only as good as database restores (or something like that). No matter the solution, good database restores require good database restore plans. If I know those plans are thought-out, tested, and in place then I know the lack of missing backup alerts in my inbox really does indicate all is well and I can enjoy my morning coffee.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.

Vijay Muthu | February 4, 2021

Scripting Out SQL Server Logins, Server Role Assignments, and Server Permissions

This blog reviews how you can generate scripts for SQL server logins, role assignments, and server permissions for a smooth migration.

JP Chen | October 1, 2015

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS