Backups in SQL Server 2005/2008, Part 1: The Basics

This is the first post in a series dedicated to exploring the backup and availability options in SQL Server 2005 and 2008. It is aimed at anyone unfamiliar with the database backup options in SQL Server 2005 and 2008. I’m not going to explore every single option or scenario, the goal is to give you the language and the tools to do deep dives where you need to.

SQL Server 2005 has several DBA-job-saving options available to the would-be administrator. Think of a Database Backup as the technology to save data and Database and Availability as the technology to keep it online and available to it’s consumers.

A very brief introduction to SQL Server databases

Its important to have a few SQL Server database basics in order to understand the backup options. If you know what a recovery model is, and the difference between an .ldf and .mdf file, you can skip this section. If this is as good as a foreign language to you, read on.

Files

By default, every SQL Server database has two files: one data file (with the .mdf extension), and one transaction log file (with the .ldf extension). The data file (.mdf) holds your database objects (tables, views, etc) and the data. The transaction log file (.ldf) contains the transactions in your database. Depending on the recovery model selected, it may hold uncommitted transactions, or it may hold every transaction since the last truncation.

Wait – What’s a transaction?

According to Wikipedia, a transaction is defined as a unit of work performed against the database. If you have a particularly large database, someone may split your databases into several files, or even put them on different disks. This is often done to balance the disk activity, and it can be part of your backup strategy. A DBA might put tables that are infrequently or never changed into their own file database file.

Recovery Models

There are three recovery models to choose from: Simple, Full, and Bulk-Logged.

This is set per-database and it doesn’t affect options for the other databases on this server. The recovery model chosen affects some backup, availability, and redundancy options.

Simple
Basically, when Simple recovery is in use, the data from any committed transaction will be discarded after each checkpoint is issued. These transactions cannot be backed up or restored. The transaction log for these databases will be smaller.

Full
All transactions will be stored in the transaction log until the log is truncated or backed up. Please note that, contrary to popular belief, only transaction log backups and truncating the log remove entries from the log, full backups do not. More here.

This recovery model is required for certain backup and availability options. In order to avoid extremely large transaction logs and/or filling up the disk, you must schedule regular transaction log backups to use this recovery model.
The reason this recovery model is used is that it allows you to perform point-in-time restores (discussed more later).

Bulk-Logged
Very similar to Full recovery, except some operations are minimally logged. A list of details regarding which operations are minimally logged can be found here. This recovery model also allows point-in-time restores, but not to any point of a non logged operation.

Now that you’re up to speed on files, transactions, and recovery models . . .

What are the backup options?

Full backups — These are exact copies of your database at the time they’re taken. They “stand alone” and can be restored back to your server, or onto other SQL Servers. The output of a full backup will be one file, but will include the transaction log and all data files for the database.

Differential backups — These are backups that include all of the changes since the last full backup that was taken. The result will be one backup file and will only hold the changed data. You will require the most recent full backup in order to restore the differential backup(s).

Transaction Log Backups — These are backups of all of the transactions in the exact order of occurrence, since the last full backup, OR the last transaction log backup. Transaction log backups require the database to be in “Full” recovery mode.

Backups can be set up, created, and scheduled via SSMS or scripted and run as code. If someone has added several files to a database or has invoked some of the high-availability options, there are additional options for consideration. This brings me to a couple special circumstance options.

Copy-Only backups

Introduced in SQL Server 2005 as an option and in SQL Server 2008 as an option in the GUI, copy-only backups do not affect the chain of backups. This is a great addition to the platform as it allows backups to be taken in special circumstances, merely for the purpose of duplication.

Partial Backups

Partial backups are new to SQL Server 2005, and are intended to be used on databases where the tables have been segmented into filegroups, and some of them set to read-only. In this situation, the partial backup would back up all changed data from the non-read-only filegroup.

This is a great feature for large data-warehouse type databases but won’t be used by everyone. The ideal candidate is someone who has a large data warehouse or a similar database where there is archive type date in the database.

This feature is not accessible from SSMS, which means you will need to write SQL to use it.

Differential Partial Backups

Only to be used with partial backups, all of the same prerequisite circumstances exist for partial backups, with the additional requirement that your must be using partial backups.

SQL Server Enterprise Edition brings a new option for compression of backups. Any edition of SQL Server 2008 can restore compressed backups, but only Enterprise Edition can create them. This option will reduce the size of your database backups.