Administering CA ARCserve Backup › Managing the Database and Reporting › How to Protect the CA ARCserve Backup Database › Specify Microsoft SQL Server Backup Options for the CA ARCserve Backup Database

Specify Microsoft SQL Server Backup Options for the CA ARCserve Backup Database

CA ARCserve Backup lets you protect the CA ARCserve Backup database using full, incremental, and differential backup methods. This capability lets you use rotation schemes and schedules to protect the database. Additionally, CA ARCserve Backup lets you back up only the transaction log and check the consistency of the database before the backup job starts or after the backup job completes.

Note: For information about how to protect Microsoft SQL Server databases that do not function as the CA ARCserve Backup database, see the Agent for Microsoft SQL Server Guide.

To specify Microsoft SQL Server backup options for the CA ARCserve Backup database

1. Open the Backup Manager windows, select the Source tab, and expand the Windows System object and locate the server that is hosting the CA ARCserve Backup database.

   The server hosting the CA ARCserve Backup database can be a primary server, a member server, or a remote system. If the server hosting the Microsoft SQL Server database does not appear in the Backup Manager directory, you must add the server to the directory tree under the Windows Systems object before continuing. For more information, see Back Up Remote Servers.

   Note: To specify SQL Server backup options, you must authenticate using Windows or SQL Server credentials.

2. Expand the server, right-click the CA ARCserve Backup database object, and select Agent Option from the pop-up menu.

   The Agent Backup Option dialog opens.

3. From the Agent Backup Option dialog, specify the options that you require to protect the database.

   The following backup methods are provided:

   • Use Global or Rotation Options--Backs up the database selected using the job’s Global or Rotation Phase Backup Method. The Global or Rotation Options provides the following options:
     • The Full job method will result in a Full backup of the database.
     • The Differential job method will result in a Differential backup of the database, unless this database has not yet had a Full backup.
     • The Incremental job method will result in a Transaction Log backup With Truncation for databases using the Full and Bulk-Logged Recovery Models, and a Differential backup of databases using the Simple Recovery Model, unless this database has not yet had a Full backup.
     • The three main System databases are exempt from the Global or Rotation job method; selecting this option for databases [master], [model], or [msdb] will always result in a Full backup.
   • Full--Full backup is performed. The files included in the Database Subset will be backed up in their entirety.
   • Differential--Backs up data that has changed since the last Full backup. For example, if you ran a complete backup of your database on Sunday night, you can run a differential backup on Monday night to back up only the data that changed on Monday. This option is not available for the [master] database.
   • Transaction Log--Backs up only the Transaction log. This option is only available for databases using the Full and Bulk-Logged Recovery Models.

   The following Database Subset options are provided:

   The database subset backs up selected files in a database. Use this option to back up a file or FileGroup when the database size and performance requirements do not allow you to perform a full database backup.

   Note: The Database Subset options are disabled if the selected Backup Method is Transaction Log Only.

   • Entire Database--Backs up the entire database.
   • Files and FileGroups--Backs up selected files in a database. Use this option to back up a file or FileGroup when the database size and performance requirements make it impractical to perform a full database backup. This option is available only for databases using the Full and Bulk-Logged Recovery Models.
   • Partial Database--Backs up the Primary FileGroup, and any other Read-Write FileGroups. For a Read-Only database, only the Primary FileGroup will be backed up. This option requires SQL Server 2005 or later.
   • Back up Transaction Log After Database--Backs up the transaction log after the database is backed up. This allows you to perform a Full or Differential backup and a Transaction Log backup in the same job. This option is available only for databases using the Full and Bulk-Logged Recovery Models.

   The following Log Truncation Options are provided:

   • Remove inactive entries from transaction log, after backup--Truncates the log files. This is the default option.
   • Do not remove inactive entries from transaction log, after backup--Retains inactive log entries after backup. These entries are included in the next Transaction log backup.
   • Backup only the log tail and leave the database in unrecovered mode--Backs up the log and leaves the database in a restoring state. This option is available for Microsoft SQL Server 2000 or later. Use this option to capture activity since the last backup and take the database offline to restore it.

     Important! Do not use the "Backup only the log tail and leave the database in unrecovered mode" log truncation option to back up the ARCserve Database. Performing a backup with this option causes the database to be placed in an offline status, and you can lose the ability to find the backups of the ARCserve Database in order to perform a restore and bring the database online. If you perform a backup of the ARCserve Database using this option, you can use ARCserve Database Recovery Wizard to recover the CA ARCserve Backup database and bring it back online.

   The following database consistency options are provided:

   A DBCC tests the physical and logical consistency of a database. DBCC provides the following options:

   • Before Backup--Select this option to check the consistency of the database before the backup starts.
   • After Backup--Select this option to check the consistency of the database after the backup completes.
   • Continue with backup, if DBCC fails--Select this option to continue the backup even if the check before backup operation fails.
   • After restore--Performs DBCC after the restore of the database.
   • Do not check indexes--Select this option to check the database for consistency without checking indexes for user-defined tables.

     Note: The system table indexes are checked regardless of whether you select this option.

   • Check the physical consistency of the database--Select this option to detect torn pages and common hardware failures. In addition, it checks the integrity of the physical structure of the page and record headers, and the consistency between the page’s object ID and index ID.

     Example: How DBCC Options Work

     The following example illustrates how DBCC options work in conjunction with Override Global Options on the Agent Backup Options dialog.

     • With Override Global Options specified, the DBCC options selected at the database level will be the only DBCC options specified.
     • With Override Global Options not specified, all the DBCC options specified for the database and all the DBCC options selected in the Global options will be applied together.

     On the Global Options/Agent Options tab, the DBCC options that follow are specified:

     • After backup
     • Do not check indexes

     On the Agent Backup Options dialog, Override Global Options is not selected and the DBCC options that follow are specified:

     • Before backup
     • Continue with backup, if DBCC fails

     When you submit the backup job, CA ARCserve Backup applies the DBCC options specified in logical order: Perform the DBCC before the backup starts. If the DBCC fails, perform the backup. After the backup is complete, do not check the indexes.

   The following miscellaneous options are provided:

   • Include Checksums generated by SQL Server--Includes error checking information from Microsoft SQL Server, which can be used to validate the integrity of the backed-up data during restore. This option requires SQL Server 2005 or later.

     All error messages that are generated during the DBCC are recorded in the Agent for Microsoft SQL Server log file named sqlpag.log. The log is located in the Backup Agent directory.

   • Override Global Options--Enabling this option will override the Global option setting pertaining to the selected database.

     Note: Backup Method and Transaction Log Truncation options are not affected by this option because they can be overridden separately. This option is available only on a per-database basis.

   • SQL Native Backup Compression--This option applies to only SQL Server 2008 (Enterprise) and later versions. If enabled, this option lets CA ARCserve Backup use SQL Server database backup compression settings, which results in faster backup times and smaller sessions.
4. Click OK.

   The Agent Backup Options are applied.