Select Page

Making the Case for Native SQL Backups

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.

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.

ORA-12154: TNS:could not resolve the connect identifier specified

Most people will encounter this error when their application tries to connect to an Oracle database service, but it can also be raised by one database instance trying to connect to another database service via a database link.

Jeremiah Wilton | March 4, 2009

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

12c Upgrade Bug with SQL Tuning Advisor

Learn the steps to take on your Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Work with Us

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


Work for Us

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