SQL Server Backups Demystified

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:09

Welcome to the first installment in our series on SQL Server Backups. My name is Michael Campbell, and in this SQL Server Video we’re going to take a look at key concepts that will help demystify SQL Server Backups. By following-along with this video and the other conceptual videos in this series, you’ll gain enough insight and understanding to help make the reference, or step-by-step tutorial, videos in this series of videos much more meaningful.

Since databases typically contain important business information, everyone just expects that databases will be regularly and perfectly backed up – to protect against hardware failures or dumb moves by employees. The disconnect though, is that VERY few people, even in IT circles, know how SQL Server backups work – or how to fully implement them correctly.

In other words, unless you were born with some sort of DBA-sixth-sense that most people don’t have, or if you didn’t have Books Online embedded into your skull as part of graduation from say… pre-school, then figuring out what’s up with SQL Server backups can be a bit of a mystery.

Happily, though, demystifying SQL Server backups isn’t such a hard nut to crack, and by focusing on just these main concepts listed, this video will help give you a high-level understanding of how SQL Server Backups function and operate.

In SQL Server, a transaction represents a single unit of work that should either complete or fail as… well, a single unit of work. In programming and database circles, this characteristic is known as atomicity – which is a term that comes to us from the Greek meaning ‘indivisible’. In other words, something that is atomic can’t, or shouldn’t, be broken down into ‘smaller pieces’.
And, in programming and database circles this notion of atomicity, or indivisibility, is typically defined with lengthy definitions of the acronym ACID. Where, as you can see, ACID stands for
Atomic, or indivisible,
Consistent – meaning that things should always play by the same rules or execute the same way when run under the same conditions, Isolated which means that our operation is not reliant upon, or impacted by, external operations,
and Durable – meaning that once the operation is done, it’s…
well, done and recorded such that it can’t really be refuted later on.

Of course, at this point in any discussion of transactions and atomicity, many people’s eyes start to roll into their heads and they start to think about other, more entertaining, things.

So let’s skip a boring, in depth, review of the ‘properties of ACID’ and jump right to an OVER-USED example that helps showcase just WHY you should care about all of this transactional stuff.


Assume, for a second that you have both a savings and a checking account and that you want to move $800 out of savings and into checking.

And let’s suppose that you’re doing this at an ATM.
In banking terms, as well as in database or programming terms, this operation would be called a transaction, and if we were to write out what the order of operations for this transaction would look like, it would be something very simple – and similar to the following – where we first take money out of our savings account, and then deposit it into checking.
So, let’s assume that we pull the money out of savings, and then the ATM crashes.
Assuming that the ATM, or software, that you’re using has never heard of atomicity, or transactions, you’re out $800 and your money will likely end up making some fat-cat banker happy someday – because it never made it in to your account.
Of course, with transactions, the operation we’re looking at would be protected with a special ‘wrapper’ (known in SQL Server as a transaction) that
would say: “until BOTH operations actually happen – neither of them happen”.


Hopefully that makes sense. But just in case it doesn’t, let’s re-review that order of operations with the notion of a SQL Server transaction rolled into the mix.

With a transaction in the mix, the first thing that will happen, behind the scenes, is that SQL Server will start by ‘declaring’ what it’s intending to do.

To do this, it will create a log entry, or log record, that describes the intentions of the operation.


In terms of what’s recorded, SQL Server calculates what the balance for both accounts should be AFTER the operation completes, but records those values BEFORE the operation starts.
In database circles this is known as write-ahead logging and it is designed to make sure that SQL Server can recover your data in the case or an error, a crash, a power-outage, or whatever. And, in the case of SQL Server, write-ahead transactions are recorded as an individual transaction record that’s added to the SQL Server Log file – where a running list of transactions are ‘logged’ as they occur.

So, let’s assume that at the start of this transaction your savings account has three thousand dollars in it, and checking has none. Before SQL Server starts making the changes, it will therefore record existing balances, and then the anticipated balances.

Then, it will go ahead and move money out of savings – and our crash, or power-outage, will again occur.

But once power is restored and our server is back up and running, SQL Server will go through all unverified transaction records in the log file, and determine which transactions completed and which did not. In the case of completed transactions, SQL Server will verify that data has been correctly saved, and in the case of uncompleted transactions (such as our banking example) , SQL Server will ‘roll’ data back to the way that it was before the operation commenced.
This process of validating log records is known as ‘Recovery’ – and it takes place every time SQL Server starts up as well as every time you ‘restore’ and ‘recover’ your database as we’ll see in just a minute.

So, in the case of our banking example, once SQL Server restarted after the crash, it would detect that our operation, or transaction, didn't ccomplete at the time of the crash, and would roll both accounts back to the way they were prior to the commencement of the operation.

In other words, both accounts would be back where they belonged.
Yeah, we’d still have to redo the transaction in this case, but we also wouldn’t have 800 of our hard-earned bucks floating around in limbo somewhere because of a crash.

So, let’s take a look at what all of this means in terms of SQL Server – and in terms of your data. Architecturally, the data that you care about is stored on one, or sometimes more, data files and any time an operation, or transaction, needs to change that data,

the state of the data before the operation, and the anticipated state of the data after the operation is recorded. Then, the operation is executed, and if it completes without error or problem, the transaction record is flagged as being complete.

But not until the transaction record has been marked as completed can the data in your files be updated. So, in that sense, your data files are always kind of playing ‘catch up’ with your changes.
The implication though, is that with many users all working against a single database at any given time, the real state of the data in your database isn’t actually stored in your data files.
Instead, the real state of your data consists of the data stored in your data files, and in the changes made by any fully completed transactions at any given time.

Therefore, when it comes to backing up your data, you can’t just back up your data files. Instead, you have to backup your data files and your transaction logs, and whatever you use to restore those files has to know how to deal with SQL Server’s ‘recovery’ process – where complete transactions are rolled forward, and incomplete transactions are rolled back.

Fortunately though, it’s not like you’re going to grab copies of your files and run off under a bridge somewhere to try and figure out the ‘state’ of your database all on your own. Instead, the only reason you’ll ever care about the state of the data in your database is when you need to recover after a crash, or when you want to create a copy of your database for testing or other purposes.

Therefore, the mystery surrounding SQL Server Backups really centers on a few key things:
First of All is the notion of transactions and write-ahead logging.
Second is an understanding of the process of Recovery,
And third, is the fact that SQL Server won’t just let you ‘copy’ files while they’re in use – meaning that you’ll have to either use SQL Server or some other approved way of backing up your files. But we’ll cover that in more detail in upcoming videos.

Or course, the flip side of backing up databases is recovering or restoring them.
And interestingly enough, the terms “recover” and “restore” are used pretty much synonymously when talking about restoring your database from backups – but from a technical standpoint these terms are quite different, or distinct.

In fact, when it comes to SQL Server, the term Restore refers to ‘restoring’ your media – or, in other words, restoring a copy of your data files as they were the last time a backup was made.


But, as we pointed out, the true state of your database can’t accurately be represented with just your data files alone.
Likewise if your last backup is a few hours old, then the only record you have of transactions, or changes to your data, within those few hours is your transaction log – which can be used to accurately ‘replay’ any changes, or modifications, that have been made since the last backup was made.


Therefore, the term “recovery” is used to describe the process of replaying the transaction log and rolling forward all complete transactions, while rolling back any incomplete transactions.

So if we assume that your database crashes, or someone does the unspeakable, and you need to restore your database from a backup, the first thing that you’ll do is ‘restore’ your database media from a backup.

Then, by either using your transaction log (or backups of your transaction log – that we’ll look at creating in subsequent videos), you can ‘restore’ your database up to the point where it was when it crashed.


Or, since the log is being used to ‘replay’ transactions as they occurred, you can also ‘Recover to a Point in Time’ and all transactions completed at that point in time will be rolled forward, while any transactions that weren’t completed at that exact time will be rolled back.

So, to recap, the ‘mystery’ surrounding SQL Server backups really stems from the need for transactional logging and SQL Server’s ability to use logging information to ‘recover’ databases – which is a complimentary, or additional step, to restoring a database.

Therefore, if you want to successfully backup, and restore (as well as recover) your databases you need to ensure that you are adequately backing up both your data files and log files. Therefore, in a subsequent videowe’ll look a bit more at logging – and what kind of management is necessary for your log files.

In subsequent episodes we’ll look at the kinds of backup options you have available to you when it comes to protecting your data, and from there we’ll review some best practices,

and then finally look at actually stepping through the process of backing up and recovering databases in a step-by-step fashion that will help turn you into a pro at working with SQL Server Backups.

So with that, we’re done with this video.

Thanks for joining us and we look forward to seeing you again.