Section 19.4. Backups | Backup & Recovery: Inexpensive Backup Solutions for Open Systems

19.4. Backups

With SQL Server 2005, you can usually perform most backup operations without worrying about performance impact to users. In previous versions, greater consideration has to be given to your database architecture and backup plan so performance is not degraded. Backups can be performed at any time, but if the server is currently trying to create or delete a file, the backup is postponed until these operations are complete. Additionally, when a backup is in progress, a database cannot be created or deleted. Even though backups are not supposed to impact performance, it is always a good idea to schedule backups during the least busy time for your database.

With SQL Server, you can use many different backup techniques to minimize your downtime. This includes the creation of hot, warm, and cold standby servers. Depending on the budget and using various mirroring, log shipping, and restore processes, it is possible to create scenarios where downtime ranges from nonexistent with mirroring to possibly several hours with cold standby to someplace in between with log shipping.

19.4.1. Backup Devices

Backup devices can be any type of media, such as disk, tape, removable storage, or network storage (such as SAN or NAS). In previous versions of SQL Server, it was necessary to explicitly define a backup device before making a backup. In SQL Server 2005, backup devices are created on the fly. However, it is usually considered good practice to specifically define backup devices for continuity and simplification of administration.

Unless you are using commercial backup software, SQL Server requires a tape device to be physically attached to the server on which the database instance you wish to back up is running. Backup operations to remote tape devices are not supported.

19.4.1.1. Logical and physical devices

Backup devices can be identified by either their physical or logical name. The physical name is the name as it would appear to the operating system, such as d:\backups\backup.bak. The logical name is simply an alias for the physical device name. The mapping of the logical device name to the physical device name is stored within the system tables. The logical name is simply an easier reference to the physical device name.

19.4.2. Recovery Models

A SQL Server recovery model helps in planning what to back up and how best to perform that backup. There are three primary recovery models available: simple, full, and bulk-logged. It is best to verify the recovery model in use for a particular database before beginning your backup and restore operations because the type of backup needed may be dictated by this setting. The recovery model affects how much data can be restored and the performance and duration of the restoration.

Simple recovery model: Simple recoveries, as the name implies, are the most basic of the recovery types in SQL Server. With this type of backup, the transaction log is automatically truncated and any inactive logs are dropped. This allows you to restore only from your last full backup because no transaction logs are available to be replayed.
Full recovery model: Full recoveries offer what simple recoveries don't because they also include the transaction logs. Replaying the transaction logs allows the database to be restored to its most recent point in time.
Bulk-logged recovery model: Bulk-logged backups are similar to full backups because they back up the existing data as well as the transaction logs. The major difference is that if this recover model is selected, any BULK transactions are not written to the transaction log. This enables you to restore all data up to the most recent point in time, except for those BULK transactions that were still present in the transaction log

The recovery model can be viewed from the GUI or by Transact-SQL.

19.4.2.1. SQL Server 2005

To find out the recovery model for SQL Server 2005, run the sp_helpdb stored procedure:

sp_helpdb Database_name

Look in the status column for the RECOVERY= section that contains the name of the current recovery model. Alternatively, you can execute the following Transact-SQL query:

select name, recovery_model from sys.databases;

This shows you a listing of all databases, followed by the number 1, 2, or 3 in the recovery_model column. The integer in the recovery_model column indicates one of the following values:

Full recovery model
Bulk logged recovery model
Simple recovery model

To see the current recovery model in the Management Studio, follow these steps:

Open the Management Studio and connect to an instance of the SQL Server Database Engine.
Expand the server you wish to work with.
Expand the Databases group.
Select a user database, or expand a system database to work with.
Right-click on the database, and select Properties.
Click Options in the "Select a page" window.

19.4.2.2. SQL Server 2000

One option for finding the recovery model in SQL Server 2000 is to run the sp_helpdb stored procedure:

sp_helpdb Database_name

The status column for the RECOVERY= section will contain the name of the current recovery model. Alternatively, you can execute the following Transact-SQL query:

select databasepropertyex('database', 'recovery')

To see the current recovery model in the Enterprise Manager:

Open the Enterprise Manager, and connect to an instance of the SQL Server Database Engine.
Expand the server you wish to work with.
Right-click on the database you wish to view, and select Properties.
Select the Option tab.

The recovery model can be seen in the Model pull-down menu.

19.4.3. Backup Types

There are many different types of backups in SQL Server. Each has advantages and disadvantages depending on your backup strategy.

19.4.3.1. Full

A full database backup is exactly what the name suggests; it creates a full backup of the specified database. This includes all objects, system tables, data, and portions of the transaction logfiles. This type of backup allows you to completely restore the server to the state when the backup finished.

19.4.3.2. Differential

A differential backup contains all the changes since the most recent full backup. An initial differential backup with a relationship to the full backup is called the differential base. Over time, the differential grows, often getting close to the size of the differential base. It is therefore important to schedule both regular full backups and differentials.

19.4.3.3. Transaction log

It is important to back up the transaction log because all transactions are written to this log before ever being committed to the database. Backing up the transaction log allows you to recover the server to the most recent time since the failure. There are three different ways to back up transaction logs: a full or pure backup, a bulk log backup and a tail log backup. A full (or pure) backup contains the full transaction log for a given interval and does not include any bulk changes. Note that with this backup type, PIT recovery is not an option. A bulk log backup is similar to the full/pure, except it does contain transactions changed by any bulk operations. The tail log backup method should be used if corruption of the database is suspected. This backs up the transaction log records that have not yet been backed up by either of the previous two methods. This backup method can contain either regular or bulk logged data.

When transaction logs are restored, they are always applied in sequence, starting from the oldest and moving to the most recent. This playback is done after the most recent full or differential backup is restored and then continues through all the logs. This sequence of logs is called a log chain. The log chain must be intact for the entire restore process to work.

Note that transaction log backups are available only in the full and bulk-logged recovery models, not in the simple model.

19.4.3.4. Copy-only backup

Copy-only backups provide a convenient way to take a copy of an existing database to use on another server. Copy-only backups do not reset the backup flag marker, so differential backups on the initial server are not affected. (The backup flag marker is similar to the archive bit in Windows.)

19.4.3.5. Partial backups

Partial and differential partial backups are new in SQL Server 2005. A partial backup is very similar to a full database backup except it doesn't contain all filegroups. It includes all the data in the primary filegroup as well as every read-write filegroup. It also can include user-specified files.

A differential partial is the same as a partial except it includes only the data in those extents that have changed since the last partial backup. As with other differential backups, the backup before the partial is called the base for the differential.

19.4.3.6. File and filegroup

An alternative to backing up your entire database is to back up files or filegroups individually. This has the advantage of possibly faster restores because if only one file fails, this file can be restored without having to do a complete database restore. Since a filegroup is simply an easy way to manage multiple files in a database, a filegroup backup is a way to more easily back up multiple files.

19.4.4. Backup/Restore of System Databases

System databases contain all the information about the running server's configuration as well as many other specifics such as file and filegroup location information, storage parameters, and information about system-level configurations. Without these databases, restoring your data becomes impossible. Therefore, backup and recovery of these databases is very important.

While there are six system databases previously mentioned (master, model, msdb, resource, tempdb, and distribution), the only one required for the instance of SQL Server to start is the master database. If this database becomes corrupt or damaged, there are two recovery options. The first option is for when the instance can still be started. In this case, you can restore master from your last full database backup. The second option is if the instance is so damaged that it can't even start at all. If this is the case, a complete rebuild of the master database might need to be performed. After successfully rebuilding, you would restore the master from the last full backup.

It goes without saying that nothing will work without the master database. Back up this database after every configuration change of any of your databases or after adding a new database. The master database backup doesn't take up much space, and it will save you a lot of grief in the end.

19.4.5. Viewing Information About the Backup

It is possible to view information about the history of backup operations. This information is located in the msdb database in the following tables: backupfile, backupfilegroup, backupmediafamily, backupmediaset, and backupset.

There are three commands to view the backup history: restore filelistonly, restore headeronly, and restore labelonly. The following examples use a database called Inventory that has a backup created in a backup set located at E:\Backups\Inventory.bak. To view the file list in this example, run the following command within a Transact-SQL window (Figure 19-1), which should be valid for multiple versions of SQL Server:

restore filelistonly from disk = 'E:\Backup\Inventory.bak'

Figure 19-1. Listing the backup header information

This command lists the backup header:

restore headeronly from disk = 'E:\Backup\Inventory.bak'

Of course, the same information is also available in the SQL Server Management Studio for 2005 or Enterprise Manager for 2000:

SQL Server 2005 SSMS
1. Open the SQL Server Management Studio.
2. Expand Management, and then expand Backup Devices.
3. Click the device for which the information is needed, and then right-click Properties.
SQL Server 2000 Enterprise Manager
1. Open the SQL Server Enterprise Manager.
2. Expand a server group, and then expand the server.
3. Expand Management, and then click Backup.
4. In the Details pane, right-click the named backup device, and click Properties.
5. Click View Contents.

19.4.6. Verify Backups

If you have time, it is always a good idea to verify the backup once it completes. Like most other operations, there are multiple ways to accomplish this. By far the easiest is to check the "Verify backup when finished" checkbox, as in Figure 19-2 for 2005 Management Studio and Figure 19-3 for 2000 Enterprise Manager.

Figure 19-2. The "Verify backup" checkbox in 2005 Management Studio

Take note of the Perform checksum option below the "Verify backup" checkbox in 2005. The Verify backup option verifies only that the backup can be restored. It provides no guarantee that the actual data on the backup is valid. If the "Perform checksum" option is also selected, it provides some indication of the reliability of the data.

If you want to verify the backup using Transact-SQL, issue the following command for either 2000 or 2005:

restore verifyonly from disk = 'F:\Backups\Inventory.bak'

19.4.7. Backup Expiration Date

Setting the expiration date of the backup allows the backup set to be overwritten without explicitly specifying that it is acceptable to overwrite. This date can be set using command-line backup options or from the Management Studio in 2005 or the Enterprise Manager in 2000. To specify this in the Management Studio, connect to the instance where the database is located, and then follow these steps:

Expand Databases, and then click on a database.
Right-click the database, expand Tasks, and select Backup.
After the Back Up Database dialog becomes visible, in the General page, specify the type of expirationeither a specific day or after some number of days.

Figure 19-3. The Verify backup checkbox in 2000 Enterprise Manager

To specify this in Enterprise Manager, connect to the instance where the database is located, and then follow these steps:

Expand Databases, and then select the database by clicking on it.
Right-click the database, expand All Tasks, and select Backup Database.
In the Options tab, check the box labeled "Backup set will expire," and then enter the date on which this backup will expire.

19.4.8. How to Back Up

In its simplest form, without taking into consideration backup strategies, recovery models, and other implementation details, the following steps perform a full backup of your database. The procedure is essentially the same between 2005 and 2000 with only a few of the options having different labels.

Open the SQL Server Management Studio, and connect to the instance where the database is located and expand Databases.
Select the database you wish to back up, right-click, expand Tasks, and select Backup.
The Back Up Database dialog appears, with the General page selected, as shown in Figure 19-4.

Figure 19-4. Back Up Database, General page

At first, the destination is preselected, but a diligent database administrator alters the location to be on a different disk than the actual database files. In Figure 19-4, I selected E:\Backups\Inventory.bak as my backup location. (The E: drive is a different physical drive than the C: drive, where the database and logfiles reside.)

Even though this is a quick backup, it would be negligent to not specify some of the options in the Options tab. At a minimum, select the checkbox to verify the backup, as previously shown in Figure 19-3.

Of course, this performs only the most basic of backups. Ideally, careful consideration would be given to planning your backups. This includes full or differential backups, locations of these backups, how much storage is necessary, and other details.

19.4.8.1. Command-line backup with Transact-SQL

The same disk-based backup can be accomplished using Transact-SQL and the backup database command for 2000 or 2005. The following command does a basic backup without any additional options:

backup database inventory to disk = 'E:\Backups\cmd_Inventory.bak' with name = 'Command Line Inventory Backup' go

19.4.9. Transaction Log Backups

It is also important to ensure that the transaction logs are backed up on a regular basis. This can be done only with a full or bulk logged backup type. In order to perform a transaction log backup, follow these steps:

Open the Management Studio, connect to the instance where the database is located, and expand Databases.
Select the database you wish to back up, right-click, expand Tasks, and select Backup.
Verify that "Recovery model" is set to Full or Bulk Logged.
In the Backup Type drop-down list, select "Transaction log."

These steps are shown in Figure 19-5. They are almost the same for 2000 with Enterprise Manager, but the Backup type is a set of radio buttons instead of a drop-down list.

Figure 19-5. Transaction log backup

Go through the rest of the options on the page and customize as needed. Afterwards, select the Options page, and make changes as necessary to the Transaction log section, shown in Figure 19-6. If this is a normal backup, simply leave the default "Truncate the transaction log." Select the "Back up of the tail log" option only if you are doing a tail log backup of the database after a failure, are in preparation for a restore operation, or are in preparation to fail over to a secondary database.

Figure 19-6. Transaction log options

As with most other operations, the backup of transaction logs can also be accomplished using Transact-SQL as in the following example. This example backs up the transaction log for the Inventory database to the previously created backup device Inventory_dev; this procedure is valid for 2005 and 2000:

backup log Inventory to Inventory_dev

19.4.9.1. Log truncation

Logs could theoretically grow until they fill the entire disk. To prevent this, logs are periodically truncated. Depending on the recovery model, logs are truncated at different times and in different ways. In the simple recovery model, in which log backups are not supported, log truncation is automatic, usually happening after a checkpoint. In the full and bulk-logged recovery models, the log is truncated when it is backed up.

19.4.10. Master Database Backups

It is extremely important to back up the master database on a regular basis. This database holds all the configuration information for the running system as well as all the configuration information for all databases and other information such as logon accounts. Without this database, the rest of the system is useless!

Only full database backups are supported for the master database.

19.4.11. Scheduling a Backup

To save time running manual backups, SQL Server allows jobs to be created that can automatically back up your database at a specified time. An automated job consists of two pieces: the backup job itself and a backup schedule. To create a new job, follow these steps for 2005 Management Studio:

Open the SQL Server Management Studio, connect to the instance where the database is located, and expand Databases.
Expand SQL Server Agent.
Right-click on Jobs, and select "Create new job."
Type in a name for the job, and make sure Enabled is selected.

This creates the backup job, but it is also necessary to create a schedule. That can be done from the initial job creation screen by selecting the Schedules page, or if you've closed it, by right-clicking on the backup job, and selecting Properties.

Once you've clicked on the Schedules page, select New to open the window shown in Figure 19-7. From here, enter a Name, and set the type of backup frequency.

Figure 19-7. Scheduling a backup job

In 2000's Enterprise Manager, the easiest way to schedule a backup is to use the Maintenance Plan wizard:

Open the SQL Server Enterprise Manager, connect to the instance where the database is located, and expand Databases.
Right-click on the database, select All Tasks, and then select Maintenance Plan.

This wizard walks you through the steps necessary for scheduling a backup.