SQL Server Backup Best Practices

00:00
  -
  -
  -
  -
00:15
  -
  -
  -
  -
00:30
  -
  -
  -
  -
00:45
  -
  -
  -
  -
01:00
  -
  -
  -
  -
01:15
  -
  -
  -
  -
01:30
  -
  -
  -
  -
01:45
  -
  -
  -
  -
02:00
  -
  -
  -
  -
02:15
  -
  -
  -
  -
02:30
  -
  -
  -
  -
02:45
  -
  -
  -
  -
03:00
  -
  -
  -
  -
03:15
  -
  -
  -
  -
03:30
  -
  -
  -
  -
03:45
  -
  -
  -
  -
04:00
  -
  -
  -
  -
04:15
  -
  -
  -
  -
04:30
  -
  -
  -
  -
04:45
  -
  -
  -
  -
05:00
  -
  -
  -
  -
05:15
  -
  -
  -
  -
05:30
  -
  -
  -
  -
05:45
  -
  -
  -
  -
06:00
  -
  -
  -
  -
06:15
  -
  -
  -
  -
06:30
  -
  -
  -
  -
06:45
  -
  -
  -
  -
07:00
  -
  -
  -
  -
07:15
  -
  -
  -
  -
07:30
  -
  -
  -
  -
07:45
  -
  -
  -
  -
08:00
  -
  -
  -
  -
08:15
  -
  -
  -
  -
08:30
  -
  -
  -
  -
08:45
  -
  -
  -
  -
09:00
  -
  -
  -
  -
09:15
  -
  -
  -
  -
09:30
  -
  -
  -
  -
09:45
  -
  -
  -
  -
10:00
  -
  -
  -
  -
10:15
  -
  -
  -
  -
10:30
  -
  -
  -
  -
10:45
  -
  -
  -
  -
11:00
  -
  -
  -
  -
11:15
  -
  -
  -
  -
11:30
  -
  -
  -
  -
11:45
  -
  -
  -
  -
12:00
  -
  -
  -
  -
12:14

If you’ve been in business or IT for a while, you’ve probably run into a nightmare scenario where the pain of a server crash or hardware failure is compounded by the fact that, backups of the crashed server strangely just stopped working a few days or a few weeks before the crash occurred.

In scenarios like this, there are two universal truths: One, that scenarios like this can be avoided. And, two, because of the previous truth, business users can’t help but look at IT personnel without thinking that they’re complete idiots.

Therefore, in this SQL Server Video, we’ll take a high-level look at some of the best practices you can put in place to help prevent you from looking like an idiot – at least in terms of disaster recovery.

Of course, while this video tries to be as comprehensive as possible (without being tedious), there’s no way that it can cover all of the best practices out there. That, and the reality is that every environment is different - so there’s on ‘one size fits all’ approach that you can take when it comes to disaster recovery.

Therefore, in this video, we’ll start by quickly looking at some of the basic concepts involved in creating disaster recovery plans. Then, we’ll transition to a review of some of the more common pitfalls, or mistakes that people make, when it comes to creating disaster recovery plans.
And, finally, we’ll turn our attention to some best practices that can help ensure quicker recoveries and decreased data loss.

At the most basic level, creating disaster recovery plans is all about minimizing the amount of loss that can happen within a computing environment.

In order to understand why minimizing that loss is so important, it’s helpful to remember what helps contribute to the total COST of the loss when problems do occur. First, there’s the cost of time spent recovering you system or database back to a workable state. Second, is the cost of recovering any data that previous recovery efforts weren’t able to address.

In other words, if you recovered everything but the last 15 minutes of updates and changes to your database right before it went down, the total cost of recovery would be the AMOUNT of time your system was DOWN while recovering the database from the crash, plus any additional time, energy, or effort, required to re-add any lost data.

Therefore, while it would be ideal to create disaster recovery plans that don’t allow for ANY loss, the reality is that creating those kinds of solutions can become extremely expensive when considering the cost to create fully redundant, fault-tolerant, systems that can withstand nuclear detonations.

On the other hand, there’s actually a lot of protection that you can put in place – without incurring massive expense, or cost. Accordingly, the trick with creating disaster recovery plans is to juggle implementation costs against protection needs to craft a plan that meets your own needs and constraints.

And with that key consideration in mind, let’s take a look at some of the absolute ‘basics’ when it comes to creating disaster recovery plans.

First in our list of basics is the role of planning – as without it, you’ll never be able to create a viable disaster recovery plan.

Accordingly, the key thing to do when planning is determine acceptable loss – such as the amount of data and down-time that your organization can tolerate under disaster conditions.
Typically, this window won’t be very wide, but most organizations also don’t have hundreds of thousands of dollars to throw at completely redundant systems either.

As such, the goal of planning is to establish very specific, and concise, tolerances that clearly match business needs and budget constraints.

In most cases, determination of these tolerances needs to be made in conjunction with business users and executive officers.

Likewise, in many environments you’ll also want to include the legal department in determining these needs as there may be liability or compliance issues that you need to address as well. And that’s especially true in terms of establishing retention periods for whatever backups you determine to implement.


Likewise, a key component of planning is determining what kinds of scenarios you need to protect against. Obviously, hardware and software failures are a core consideration, but does your organization consider it equally ‘disastrous’ if ‘Stan the Man’ tacos your product catalog by running an UPDATE query without a WHERE clause?
If so, what kinds of contingencies will you have in place to account for such a disaster?

Then, once you’ve been able to determine needs and establish parameters for acceptable degrees of loss, you can go ahead and start to establish how frequently backups need to take place, as well as what kinds of backups to use.

It’s also at this point that you’ll be able to start formulating plans about how long to retain backups, as well as address considerations about whether or not you will need to implement any kind of off-site backups, or copies of your backups to help protect against disasters.

With plans in place, you’re ready for the next step – documentation. Though, don’t think of creating a disaster recovery plan as a rigid process of stepping from one step to the next as disaster recovery plans actually need to be very flexible and adaptive – given how core, or basic, considerations all hinge upon one another.

The purpose, however, of documentation, is two-fold. First, it helps ensure that business needs and planning considerations have all been correctly communicated and captured. Second, it can help provide a bit of structure, and calm, when responding to an actual emergency, or disaster, by providing a road-map.

Moreover, if the documentation has been implemented correctly, it will also account for contingencies that may be encountered when attempting to respond to an emergency. As such, it’s hard to overstress the importance of a well-documented disaster recovery plan.

In terms of what a disaster recovery document contains, the most important thing that it can contain is accurate, or up-to-date, information. So make sure to account for environmental and business requirement changes as they occur.

Other than that, a good disaster recovery document will outline and delineate the roles and responsibilities for various tasks during the recovery period, and will also include core information such as where to access proper credentials, backups, and other resources.

And, obviously, it will also contain step-by-step instructions for how to work through the recovery process, along with contingency plans for dealing with unexpected problems along the way.

Finally, in terms of basics, it’s impossible to stress the need to continually practice disaster recovery scenarios. Because not only will routinely practicing recovery scenarios help build the necessary skills to make this task much easier to accomplish when an actual disaster occurs, it’s really the only step you have in protecting against something going awry with your backups – that would, otherwise, go undetected.

Otherwise, in terms of some of the common pitfalls and mistakes, one of the most common errors, or problems, that people make is confusing High-Availability solutions such as database mirroring, log shipping, or replication, as a suitable substitute for a solid disaster recovery plan.

Yes, it can protect you from some types of disaster by providing redundancy, but if ‘Stan the Man’ trashes a core table and no one is looking, that ‘trashing’ will be quickly spread throughout your entire enterprise and there’s little you can do if you don’t have the ability to recover.

In fact, the truth is that rather than simplifying disaster recovery, high-availability solutions (other than clustering) typically tend to complicate it quite a bit – so don’t fall prey to this common misstep.

Another very common mistake comes from treating all databases on a server or within an organization the same. And the problem with this approach, is that the ‘One Size Fits All’ mentality works even worse with databases than it does with Bras.

So don’t fall prey to creating just a single set of backup plans, or assuming that a single recovery plan will work for all of your databases, because if you end up treating critical databases the same way that you do test and development databases, you’ll likely find that you’ve got too much protection for databases that don’t matter as much, and not enough protection for databases that matter the most.

Likewise, sophisticated file-backup utilities are no substitute for database backups handled by either SQL Server or 3rd party SQL Server Backup providers and solutions.

In other words, just because your point-and-click backup solution for other enterprise data and assets works so well, don’t fall prey to marketing that says that this solution will also ‘back-up’ databases as well – because there’s much more involved in backing up databases than merely slipping under the files using Volume Shadow Copy and sneaking off with a backup.
And all you need to truly appreciate this fact is to experience the joys of watching your database get marked as SUSPECT when you try and recover it using one of these tools.

Finally, in terms of best practices, there are a couple of things to watch out for, as well as to take advantage of in order to both mitigate potential pain points and improve cost efficiency and protection ratios when creating disaster recovery plans.

The first of these is a reminder of something very subtle when it comes to SQL Server backups – which is that Full Backups are the baseline for all of your hopes and dreams when it comes to properly recovering from a disaster.

As such, you’ll want to guard against any ad-hoc backups that might be put in place against your well choreographed backup scheduling routine, as the last thing you need is to try and recover log or differential backups against a full backup that has was deleted or moved off box because it was only generated to create a development or other copy of your data.


Likewise, when it comes to storing backups, the more backups that you can concurrently store, the better your options will be. Of course, when dealing with high-performance systems running 15,000 RPM drives on high-end controllers, you’ll likely find that you can’t keep as many local copies of your backups as you would like. As such, don’t be afraid to hook up cheaper SATA drives to relatively cheap controllers in order to provide some ‘cheap disk’ that you can use to allow for increased coverage.


In a similar fashion, if you have other production servers with ample free space, you can also create UNC shares for backup purposes as well. With the exception of really large databases, pushing backups to external locations usually isn’t a problem, though you will be at the mercy of your network when it comes to executing a restore as throughput will be limited by the network.

As such, I prefer keeping 1-2 days worth of backups on-box, while mirroring up to a week’s worth of backups (or more) on to UNC shares.

Otherwise, when it comes to retention in most cases your disaster recovery plan will be geared towards dealing with, well, disasters. But in cases where regulatory, legal, or other requirements require you to keep lost of past data, you’ll want to look at longer retention periods than just a buffer of a few days which can be used to recover from a crash or other problem.

And one great way to do that is to use rotating backup sets or locations. For example instead of just writing individual backups to .BAK files, you could use 4 different backup sets or locations, it’s possible to set up backups to use a single device or location on even weeks, and a different location on odd weeks.

Then, with another set you can store backups at the first of every month, and with the final backup set, you can store backups before any major code changes or releases.

This approach obviously takes more coordination and ends up being more complex to administer, but it can give you much greater coverage when it comes to providing different forms of protection and redundancy.

It’s also a great example of how you really need to tailor all of the various options available in order to craft a disaster recovery solution that meets your organization’s specific needs.

And to learn more about those options, or to view how-to videos covering specific backup tasks, make sure to check out some other videos in this series.

Otherwise, I’m Michael Campbell, and I hope that you’ve enjoyed this SQL Server Video, and I look forward to seeing you in subsequent videos.