In the previous blog entries we talked about the different data files, recovery models, and how they interact. Today’s entry will talk about Native SQL Server Backups. Having an understanding of the files and recovery models will help this entry make more sense. We’ll cover restoring these backup files in the next installment.
The most basic backup of a SQL Server database is a full backup. This takes a snapshot of the data and log files for the moment in time that the backup was invoked. It includes all of the objects of the database including tables and code objects. This file can be restored and will bring your database back on line. It is generally saved with a BAK file extension.
A differential backup acts like a full backup, but only backs up data that has changed since the last full backup. This is done by utilizing the “Differential Bitmap Page”. The DBP file has one bit for every 64kb of data in the data file. These 64kb segments are called extents. The DBP bits are set to all zeros when the database is created or when a full backup is taken. From then on, every time a part of an extent within a data file is changed its respective bit is changed to a 1 and remains a 1 until the next full backup. This cycle continues indefinitely. The differential backup file will backup only the extents whose DBP bit is a 1.
(Photo Credit Microsoft Technet)
Since the entire data file is not included in a differential backup, the backup file will often be much smaller and complete much quicker than a full backup. For the same reason, the differential backup file has no value on its own. Only when paired with the most recent full backup file can the entire database be restored to the point in time of the differential backup. These files also generally use a BAK file extension.
The 3rd kind of native backup is the Transaction Log backup. This type of backup does not get any information from the data file. It simply backs up a list of transactions from the transaction log. Like the differential backup file, a log backup file has no value on its own and must be paired with a full backup. Unlike a differential file, it includes only the transactions recorded since the last transaction log backup, not since the last full backup. As such, you need EVERY log backup file since the last full or differential backup, not just the most recent to properly restore. Transaction log backup files traditionally use a TRN file extension.
Remember that a Transaction Log backup can only be taken when the recovery model is set to Full or Bulk-Logged. If the database is in simple recovery then the backup type drop down will only include Full and Differential.
There are several options available during the backup process. We’ll cover 2 of the more popular options here.
Compression — This feature will compress the backup on-the-fly to the disk. There is a small CPU penalty associated with it, but is generally more than made up for in reduced execution time and disk usage. There are no special considerations for the later storage or restore of the compressed files. This setting is off by default, but really should be on absent a specific reason not to use it.
To compress just this backup, use the drop down menu in the Backup Options section.
Use the Compress backup checkbox on the Database Settings section of the server properties to set the system default.
Copy Only – Copy Only is an option allowed when taking a full backup. When this option is checked the Differential Bitmap Page is NOT reset to all zeros as it is in normal full backup. This feature is useful when a file is being created specifically to go offsite where you will not be able to later pair it with differential files. Make a Copy Only backup by checking the Copy Only box on the first backup wizard window. You can see this box on the first screenshot of the post.