Differential Backups Incremental and Differential

Incremental backups archive only files that have changed since the last backup of any kind. Differential backups archive any files that have been changed since the last full backup.

Incremental backups only archive files that have changed since the last backup of any kind was performed. Differential backups will archive any files that have been changed since the last full backup.

It is often desirable to group a number of tasks into a single job. For example, after each complete or differential backup, you might want to run a backup of the transaction log and, before starting the backup, do a integrity check of the database to ensure that the database backup is not corrupted. In this case, you can create maintenance plans manually or use the Database Maintenance Plan Wizard (see Figure 7.5). A major advantage of this wizard is that not only can you group more maintenance tasks in one job, you can also execute this same plan on more than one database. The more experienced database manager can construct maintenance plans using the command-line utility sqlmaint. With the Database Maintenance Plan Wizard, though, you are not able to implement backups based on files and/or filegroups.

Sign in to download full-size image

Figure 7.5. The welcome screen of the Database Maintenance Plan Wizard.

The Database Maintenance Plan Wizard will lead you through the following steps:

1.

Select databases.

2.

Choose the data optimization information options.

3.

Choose the database integrity check options.

4.

Specify the database backup plan.

5.

Specify the transaction log backup plan.

6.

Select the report options.

On completion of these steps, the wizard will first give a summary of the maintenance plan to be created. If the summary is correct, you can finish it, and then the maintenance plan job will appear in the Database Mainte-nance Plans list of the SQL Server Enterprise Manager (see Figure 7.6). The list of jobs can hold all backup tasks for all databases. Every one of them can be scheduled or manually run. (The latter is also true even if the task is scheduled.)

Sign in to download full-size image

Figure 7.6. Maintenance plans available through the SQL Server Enterprise Manager.

The types of backups you can choose in most commercial backup programs as well as the backup utility provided with Windows 2003 are as follows:

▪

Normal

▪

Incremental

▪

Differential

▪

Copy

▪

Daily

As you will see in the paragraphs that follow, different types of backups archive data in different ways. Because of this, the methods used to back up data will vary between businesses. One company might make normal backups every day, while another might use a combination of backup types. Regardless of the types used, however, it is important that data be backed up on a daily basis so that large amounts of data won’t be lost in the event of a disaster.

Before describing each of the backup types, it is important to understand that the type chosen will affect how the archive attribute is handled. The archive attribute is a property of a file or folder that’s used to indicate whether a file has changed since the last time it was backed up. As you will see in the paragraphs that follow, depending on the backup type used, the archive attribute of a file is or is not cleared after it is backed up. When the file is modified, the archive attribute is set again to indicate it has changed and needs to be backed up again. Without the archive attribute, your backup program is unable to tell whether files need to be backed up or not. Here is a description of each backup type in more detail:

▪

Full Backup The full backup, as its name implies, backs up everything specified by the user performing the backup operation. A full backup can include the operating system, system state data, applications, and any other data. With a full backup, everything that is backed up has the file system archive bit reset (cleared).This allows the incremental and differential backup types to determine if the file needs to be backed up. If the bit is still clear, the other backup types know that the data has not changed. If the bit is set, the data has changed, and the file needs to be backed up. The full backup is usually the first backup performed on a server. It takes the longest of all the backup types to complete, because it backs up all specified files, regardless of the state of the archive attribute. A full backup consumes the largest amount of backup media of any backup type. Depending on the amount of information chosen to back up and the underlying backup technology involved, it may require multiple backup media to complete. The main advantage of the full backup type is the ability to rapidly restore the data. All of the information is contained in a single backup set when this type of backup is used. The disadvantages of full backups are high media consumption and long backup times.

▪

Incremental Backups During an incremental backup operation, all specified files have their archive bit examined. If the bit is set, the file is backed up, and then the bit is cleared. This backup type is used to back up data that has changed or been created since the last full (normal) or incremental backup. It can also be used after a copy or differential backup, but because these do not reset the archive attribute, there is no way for the incremental backup to tell which files have changed since one of those backups last ran. As a result, every file with the archive attribute set is backed up. The incremental backup type is used between full backups. It is quick to perform, collects the least amount of data, and consumes the smallest amount of media. A complete restore, however, requires the last full backup and every incremental backup (in sequence) since the full backup was performed. The primary benefits of using the full/incremental backup combination are time and media savings. The main drawback of this combination is longer and more complex restore operations if there are long periods between full backups.

▪

Differential Backups The differential backup type is sometimes used as a substitute for the incremental type. A differential backup collects data that has changed or been created since the last full (normal) or incremental backup, but it does not clear the archive bit on the file. It can also be used after a copy or differential backup, but as with an incremental backup, every file with the archive attribute set is backed up. The differential backup is advantageous when you want to minimize the restoration time. A complete system restore with a full/differential backup combination requires only the most recent full backup and the most recent differential backup. Differential backups start with small volumes of data after a recent full or incremental backup, but often grow in size each time, because the volume of changed data grows. This means that the time to perform a differential backup starts small but increases over time as well. In theory, if full or incremental backups are infrequent, a differential backup could end up taking as long and reaching the same volume as a full backup.

▪

Volume Shadow Copy More of a Windows 2003 feature than a backup type, Volume Shadow Copy allows you to back up all files on the system, including files that are open by applications or processes. In previous versions of Windows, the applications would need to be stopped or users logged out to allow these files to be closed and backed up using a backup program. With Volume Shadow Copy, these files can continue to remain in use without affecting the integrity of the backup. This feature is enabled by default, but it may need to be disabled if data managed by some critical applications would be affected by the use of Volume Shadow Copy