Points of clarification regarding SQL Server backup/restore

Question

I'm learning about SQL Server backup/restore options. Not in a class or anything, but because I've recently realized that the guy here before me set things up very inefficiently. For instance, full backups ran once a day, and transaction backups ran once an hour. All this was done through jobs, not maintenance plans. In researching how to improve the setup where I work, I've learned a lot, but also gotten stuck on a few points. I'm hoping for confirmation of what I think I know, and clarification for what I don't yet fully grasp. Thanks in advance!

As I understand it, a full backup is just that--a complete backup of every bit in the database files, including transaction logs, up to the time the backup began. A differential backup is the same thing, but only encompasses changes made since the last full or differential backup was performed. These generally run once a day or so, whereas full backups run once a week or so.

First question: how does the system know when that last backup took place? A flag in the database? Does it open and look in the backup destination file? Is it set internally whenever a backup runs and doesn't fail?

Transaction backups confuse me a bit. I know that this type backs up the transaction log from the last full/differential backup until the current moment, but then what's a "tail log"? I see it referenced in documentation, and as far as I know, it's just the transaction log from the time of the most recent backup until "now". If my db fails seven minutes after a backup, I'd have a seven-minute tail log to worry about, right?

Were I to restore my failed database, how exactly would I do it? Let's say it failed on Tuesday, at 3:41 PM. My full backup ran the previous Saturday at 2:00 AM, my differential ran the morning of the failure at 5:00 AM, and my transaction backup ran every fifteen minutes, so I have good data up to 3:30 that Tuesday. My backups all go to a local server for storage, call it \backups. Thus, my files are in \backups\sql\full, \backups\sql\differential, and \backups\sql\transaction_logs.

• Is the folder structure the best way to organize backups? Is there a better or more standard way, or does it not matter?
• Given all that, how do I restore my database? I've done full restores, but never the other two. I'm using SSMS on Server 2012.
• What if I were to use these backups to move my database to a new server? Would the restore process be identical?
• Any other suggestions on how I might change this setup to be better?

My guess at the second question is to first take the failed database offline, then restore the full backup like I've done in the past. I'd next do the restore again, but this time choose the file from the differential backup. Finally, I'd repeat, this time using the transaction backup to restore to 3:30. I'd lose eleven minutes of data, but I don't think I have a choice. Or could I start with the transaction backup and see if it restores correctly, whereas the 'full restore first' idea is best for moving the database?

Anything else I should know about automating backups for all our databases? Some are pretty large, and we need to make sure we have as solid a backup plan as possible. I'd greatly appreciate any input anyone has on all this.

Answer 1

A differential backup is the same thing, but only encompasses changes made since the last full or differential backup was performed.

Nay. A differential is only changes since the last full backup was performed. As a segue, how the database knows what to include in a differential backup by tracking them in special database pages called Differential Page Maps. These pages get reset on a full backup.

Your other question about "what is a tail log?", it's just the portion of the log in the database that hasn't been backed up yet.

Incidentally, the metadata on what types of backups happened when and to where is stored in the msdb.dbo.backup* tables.

Generally speaking, a restore sequence looks like this:

1. (optional) Take a tail log backup with backup log «your database name» with norecovery; This will take your database offline (technically, it puts it in recovering state). If you're in a state where your database is down, this is here to try to get the last bit of log out before you do a restore.
2. Restore the latest full backup that you can that is before the point in time that you want to restore to. Make sure you include the with norecovery clause so that the restore statement doesn't also run crash recovery, thus making further restores impossible without restarting from the beginning.
3. Restore the latest differential backup that you can that is before the point in time that you want to restore to. If you last took a full backup and haven't done a differential backup yet, there may be none (and that's okay). Make sure you include the with norecovery clause so that the restore statement doesn't also run crash recovery, thus making further restores impossible without restarting from the beginning.
4. Restore all the log files from the start time of the differential until the point in time that you want to restore to (including the tail log backup you took first if you did). Make sure you include the with norecovery clause so that the restore statement doesn't also run crash recovery, thus making further restores impossible without restarting from the beginning.
5. Lastly, do restore «your database name» with recovery; to start crash recovery.

You can use the same process to do a restore to another server. You may have to include optional clauses on the restore of the full file like move «logical file» to «new location» or replace, and you may not take a tail log backup. But all of the broad strokes are the same.

As for advice on how better to do this, I wouldn't re-invent the wheel. I would (and have and continue to) used the backup solution provided for free (both "as in beer" and "as in speech") by Ola Hallengren here. It does smart things like:

• Timestamp your files
• Put the different types of backup file in separate directories
• Treats databases as logical groups (i.e. USER_DATABASES, SYSTEM_DATABASES) so you don't have to maintain an accurate list. That is, it'll do the right thing almost always.
• Deals with log shipped/availability group databases correctly
• On a differential backup, optionally take a full backup if one hasn't been taken (for instance, if a new database got added to the server)
• So much more

Seriously, check it out. My last advice would be to practice until you have this down cold. The last thing you want to be doing in an emergency is reading the manual trying to figure out how to do it.