Setting default backup and restore options for SQL

You can use the defaults set by Backup Exec during installation for all SQL backup and restore jobs, or you can choose your own defaults. You can also change the defaults for any specific backup or restore job.

To set default backup and restore options for SQL

  1. On the Tools menu, click Options.

  2. On the Properties pane, under Job Defaults, click Microsoft SQL.

  3. Select the appropriate options as follows:

    Backup method

    The following methods are available:

    Full - Back up entire database or filegroup. Select this option to back up the entire database or filegroup. This option is selected by default.

    See Backing up SQL databases.

    • Log - Back up transaction log. Select this option to back up only the data contained in the transaction log; it does not back up database data. After the transaction log is backed up, committed transactions are removed (truncated).

      See Backing up SQL transaction logs.

    • Log No Truncate - Back up transaction log - no truncate. Select this method only when the database is corrupted or database files are missing. Since the Log No Truncate method does not access the database, you can still back up transactions that you may not be able to access when the database is in this state. You can then use this transaction log backup along with the database backup and any previous transaction log backups to restore the database to the point at which it failed; however, any uncommitted transactions are rolled back.

      The Log No Truncate method does not remove committed transactions after the log is backed up.

      See Backing up SQL transaction logs.

    • Differential - Back up database or filegroup changes only. Select this option to back up only the changes made to the database or filegroup since the last full backup. Because differential backups allow the restore of a system only to the point that the differential backup was created, you should also create multiple log backups between the differential backups.

      See Backing up SQL databases.

    Consistency check before backup

    Select one of the following consistency checks to run before a backup.

    • None. Select this option if you do not want a consistency check to run before a backup. Symantec strongly recommends that you always run a consistency check either before or after the backup. This option is selected by default.

    • Full check, excluding indexes. Select this option to exclude indexes from the consistency check. If indexes are not checked, the consistency check runs significantly faster but is not as thorough. Only the data pages and clustered index pages for each user table are included in the consistency check. The consistency of the nonclustered index pages is not checked.

    • Full check, including indexes. Select this option to include indexes in the consistency check. Any errors are logged.

    • Physical check only. Select this option to perform a low overhead check of the physical consistency of the database. This option only checks the integrity of the physical structure of the page and record headers, and the consistency between the pages' object ID and index ID and the allocation structures.

    Consistency check before backup is not supported under SQL 7.0.

    Continue with backup if consistency check fails

    Select this option to continue with the backup operation even if the consistency check fails. You may want to continue with the backup when the consistency check fails if you think that a backup of the database in its current state is better than no backup at all, or if you are backing up a very large database with only a small problem in a table.

    Consistency check after backup

    Select a consistency check to run after a backup. Because database transactions can occur during or after the consistency check, but before the backup runs, consider running a consistency check after the backup to ensure the data was consistent at the time of the backup.

    The following checks are available:

    • None. Select this option if you do not want a consistency check to run after a backup. Symantec strongly recommends that you always run a consistency check either before or after the backup. This option is selected by default.

    • Full check, excluding indexes. Select this option to exclude indexes from the consistency check. If indexes are not checked, the consistency check runs significantly faster but is not as thorough. Only the data pages and clustered index pages for each user table are included in the consistency check. The consistency of the nonclustered index pages is not checked.

    • Full check, including indexes. Select this option to include indexes in the consistency check. Any errors are logged.

    • Physical check only. Select this option to perform a low overhead check of the physical consistency of the database. This option only checks the integrity of the physical structure of the page and record headers, and the consistency between the pages' object ID and index ID and the allocation structures.

    Consistency check after backup is not supported under SQL 7.0.

    Display filegroups when creating new backup jobs

    Select this checkbox if filegroups exist that you want to select for backup. If this checkbox is not selected, filegroups are not displayed as backup selections.

    Use checksums on backup (SQL 2005 or later)

    Select this option to have SQL 2005 add checksums to the SQL 2005 database data being backed up by Backup Exec. Adding checksums to the data being backed up is required if you want to use the option Run verify only; do not restore data. Using this option, along with Run verify only; do not restore data, ensures that during a restore of the SQL database, you are restoring from a verified SQL 2005 backup.

    Database snapshots to keep

    (SQL 2005 only) Use this option to select the number of database snapshots to keep on disk. As the threshold is met, older database snapshots are deleted, which are then replaced with new snapshots. Because database snapshots continue to grow as the SQL 2005 database is updated, limiting the number of snapshots enables you to minimize both the disk space and SQL Server processing time that is required when the snapshots are updated.

    See About SQL 2005/SQL 2008 database snapshots.

    Create on-disk copies of SQL backups to be placed on the SQL server where the database is located

    Select this option to create an on-disk copy of the SQL database being backed up. This option lets you simultaneously back up a SQL database to storage media while also writing a copy of the database to a disk path you specify in the Save to path box.

    This option gives IT administrators the ability to back up SQL databases while also providing database administrators with copies of the database on disk, which can be used for such things as tests and restores.

    This option is not compatible with Advanced Open File Option backups or with database snapshot backups.

    Save to path

    Enter a path in which to save on-disk copies of SQL backups.

    Leave database ready to use. Additional transaction logs cannot be restored

    Select this option to have the restore operation roll back all uncompleted transactions when you restore the last database, differential, or log backup. After the recovery operation completes, the database is ready for use. If Leave the database ready to use is not performed, the database is left in an intermediate state and is not usable.

    If you select the option when an intermediate backup is applied, you cannot continue to restore backups. You must restart the restore operation from the beginning.

    This option is selected by default.

    Leave the database nonoperational. Additional transaction logs or differential backups can be restored

    Select this option during a restore if you have additional differential or transaction log backups to be restored in another restore job.

    Leave the database in read-only mode

    Select this option during transaction log and database restores to create and maintain a standby database. See your SQL documentation for information on standby databases.

    Consistency check after restore

    Select one of the following consistency checks:

    • None. Select this option if you are doing sequential restores. Do not run a consistency check after a restore until all sequential restores have been done. If a consistency check is selected during a restore, the restore will complete but the consistency check will not be done. Check the job log for this information.

      If you selected the option Leave the database ready to use, select one of the following consistency checks:

    • Full check, excluding indexes. Select this option to exclude indexes from the consistency check. If indexes are not checked, the consistency check runs significantly faster but is not as thorough. Only the data pages and clustered index pages for each user table are included in the consistency check. The consistency of the nonclustered index pages is not checked.

    • Full check, including indexes. Select this option to include indexes in the consistency check. Any errors are logged. This option is selected by default.

    • Physical check only. Select this option to perform a low overhead check of the physical consistency of the database. This option only checks the integrity of the physical structure of the page and record headers, and the consistency between the pages' object ID and index ID and the allocation structures.

    Consistency check after restore is not supported under SQL 7.0.

    Overwrite the existing database

    Select this check box to replace a database or file group, even if another database or file group with the same name already exists on the server. If Overwrite the existing database is not specified for a restore, SQL performs a safety check to ensure that a different database or file group is not accidentally overwritten. Refer to your SQL documentation for more information about the safety check that occurs when this option is not selected.

More Information

Setting backup options for SQL

About restoring SQL databases and file groups