SQL Server Magazine

Full Plus Log Backups
If you can’t tolerate any data loss on restore, use the full plus log backup strategy. This method guarantees against data loss and works well for databases that are updated frequently. Although using this strategy increases your database’s complexity and maintenance, the total amount of time necessary to back up the database will decrease.

Figure 2 shows a sample schedule for a full plus log backup strategy—a weekly full backup on Sunday, a transaction log backup on Monday, a second log backup on Tuesday, and a log backup every day of the week until the following Sunday rolls around, when you take a new full backup. A log backup includes all data and structures that have changed since the last log backup. Thus, each log backup in this schedule contains only the changes for that day: Monday’s log backup contains all of Monday’s changes, Tuesday’s log backup contains all of Tuesday’s changes, and so on.

Unless you specify otherwise, inactive records in the log are “removed” (marked for overwriting) at the end of a transaction log backup by default. You can add NO_TRUNCATE or COPY_ONLY to the BACKUP LOG command, which will leave the log records as they were before the log backup began. However, you shouldn’t use this option unless you have a lot of experience.

SQL Server 2005 lets you perform a tail-log backup, which is a backup taken after a database crash—assuming that the transaction log file isn’t corrupt. A tail-log backup captures the last few transactions since the last transaction log backup. (For a more complete explanation of tail-log backups, see the Web sidebar “What Is a Tail-Log Backup?” www.sqlmag.com, InstantDoc ID 98376.)

Using the full recovery model provides relatively straightforward recovery and is preferable when using the full plus log backup strategy. You simply restore the full backup followed by each of the transaction log backups in chronological order (i.e., the order in which they were taken), finishing with a restore of the tail-log backup. This strategy works well for production systems, especially those that are mostly transactional with few bulk operations.

If your database has regular bulk operations (e.g., bulk inserts done daily), then you might want to use the bulk-logged recovery model. Because individual records included in the bulk operation aren’t logged, this approach eliminates the overhead of SQL Server writing to the transaction log. Although you might achieve a performance advantage during the time the bulk operations are running, you run the risk of losing data on a restore operation if you don’t have the source data needed to rerun the bulk operations. If you’re using a simple recovery model, you can’t perform a log backup because this model causes the log file to be truncated on checkpoint.

To perform a full plus log backup, you must first back up the entire database, as follows:

BACKUP DATABASE AdventureWorks
TO DISK = ‘E:\SQLdata\BACKUPS\AdventureWorks_FullDbBkup.bak’
WITH INIT, NAME = ‘AdventureWorks Full Db backup’,
DESCRIPTION = ‘AdventureWorks Full Database Backup’
Then run the following code for the transaction log backup:

BACKUP LOG AdventureWorks
TO DISK = ‘E:\SQLdata\BACKUPS\AdventureWorks_TlogBkup.bak’
WITH NOINIT, NAME = ‘AdventureWorks Translog backup’,
DESCRIPTION = ‘AdventureWorks Transaction Log Backup’, NOFORMAT

WITH NOINIT specifies that the backup files should be appended to the backup media, whether you’re using disk or tape. In this case, all the transaction log backups will be written to the same disk file, one after another, in sequence. NOFORMAT instructs the backup process to preserve any header information that might already be on the backup disk headers. This behavior is the default, so you don’t necessarily need to use this option, although doing so is helpful for self-documentation.

To restore a database that’s using a full or full plus log backup strategy, perform the following steps.

1. If the database is online, restrict database access by switching the database availability option (in the property window) to RESTRICTED_USER, which allows only members of the db_owner fixed database role and members of the dbcreator and sysadmin fixed server roles to access the database.
2. Perform a tail-log backup (new to SQL Server 2005).
3. Fix the problem that caused the database to crash.
4. Restore using the full backup with the NO-RECOVERY option.
5. Apply each of the transaction log backups with the NORECOVERY option, if applicable.
6. Restore the tail-log backup with the RECOVERY option.

The code to perform a tail-log backup is as follows:

BACKUP LOG AdventureWorks
TO DISK = ‘E:\SQLdata\BACKUPS\AdventureWorks_TaillogBkup.bak’
WITH NORECOVER

To perform a complete restore from a full backup, you must first restore the files for the database as follows:

RESTORE DATABASE AdventureWorks
FROM DISK = ‘E:\SQLdata\BACKUPS\AdventureWorks_FullDbBkup.bak’
WITH NORECOVERY

NORECOVERY instructs the recovery operation to leave partial transactions intact rather than roll them back. The transaction log backup(s) that follow the full database restore contain additional data that complete these partial transactions. NORECOVERY leaves the database in a nonoperational state. The full restore is immediately followed by a restore of each of the transaction log backups in chronological order, all using NORECOVERY as follows:

RESTORE LOG AdventureWorks
FROM DISK = ‘E:\SQLdata\BACKUPS\AdventureWorks_TlogBkup.bak’
WITH NORECOVERY

Finally, apply the tail-log backup with the RECOVERY option, as follows:

RESTORE LOG AdventureWorks
FROM DISK = ‘E:\SQLdata\BACKUPS\AdventureWorks_TaillogBkup.bak’
WITH RECOVERY

The full plus log backup strategy isn’t bulletproof. If one of the transaction log backups is corrupted, then you can restore only to a point before the corrupted log backup. For instance, suppose you run a weekly full backup on Sunday and transaction log backups on Monday through Saturday. If Tuesday’s log backup is corrupt, you can restore only through Monday’s backup. All of Tuesday’s work would be missing because of the corrupted log backup, so you wouldn’t want to risk violating data integrity by applying Wednesday’s transactions to Monday’s data. Even the tail-log backup would be useless.

Full Plus Differential Backups
If you want an extra level of insurance, consider adding differential backups to your full backup scheme instead of doing just log backups. This strategy is good for a transactional database that has many record inserts and updates and that can sustain little to no data loss on restore and recovery, as well as for administrators who place a priority on fast recovery.

A differential backup is cumulative; it includes all data and structures that have changed since the last full backup, regardless of when that last full backup was made, or how many previous differential backups have been run. Suppose you perform a full backup on Sunday and differential backups on subsequent days of the week, as Figure 3 illustrates. Monday’s differential backup will contain all of Monday’s changes, Tuesday’s differential backup will contain all of Monday’s plus Tuesday’s changes, Wednesday’s differential backup will contain all of Monday’s plus Tuesday’s plus Wednesday’s changes, and so on.

Restoring a differential backup generally takes less time than restoring a full plus log backup, because restoring just one differential backup takes less time than restoring a string of log backups. To perform a differential backup, run the following code:

BACKUP DATABASE AdventureWorks
TO DISK = ‘E:\SQLdata\BACKUPS\AdventureWorks_DiffDbBkup.bak’
WITH INIT, DIFFERENTIAL, NAME = ‘AdventureWorks Diff Db backup’,
DESCRIPTION = ‘AdventureWorks Differential Database Backup’

1. If the database is online, restrict database access by switching the database availability option (in the property window) to RESTRICTED_USER, which allows only members of the db_owner fixed database role and members of the dbcreator and sysadmin fixed server roles to access the database.
2. Perform a tail-log backup (new to SQL Server 2005).
3. Fix the problem that caused the database to crash.
4. Restore using the full backup with the NO-RECOVERY option.
5. Apply the latest differential backup with the NO-RECOVERY option.
6. Apply the tail-log backup with the RECOVERY option.

After restoring the full backup, do a differential restore as follows:

RESTORE DATABASE AdventureWorks
FROM DISK = ‘E:\SQLdata\BACKUPS\AdventureWorks_DiffDbBkup.bak’
WITH NORECOVERY

Then, restore the tail-log backup with the RECOVERY option, as discussed previously.

The differential backup provides a level of insurance you can’t achieve when performing only log backups. If the most current differential backup is corrupted, you can still restore from the previous differential and maintain full data integrity.

Combining Strategies
If redoing transactions for the missing day isn’t practical, you can combine full, differential, and multiple daily log backups. For example, you could perform a full backup on Sunday and differential backups on subsequent nights, plus log backups on Monday through Saturday mornings and afternoons, as Figure 4 shows. If the database came down on Friday night and needed to be restored, but Thursday’s differential backup was corrupted, you could use Wednesday’s differential for the restore, then restore the log backups taken on Thursday and Friday. The database would then be restored to the point of failure. For more information, see the Web sidebar “How Do I Recover to a Point in Time?” www.sqlmag.com, InstantDoc ID 98377.

To minimize the risk of data loss, you should consider mixing and matching full, log, and differential backups, even though doing so will complicate your backup strategy and backup file management. You also need to realistically evaluate how much data loss you can live with following a database crash and restore. Using a full or bulk-logged recovery model rather than a simple recovery model means more transaction log file activity and a larger (and longer) log file backup, but the benefit is less lost data.

Alternative Backup Strategies
SQL Server backups aren’t limited to full, log, and differential. More advanced options include the file or file group backup strategy, the partial backup strategy, and the copy-only backup strategy. For information about these strategies, see the Web sidebar “Alternative Backup Strategies,” www.sqlmag.com, InstantDoc ID 98956.

Database Access During Backups and Restores
SQL Server backups are an online process; the data stored in SQL Server is highly available during this time. Operations such as INSERT, UPDATE, and DELETE are allowed, as are SELECT statements. However, operations that would modify the underlying table or file space architecture, such as ALTER DATABASE, ADD FILE, or SHRINKFILE, can’t be done while the backup is running. If auto-shrink is turned on in your database configuration file, you might experience a conflict during a backup operation. For example, if auto-shrink tries to initiate while the backup is running, both operations might fail. Whichever operation starts first will set a lock on the file; the second operation will wait for that lock to be released before it begins. If the first operation releases the lock, then the second operation will commence. If the lock times out on the first operation, the second operation will fail. This development seems unfair to the second operation, which has to wait for the lock timeout, only to then fail. However, the rationale is that the second operation’s viability is based on the first operation succeeding. If the first operation fails, the second operation doesn’t need to proceed. To prevent this problem, consider turning off auto-shrink before performing a backup.

Most SQL Server restores are offline operations; users can’t access the database while it’s being restored. If you’re using SQL Server 2005 Enterprise Edition with the full recovery model, partial restores and restores of nonprimary file groups are online operations by default. The parts of the database that aren’t being restored, such as read-only file groups, are accessible throughout the entire restore operation. Read/write file groups are available except when they’re pulled offline to be restored. This option is immensely valuable for large databases that are heavily accessed 24 × 7 × 365. For more information, see SQL Server 2005 BOL, “Performing Online Restores,” msdn2.microsoft.com/en-us/library/ms188671.aspx, as well as the Web sidebar “Why Can’t My Database Restore Be an Online Operation?” www.sqlmag.com, InstantDoc ID 98378.

Putting It All Together
Data has become so central to businesses’ success that safeguarding it is mandatory. Backups are therefore crucial to maintain a healthy back office environment. The first step toward building business and database continuity is to make regular database backups and test them to ensure they can be successfully restored. When you create a new database, you should write the backup and restore scripts at the same time. SQL Server gives you many backup and restore options, which you can customize to meet the needs of each database.

End of Article

Prev. page     1 [2]     next page -->