One of the most important, if not the most important tasks of the DBA is backing up your database. The database backup is the most basic disaster recovery plan you can have in place. It’s certainly the easiest to implement. Today I’m going to go over the basics of backups, including the different types of backups and how they work.
Full Backup
A full backup of a database includes all data pages in the database and just enough of the transaction log to be able to recover up to the end time of the backup. Curious as to all the internal steps involved in a full backup? Turn on trace flags 3004 and 3605 using DBCC TRACEON (3004, 3605, -1) and perform a backup a backup. Then take a look at your errorlog. Just don’t forget to turn off the trace flags when you’re done, since they can cause some performance degradation in your backups.
Differential Backup
A differential backup is an incremental backup of all changed data since the last full backup. The last full backup. Not the last differential backup. This is an important distinction to keep in mind because a lot of new DBAs (especially former Oracle DBAs) think that, in the event of a restore, they need to restore the full backup and then all of the differential backups taken after. And that’s simply not the case. You only need to restore the latest differential.
So how do differentials work? SQL Server uses something called a Differential Change Map (DCM) page to track changes to extents. The 6th page in every data file is a DCM page, and it tracks about 4GB worth of data. So, for every 4GB of data in your database, there’s a corresponding DCM keeping watch. Let’s take a quick look.
-- Start with a full backup
BACKUP DATABASE [AdventureWorks] TO DISK = N'D:SQL2008BackupAdventureWorks.bak' WITH NOFORMAT, INIT, NAME = N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- Look at the DCM
DBCC TRACEON (3604)
DBCC PAGE('adventureworks',1,6,3)
GO
Scroll down through the results until you get to the DIFF MAP section. Your results should look something like this:
DIFF_MAP: Extent Alloc Status @0x000000000F51A0C2
(1:0) – (1:24) = CHANGED
(1:32) – (1:21752) = NOT CHANGED
Now let’s update some data and take another look.
-- update some data
update Production.TransactionHistory
set Quantity = 100
GO
-- check out the differential map now
DBCC PAGE('adventureworks',1,6,3)
GO
Again, scroll through the results to the DIFF MAP section. Now it probably looks more like this:
DIFF_MAP: Extent Alloc Status @0x000000000F51A0C2
(1:0) – (1:24) = CHANGED
(1:32) – (1:152) = NOT CHANGED
(1:160) – = CHANGED
(1:168) – (1:224) = NOT CHANGED
(1:232) – = CHANGED
(1:240) – (1:20096) = NOT CHANGED
(1:20104) – (1:20160) = CHANGED
(1:20168) – (1:20224) = NOT CHANGED
(1:20232) – (1:20288) = CHANGED
…
So by reading 1 page for every 4GB of data, SQL Server knows exactly what extents have been changed and therefore knows what extents need to be backed up. And that’s what makes differential backups so fast. If we were to perform a differential backup now and check the DCM page, we’d see no difference. However, if we performed a full backup, the DCM page would revert back to its original state.
Note that you can take a special type of full backup, called a Copy-Only backup that will not reset the DCM pages. Use this if you need to take a one-off backup, say to refresh a Dev environment, without interrupting your normal backup plan.
File Backup
Individual database files can be backed up and restored using full or differential file backups. This is helpful if only one data file is corrupted or lost and needs to be restored. File backups can also make backing up very large databases more manageable. But you really need to think through your backup/restore strategy if you’re using file backups. Miss one file and you could render your database unrecoverable. In my humble opinion, they’re generally not worth the administrative nightmare.
Partial backup
SQL Server 2005 introduced partial backups. Like file backups, partial backups are helpful in very large databases, however they don’t entail quite so much administration. A full or differential partial backup will back up all read/write filegroups in the database and, optionally, any read-only filegroups you specify.
Transaction Log Backup
If your database is in Full or Bulk-Logged recovery mode, you need to take transaction log backups. A transaction log backup backs up the active portion of the transaction log, including all transactions since the last log backup. If you’re using Bulk-Logged recovery, the log backup also includes the actual data pages for any minimally logged transactions. Once a log backup is performed, that portion of the transaction log is freed up for reuse. This prevents those pesky transaction log full (9002) errors.