SQL backup options

You can set the following options when you create a backup job for SQL.

See Setting backup options for SQL.

See Backing up SQL databases.

Table: SQL backup 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.

    Select this option to be able to select No recover – Place database in loading state or Standby – Place database in standby state under Enable advanced log backup options.

    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 otherwise 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 in time that the differential backup was created, you should also create multiple log backups between the differential backups.

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

    This option backs up the entire database or filegroup without affecting future differential or log backups.

    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.

Database snapshots to keep

(SQL 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 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.

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.

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

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 options 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 afte
    r 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.

Enable advanced log backup options

Enables either the No Recover – Place database in loading state option or the Standby – place database in standby state option to apply to the backup.

This option is only available after you select the backup method Log – Back up transaction log.

No recover – Place database in loading state

Places the database in a loading state when the log file backup completes. Users cannot connect to or query the database while it is in a loading state.

This option is only available after you select Enable advanced log backup options.

Standby – Place database in standby state

Places the database in standby mode when the log file backup completes. Users can connect to and query the database when it is in standby mode, but cannot update it.

You can convert a standby database to a live database by restoring the latest transaction log. Ensure that you select the following recovery completion state Leave the database ready to use; additional transaction logs or differential backup cannot be restored.

This option is only available if Enable advanced log backup options has been selected.

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.

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.

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.

Guide Me

Starts a wizard that helps you select backup job properties for SQL.

More Information

About consistency checks for SQL

SQL backup options