Setting backup options for SQL

This procedure details how to select backup job properties for SQL and provides definitions for SQL-specific backup options.

To select backup job options for SQL

  1. On the navigation bar, click the arrow next to Backup.

  2. Click New Backup Job.

  3. On the Properties pane, under Settings, click Microsoft SQL.

  4. Select the appropriate options:

    Backup method

    The following options 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).

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

    • Database Snapshot (SQL 2005 or later) - Read-only, point-in-time copy of another database - Select this option to create a read only, point-in-time copy of another database.

      See About SQL 2005/SQL 2008 database snapshots.

    • Full Copy-only (SQL 2005 or later) - Back up entire database or filegroup without affecting future differential or log backups - Select this option to back 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 2005 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 2005 database without affecting the baseline backup set required to run future differential backups.

    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.

    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.

    The Physical check only option 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 options 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.

    The option Physical check only is not supported under SQL 7.0.

    Enable advanced log backup options

    Select this option to choose 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

    Select this option to put 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

    Select this option to put 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.

    This option is not supported for SQL 7.0.

    Use checksums on backup (SQL 2005 only)

    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.

    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.

    Save to path

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

    SQL Server 2008 Enterprise Edition software compression (SQL Server 2008 Enterprise Edition only )

    Select the compression setting you want to use for this backup job. Select None if you do not want to use compression. Select Compress if you want to use SQL Server 2008 compression.

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

    If you back up remote SQL 2008 computers and you use SQL 2008 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 software compression in a backup job that uses Backup Exec-initiated software compression. Minimal additional SQL 2008 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 software compression is not used if a backup job that includes SQL 2008 data uses the Advanced Open File Option.

    Guide Me

    Click this to start a wizard that helps you select backup job properties for SQL.

More Information

Selecting data to back up

About selection lists

Consistency checks for SQL

Creating a backup job by setting job properties