Internacionales

A backup strategy is one of the most import parts of your planning considerations, because every environment is unique.

The more simple backup strategy you use, the more simple is your restore process. At the same time, the more simple backup strategy results in more limitations, considering both storage utilization and the restore operation.

The more complex backup strategy you use, the more work must be done when setting up the scheduled backups. At the same time, the more complex backup strategy gives you more options, considering both storage utilization and the restore operation.

Note: Certain commands in SQL Server, such as the BACKUP LOG with the

TRUNCATE_ONLY option, truncate the log and break the log chain sequence, causing the database to stay vulnerable to media failure until the next execution of a full or differential database backup. It is strongly recommended to only use these commands if you fully understand the resulting impacts.

You must balance business recovery requirements and available resources to define the right backup strategy for you. We recommend you to clearly identify and document business recovery requirements, the strategy being used to backup the environment, the recovery routines to restore and recover the environment, and also resources involved in the backup and restore procedures.

Different backup strategies are available depending on specific requirements for network traffic, backup window and acceptable restore times.

Some commonly used strategies are discussed in next sections, as well the restore steps for each of them. Pratical examples of backup and restore routines can be found in “Data Protection for SQL Server backup” on page 103 and “Data Protection for SQL Server restore”

on page 143 respectively.

Full backup only (legacy or VSS backup)

This approach is best for SQL databases that are relatively small because it implies that the entire database is backed up each time. Each full backup takes the same amount of time to perfom and is directly affected by the resources available in the environment. If you have a large database you can use VSS snapshot to improve backup and restore times.

The restore process is most efficient because only the most recent full backup needs to be restored.

Figure 2-1 illustrates the full backup only strategy.

Figure 2-1 Full backup only strategy

Steps to back up database:

򐂰 Periodically perform full database backups - daily, for example Steps to restore database:

򐂰 The database is restored in one step from the full backup.

Full plus log backup (legacy backup only)

A full plus transaction log backup strategy is commonly used when the backup window or network capacity cannot support a full backup each time. In such cases, a periodic full backup followed by a series of log backups minimizes the backup window and network traffic.

Monday Tuesday Wednesday

• Perform full backups daily to Tivoli Storage Manager if:

Database is small

Database has few changes or is read-only

• Perform full backups using VSS snapshot if:

Database is larger

The restore operation involves restore the most recent full backup and the transaction logs since them.

Figure 2-2 illustrates a full plus log backup strategy.

Figure 2-2 Full plus log backup strategy

Steps to back up database:

򐂰 Periodically perform full database backups - daily, for example

򐂰 Periodically perform transaction log backups - for example, every 1 hour.

Steps to restore database:

򐂰 Restore last full backup.

򐂰 Restore all transaction logs since the last full backup until the end of the logs, or until desired point in time.

Full plus differential backup (legacy backup only)

Perform this type of backup between full backups. A differential database backup can save both time and space, because it consists of only the changed portions of a database since the last full backup, minimizing resources necessary to back up the database. This strategy also benefits the restore time, because instead of apply several transaction log backups you just apply the differential backup.

The restore operation involves restore the most recent full backup and the last differential backup after the full backup.

Figure 2-3 on page 39 illustrates a full plus differential backup strategy.

Note: You can do a point-in-time restore to a specific date and time using transaction log backups.

Sunday Monday

• Perform log backups if:

Database is often modified

You need to restore in a specific point-in-time

Figure 2-3 Full plus differential backup strategy

Steps to back up database:

򐂰 Periodically perform full database backups - weekly, for example.

򐂰 Periodically perform differential backups - for example, daily, or each 12 hours.

Steps to restore database:

򐂰 Restore last full backup.

򐂰 Restore last differential backup.

Full plus differential plus log backup (legacy backup only)

This strategy speeds the restore because it reduces the number of transaction logs that may need to be restored and applied. If, for example, a full backup is done weekly, a differential nightly, and a log backup every four hours, the restore would involve the full backup, a differential, and at most five log backups.

The difference between this strategy and the full plus differential backup is the transaction log files backup. For a database using simple recovery model the previous strategy is suitable, however, for production databases using full or bulk-logged recovery models this strategy is the recommended option.

Figure 2-4 on page 40 illustrates a full plus differential plus log backup strategy.

Sunday Tuesday

• Perform differential backups to Tivoli Storage Manager if:

Database changes frequently You want to reduce backup time

Monday

Figure 2-4 Full plus differential plus log backup strategy Steps to back up database:

򐂰 Periodically perform full database backups - weekly, for example

򐂰 Periodically perform transaction log backups - for example, every 15 minutes or 1 hour.

򐂰 Periodically perform differential backups - for example, daily, or each 12 hours.

Steps to restore database:

򐂰 Restore the last full backup

򐂰 Restore the last differential backup (before desired restore point in time)

򐂰 Restore the following transaction logs until the desired point in time.

File or group backups (legacy backup only)

Use a file backup strategy when it is impractical to backup an entire database due to its size and accompanying time and performance issues. When performing restores for a file or file group, it is necessary to provide a separate backup of the transaction log.

Figure 2-5 on page 41 illustrates a file backup strategy.

Monday Tuesday

• Perform differential backups to Tivoli Storage Manager if:

Database changes frequently You want to reduce backup time

• Perform back up logs separetely

You can restore transaction logs in a specific point-in-time

Figure 2-5 File backup strategy

Steps to back up database:

򐂰 We recommend to perform an initial full backup of the database.

򐂰 Periodically perform transaction log backups - for example, every 15 minutes or 1 hour.

򐂰 Periodically perform file or group backups.

Steps to restore database:

򐂰 Restore the last file or group backup (before desired restore point in time)

򐂰 Restore the following transaction logs until the desired point in time.

Use VSS and legacy backups together

Using VSS and legacy backups together can implement a highly-effective backup solution for Data Protection for SQL data.

Microsoft supports and recommends using both methods of backup in your complete backup strategy. However, Microsoft also states that you cannot mix the two types of backups. For example, a legacy differential backup cannot be applied to a VSS full backup.

Table 2-8 gives more details of this strategy.

Table 2-8 Backup strategy characteristics using VSS and legacy backups together

Relationship between database recovery models and backup strategies

Table 2-9 shows what backup strategies are supported for each database recovery model.

Strategy characteristics Legacy backup only Legacy backup plus VSS backup

Available backup types Full (1 per week) Incr (4 per day)

Legacy Full (1 per week) VSS Full (1 per day)

Note: Backups to local shadow volumes are usually dictated by versions because of space limitations and provisioning of VSS storage.

Sunday Tuesday

• Perform file or filegroups to Tivoli Storage Manager if:

Database is very large A full backup would take too long

• Backup transaction log separetely

• Complex to manage

Monday

File 1 File 2

Table 2-9 Supported backup strategies for each database recovery model