Best Practices for Backing Up Commerce Server

  • Article

This topic lists the best practices for backing up Commerce Server.

  • Manage the size of the transaction log file by backing it up frequently.

    SQL Server automatically truncates the transaction log file when you back it up; therefore, the more often you back it up, the smaller the file.

  • Back up the transaction log file on a regular schedule and before performing other types of backup.

    The frequency with which you back up the transaction log file determines how accurate to the time of a failure your restore will be. You can restore your database to the condition it was in at the time of the last backup of the transaction log file before a failure. Backing up the transaction log before performing other types of backups ensures that you will be able to restore your database even if the backup fails.

  • Plan your backup strategy to meet the level of restore accuracy you need.

    SQL Server provides you with backup tools that you can use to create backups that provide you with different levels of granularity. Depending on how frequently your database changes, and the needs of your organization for up-to-the-minute restoration after a database failure, your backup strategy will be simple or complex.

  • Do not back up the tempdb database.

    The tempdb database does not contain any data required for the operation of your database. Back up Commerce Server databases to a separate physical disk from where the data and transaction log files are stored.

    Backing up Commerce Server databases to a separate physical disk:

    • Maximizes the performance of the databases being backed up.
    • Maximizes the performance of the backup because it is the only operation writing to the destination disk.
    • Protects your backup in case of a disk failure.

  • Recreate any full-text indexes when you restore the associated database.

Backing up a database does not back up full-text index data in full-text catalogs. However, if full-text indexes have been defined for tables, the meta data is backed up when a database backup is created. After a database backup is restored, the full-text index catalogs can be re-created and repopulated. For more information, see Full-text Indexes in SQL Server Books Online.

  • Use the stored procedure sp_spaceused to estimate the size of the backup and manage the space requirements.

Your backup will fail if the backup device runs out of storage room before the backup is complete. The stored procedure sp_spaceused displays the number of rows, disk space reserved, and disk space used by a table in the current database, or displays the disk space reserved and used by the entire database.

For information about using the stored procedure sp_spaceused, see System Stored Procedures in SQL Server Books Online.

  • Stripe full backups across multiple backup devices located on separate physical disks to minimize the time it takes to backup a database.

    Striping backups across multiple backup devices will speed up the backup process and reduce the memory and performance hits on any single backup device.

  • Base your backup intervals on the frequency needed to minimize data loss and time them to avoid interference with production work.

Users will not be able to write to production databases while they are being backed up. Schedule backups during lulls in user activity, such as late at night. Determine the frequency with which the databases must be backed up by studying activity levels and growth rates. For example, if management decides that if the database server crashes, they do not want to lose more than 120 minutes of data, then you must perform transaction log file backups every 120 minutes. It would make sense to schedule one of these backups at noon when there is a lull in activity and avoid contention with users as much as possible.

Copyright © 2005 Microsoft Corporation.
All rights reserved.