Understanding Backup Options

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:15
  -
  -
  -
  -
12:30
  -
  -
  -
  -
12:45
  -
  -
  -
  -
12:46

Relational Database Management Systems are very complex. And SQL Server does a great job of shielding most users from that complexity – without sacrificing power and capability.
But one area where that complexity really can’t be hidden is backups – especially in terms of the number of different options available when it comes to backing up and restoring databases.
In the video, SQL Server Backups Demystified, we covered core concepts relative to the inner workings of backups and recovery. And in this video, we’ll take a high level look at backup and restore options to help demystify them as well.

To do that, we’ll examine all of the options by walking through a typical workflow of backing up and restoring databases - as indicated by this schematic where we have backups and restore broken down into to distinct areas.


We’ve already covered recovery models in other videos, but the important thing to remember about them is that they define what kinds of backup and recovery options are available – as they define what happens with the inactive portions of your log.
In the case of full and bulk-logged recovery models, inactive log records are preserved until they’re backed up by a transaction log backup. And, since these records contain a detailed, step-by-step, record of all of the changes made to your database, they can be used to ‘replay’ changes during restore and recovery operations.

On the other hand, databases running under the simple recovery model periodically truncate, or remove, inactive portions of the log in order to keep space requirements to a minimum. This, of course, limits your backup and recovery options to a more ‘simple’ set of options – as we’ll see in a minute.

In terms of options for the kinds of backups you can make, the baseline for all recovery models is the Full backup. Which, as you could guess from the name, allows you to backup everything in your database.
When full backups start, they throw a marker into the log file indicating that a backup has started. The backup process then dutifully copies all of the zeroes and ones in the files representing your database out to a backup file or location. Once that operation is complete, SQL Server revisits the log file, figures out what data has changes since the start of the backup, and makes sure that any of those changes that have completed are then recorded into the backup as well. As such, a full backup gives you an exact duplicate of the data and state of your database at the time the backup completes.



Because full backups can get pretty large in some cases, SQL Server also offers options to create differential backups – which backup all data that has changed since the last full backup.
The idea, therefore, with differential backups is to periodically make full backups, and then run differential backups at more regular intervals since they’re much faster and take less disk space.
In databases using the full and bulk-logged recovery models, it’s assumed that you’re also backing up the transaction log on a regular basis for two reasons:


First, to stop your log file from growing out of space. And second, to keep a copy of the detailed, step-by-step, changes made to your database as recorded in the log file – that you’ll want on hand should you ever need to restore your database back to the ‘point in time’ right before something bad happened, for example.


Likewise, in environments where you really care about your data, you can also use the transaction log to ‘replay’ any transactions that have happened SINCE your last full or differential backup.

So… for example… let’s say you’re doing full backups every night and differentials every 4 hours. If your DB crashes right before a scheduled differential backup, you’ll lose roughly 4 hours of work if you’re only using simple recovery – since you can only use full and differential backups.


But, if you’re using full or bulk-logged recovery, and if you’re doing regular log backups (say… every 15 minutes), then you could apply the last full backup, the last differential backups, and then all log file backups since that last differential backup to recover to within 15 minutes of the crash.

Better yet, since the actual database transaction log contains transaction records as well, you can usually backup the ‘tail’ of the log right before recovering from an emergency and, in most cases, you can apply those transactions back on top of everything – which typically means that you’ll only lose a few minutes worth of data when recovering from a crash.
Of course, to pull that off, you need to have a well designed and carefully rehearsed disaster recovery plan – but the point of this series of videos is to get you to that point.


In this video though, we’ll keep looking at the options – which brings us to File Backups – which, in turn, represents a very specialized and advanced option available to databases using full or bulk-logged recovery.

With File Backups, you have the option of backing up single database files or file groups – which is a powerful option in environments where databases are so large that full backups take longer than a day or so.

That said, this is such an advanced and specialized scenario, that I’d wager than fewer than 1% of SQL Server deployments even consider using this option.
And, with SQL Server 2005 and above, a similar, advanced, option for very large databases using the simple recovery model is available to create partial backups.


Now, in terms of how you actually execute your backups… there are also a number of different options. One of the easiest ways to backup a database, is just to use the GUI (Enterprise Manager or SQL Server Management Studio), as these will easily let you save a backup to disk.

You can also accomplish the same thing using T-SQL. And while the syntax may seem a bit daunting at first, it’s actually pretty easy to get the hang of.


Of course, both of these approaches are really only good for simple, one-off, backups. And therefore don’t represent the best way to go about implementing a disaster recovery plan.

Therefore, the options you’ll likely want to focus on in most cases will be to either use SQL Server’s native Maintenance Plan functionality, or to look to third party backup and recovery solutions.
Personally, I love handling backups using SQL Server’s native maintenance plans, as they provide a really easy way to schedule backups.

The only real thing that maintenance plan backups are missing though, is the native ability to compress and encrypt backups. Happily, SQL Server has a set of public APIs that make it possible for Microsoft Partners to create backup plugins that handle these needs.

As such, there are a number of really great 3rd party backup solutions out there such as Red Gate’s SQL Backup, Quest’s Light Speed, Idera’s SQLSafe, hyperbac, and others.

These tools typically cost a fair amount, but since they can decrease disk requirements for backups by as much as 60%, seamlessly encrypt data, and help decrease recovery times, they’re typically a great investment and can easily pay for themselves in environments where you’re running a bit short on disk.

SQL Server 2008 will also offer native backup compression – but only for Enterprise Editions, and I’m personally of the opinion that it won’t get used a ton, as organizations that can afford Enterprise Licenses will likely have no problems sticking with the 3rd party tools that their DBAs like anyhow.

Finally, in terms of backup options, don’t confuse High-Availability solutions, such as Database Mirroring, replication, or log-shipping, as being viable options for disaster recovery.

While they may make copies of your data available in multiple locations, they’re not a suitable substitute for disaster recovery plans, as all you need is for some fool to delete all the rows in a key table late at night and have that ‘replicate’ through your system to realize that there’s no substitute for backups.


Now, moving on to location options for your backups. These can most easily be broken down into two main options: Disk, and everything else – where everything else effectively means tape, or some funky spin on tape, such as DAT, auto-loaders, and removable disks.


In terms of benefits, tapes are typical considered cheap storage solutions, though as hard drive prices decrease, that becomes less of a benefit – especially since tape and other removable media options frequently need to be replaced.

Likewise, drastically lower speeds and small capacity or sizes lowers their value as well. Their biggest benefit, however, is that they make it easy to move copies of key data off-site.


On the other hand, Disk offers the opposite in terms of benefits and drawbacks as they’re drastically faster, typically provide plenty of capacity, yet remains hard to remove for offsite backups.


Whichever route you go though, you’ll still need to address another issue, or option: which is whether or not you will use raw files, or store your data in what are known as devices.

In SQL Server devices represent generic storage locations that treat files as a virtual address space that can be managed by SQL Server. This allows you to specify the location of a device, and then SQL Server will manage all of the backups that you want to stuff into that device. The benefit of this approach is that ensures that backups will always be in the same location.

Files, on the other hand, represent a more logical, and less complicated, approach for most users – as backups are stored as individual files with names that typically indicate the backup type and the time it was made.

This can result in lots of distinct files, but if you use a folder per database, you’ll still always be able to know where your backups are.

And with maintenance plans, it’s typically much easier to instruct SQL Server to delete individual files older than a certain time, than it is to deal with the retention periods and initialization concerns that go hand-in-hand with the use of devices.
Although, in the Backup Best Practices video we’ll address some other benefits to using devices as well.
Either way, no matter which approach you use, you’re a cotton-headed ninny-muggins if you don’t periodically test your backups to make sure that they’re viable.
Not only testing give you a feel for the restoration and recovery process under controlled circumstances, but it will also let you know if there are any flaws in your disaster recovery plan - before a disaster occurs.

Which, of course, brings us to the topic of restoring databases. And, as you can see from this slide, recovery options start with the kinds of storage you’ve elected to use, and are heavily influenced by the kinds of backups you’ve made and have available.

And, as you can see, when using simple recovery models, there’s always the possibility of data loss, so it’s therefore recommended that you don’t use this model unless you’re dealing with data that isn’t updated frequently, or isn’t critical to business operations.

Otherwise, in terms of restoring and recovering your databases, you’ll always want to take the following approach: First, start by restoring the last full database backup available prior to the point in time you’re trying to recover to.

Then, restore the last differential database backup if it’s available.

If you’re recovering a database in simple mode, then you’re done – and any changes made since the last differential backup are gone.


On the other hand, if you’re using full or bulk logged recovery, then you can add any log backups since your last full or differential backup (one or the other, not both), and those changes can be recovered as well.

Likewise, if you’re recovering from a real-life disaster, then BEFORE starting the recovery process, you’ll want to backup the ‘tail end’ of your log file – such that you can apply that after applying all transaction log backups as well.
And it’s important to call out that while you’re loading log file backups as part of restoring your database, you’ll want to prevent your database from being ‘recovered’ until all of the log files are in place.

Now, if all of that sounds pretty complicated and hard to juggle, don’t worry. Because SQL Server’s GUI for restoring databases, along with the GUIs provided by 3rd party offerings, all make this pretty easy.

In fact, in most cases, you just need to point at which file you wish to recover (be it a full backup, a differential backup, or a log file backup) and the GUI will do most of the work for you.

Only in cases where you’re trying to do this manually do you really need to understand exactly what’s going on and the exact order of operations.
And, we’ll tackle both the GUI-based approaches and the manual approaches in subsequent how-to videos that will help turn you into a pro when it comes to managing backup and recovery options.