Microsoft SQL Server backup types

Microsoft SQL Server supports a number of types of backup. Some of them are supported by the Tivoli Data Protection for SQL Server Legacy backup method;

others are supported by the Data Protection for SQL Server Legacy and VSS backup methods, and others are not supported by Data Protection for SQL Server.

Microsoft SQL Server supports the following backup types:

򐂰 Full database backup

A full database backup backs up an entire Microsoft SQL Server database and the portion of the transaction log necessary to provide a consistent database state. With both full and differential backups, the copy includes enough information from any associated transaction logs to make a backup consistent with itself. The portion of the log included contains only the transactions that occurred from the beginning of the backup until its completion. This backup type is supported by the Legacy and VSS backup methods.

Note: VSS backups are available only on Microsoft SQL Server 2005 on Windows Server 2003.

Chapter 1. Introduction 27

򐂰 Differential backup

A differential backup backs up only those data pages in a Microsoft SQL Server database changed since the last full backup, as well as a portion of the transaction log. This is equivalent to an incremental backup on the Tivoli Storage Manager backup-archive client. This backup type is supported by the Legacy backup method only.

򐂰 Log backup

A log backup backs up only the contents of a Microsoft SQL Server database transaction log since the last successful log backup. Before doing the first log backup, you must have done a full backup or its equivalent first. Log backups normally follow full backups. The portion of the log included in full and differential backups is not equivalent to a log backup. Additionally, in full and differential backups, the log is not truncated as it is during a log backup.

However, a log backup following a full or differential backup includes the same transactions as a full or differential. Log backups are not cumulative; they must be applied in turn against a base backup and in the correct order. This backup type is supported by the Legacy backup method only.

򐂰 Tail-log backup

A tail-log backup is a transaction log backup that includes the portion of the log that has not previously been backed up. The tail-log backup is commonly used when the database is damaged or becomes inaccessible, but the transaction log file is undamaged. This backup type is supported by Data Protection for SQL Server.

򐂰 File backup

A file backup backs up only the contents of a specified Microsoft SQL Server logical file. This can ease the scheduling for backing up very large databases by enabling you to back up different sets of files during different scheduled backups. File, group, and set backups must be followed by a log backup, but a full backup is not required. This backup type is supported by the Legacy backup method only.

Note: You do not have to do an actual full backup to constitute the equivalent of a full backup. Backing up all the groups or files in a database as well as its log is recognized as a full backup by the SQL server. A base backup may be full, group, file, or set backup.

Note: A log backup in Microsoft SQL Server terms is not equivalent to an incremental backup in Tivoli Storage Manager terms.

򐂰 Group backup

A group backup backs up only the contents of a specified Microsoft SQL Server filegroup. This enables you to back up only the set of database tables and indexes within a specific group of files. This backup type is supported by the Legacy backup method only.

򐂰 Set backup

A set backup backs up the contents of specified Microsoft SQL Server filegroups and files as a unit. This backup type is supported by the Legacy backup method only.

򐂰 Copy-only backup

At times it is necessary to back up a Microsoft SQL Server database without affecting the sequence of conventional Microsoft SQL Server backups - for example, to transfer a database from production to development

environments. To address this scenario, Microsoft SQL Server 2005 introduces the copy-only backup. A copy-only backup is a backup that does not affect the overall backup and recovery routines for the database. The two types of copy-only backups are:

– Copy-only full backups - available in all recovery models

– Copy-only log backups - available in full recovery and bulk-logged models A copy-only full backup cannot be used as a base or differential backup and does not affect differential backups. A copy-only log backup does not change the transaction log sequence; consequently it does not affect the database transaction log chain. A copy-only backup never truncates the transaction log.

This backup type is not supported by Data Protection for SQL Server. If you plan to use this feature, you must use Microsoft SQL Server tools to perform the backup.

򐂰 Offline backup

In some situations you may want to perform an offline copy of your Microsoft SQL Server database files. To do this, you can stop the SQL Server instance, and, using operating system tools or backup tools (such as the Tivoli Storage Manager backup-archive client for Windows), copy all the related files to the Microsoft SQL Server database and then restart the SQL Server instance.

A discussion of this type of backup is beyond the scope of this book. For more information, see the Microsoft SQL Server documentation.

Note: A discussion about backup strategies is included in 2.3.4, “Microsoft SQL Server backup strategies” on page 46.

Chapter 1. Introduction 29