Backing Up Nonlogged Operations

Nonlogged operations are generally used to prevent the transaction log from filling rapidly and running out of disk space. However, because not all of the details of these operations are logged in the transaction log, if the transaction log needs to be applied, these operations would not be rolled forward because the transaction log does not have all the data needed to roll them forward.

Using differential database backups is a good way for backing up nonlogged operations because differential database backups record all the changes made to the database since the database backup was created, including nonlogged operations. Therefore, a recommended process for backing up a database after nonlogged operations have occurred is:

1. Back up the database regularly, such as every night.
2. Create a differential database backup periodically and after every nonlogged operation, such as every four hours or more for highly active systems.
3. Create transaction log backups between each differential database backup, such as every 30 minutes.

The process for restoring the database where nonlogged operations have occurred is:

1. Restore the most recent database backup.
2. Restore the last differential database backup.
3. Apply all transaction log backups created after the last differential database backup was created.

Another strategy for handling nonlogged operations is to ensure that all nonlogged operations occur in a separate database, isolating the nonlogged operations from the database containing the potentially more valuable data. For example, a user can create a table named pictures with large image data (for which nonlogged operations are often used to insert the image data) in a database separate from the main production database. The transaction logs in the main production database can be backed up without regard for any nonlogged operations that occur using the pictures table.

See Also