Microsoft SQL default options

You can set the following default options for all backup and restore jobs for Microsoft SQL.

See Setting default backup and restore options for SQL.

Table: Microsoft SQL default options

Item

Description

Backup method

Specifies one of the following backup methods:

  • Full – Back up entire database or filegroup.

    This option backs up the entire database or filegroup. This option is selected by default.

    See About backing up SQL databases.

  • Log – Back up transaction log.

    This option backs 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).

    If the databases are configured for the SQL Server simple recovery model, log backups are not supported. To change the recovery model, use the SQL administration tools to set the recovery model to Full. You should run a new full backup if you change the recovery mode before a log backup is run.

    Alternatively, you can run full backups only, or run full and differential backups of the SQL databases.

    See Backing up SQL transaction logs.

  • Log No Truncate – Back up transaction log – no truncate.

    This option backs up the database when it is corrupt 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.

    This option backs 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 About backing up SQL databases.

  • Database Snapshot (SQL 2005 Enterprise Edition or later) – Read-only, point-in-time copy of another database

    This option creates a read only, point-in-time copy of another database.

    See About SQL 2005 or later database snapshots.

  • Full Copy-only (SQL 2005 or later) – Back up entire database or filegroup.

    Unlike the Full backup method, the Full Copy-only backup method does not reset the SQL differential baseline that is used to indicate the database blocks that have changed since the last full backup.

    After making a full backup, you can use the Full Copy-only backup method to make a copy of a SQL database without affecting the baseline backup set required to run future differential backups.

Consistency check before backup

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

  • None.

    This option does not run a consistency check 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.

    This option excludes 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.

    This option includes indexes in the consistency check. Any errors are logged.

  • Physical check only.

    This option performs 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.

Continue with backup if consistency check fails

Continues 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

Specifies the 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.

    This option does not run a consistency check after a backup. Symantec strongly recommends that you always run a consistency check either before or after the backup.

  • Full check, excluding indexes.

    This option excludes 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.

    This option includes indexes in the consistency check. Any errors are logged.

  • Physical check only.

    This option performs 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. This option is selected by default.

Display filegroups when creating new backup jobs

Displays filegroups 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)

Adds checksums to the SQL 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 backup.

Database snapshots to keep

(SQL Server 2005 or later) Displays 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 Server 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 or later database snapshots.

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

Creates 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

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

SQL Server (2008 or later) software compression

Specifies the following compression setting you want to use for this backup job:

  • None.

    Do not use compression.

  • Compress.

    Use SQL Server 2008 or later compression.

SQL compresses the data on the computer on which SQL Server 2008 Enterprise Edition or later is installed. Therefore, faster SQL 2008 or later backups should occur if you use SQL compression.

If you back up remote SQL 2008 or later computers and you use SQL 2008 or later software compression, you must use the latest version of the Remote Agent.

You can find a list of compatible operating systems, platforms, and applications at the following URL:

http://entsupport.symantec.com/umi/V-269-1

Symantec recommends that you do not use SQL 2008 or later software compression in a backup job that uses Backup Exec-initiated software compression. Minimal additional SQL 2008 or later compression benefits are gained when you enable Backup Exec compression. In fact, in jobs where both compression schemes are used, backup times may increase.

SQL 2008 or later software compression is not used if a backup job that includes SQL 2008 or later data uses the Advanced Open File Option.

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

Lets 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

Indicates that you have additional differential or transaction log backups to be restored in another restore job.

Leave the database in read-only mode

Creates and maintains a standby database during transaction log and database restores. See your SQL documentation for information on standby databases.

Consistency check after restore

Specifies one of the following consistency checks:

  • None.

    This option is for 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.

    This option excludes 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.

    This option includes indexes in the consistency check. Any errors are logged. This option is selected by default.

  • Physical check only.

    This option performs 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.

Overwrite the existing database

Replaces 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.

Microsoft SQL default options