Advanced Backup – SQL Server iDataAgent

Table of Contents

Full Backups

Full backups provide the most comprehensive protection of data.

Backups for any client start with a full backup. The full backup becomes a baseline to which subsequent backup types are applied. For example, a full backup must be performed before a transaction log backup can be initiated.

Use the following steps to run a full backup:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server.
  2. Right-click an <Instance> and click Backup All Subclients.
  3. Select Full as the backup type and click Immediate.
  4. Click OK.

    You can track the progress of the job from the Job Controller.  When the backup has completed, the Job Controller displays Completed.

Transaction Log Backups

A transaction log backup captures the transaction log which contains a record of all committed or uncommitted transactions. Transaction log backups are consistent with the start time of the backup.

The use of transaction log backups make point-in-time recovery possible. This is useful in the scenario of a database failure where it is unacceptable to lose any data and you want to restore to the point of failure. If you use only full and differential backups, you will be able to restore to the time of the backup, but not to a point-in-time between backups.

A transaction log backup is similar to a traditional incremental backup you might perform on a file system because the transaction log backup contains only the new changes since the full or another transaction log backup.

Each time a transaction log is backed up it is truncated to the exact time of the backup. No checkpoint is issued at this time, therefore dirty pages are not written to disk before or after a transaction log backup. If there are dirty pages, any completed transactions will need to be rolled forward if a transaction log restore is performed. Any transactions that are not completed at the time a transaction log backup is performed are rolled back during a restore involving a transaction log backup.

Transaction log backups will truncate the log file and free the inactive virtual log files for reuse, it does not reduce the size of a physical log file.

Use the following steps to run a transaction log backup:

  1. Ensure that the SQL Server database is in full or bulk-logged recovery mode.
  2. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server | <Instance>.
  3. Right click a <Subclient> and click Backup.
  4. Select Transaction Log as backup type.
  5. Click Immediate.
  6. Click OK.

    You can track the progress of the job from the Job Controller.  When the backup has completed, the Job Controller displays Completed.

Automatically Start a Transaction Log Backup after a Successful Backup

You can start a Transaction Log backup automatically after a successful Full or Differential backup. This is useful when you want to back up logs immediately after a data backup, and allows you to do so without creating two scheduled jobs.

Use the following steps to automatically run a transaction log after a backup:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server | <Instance>.
  2. Right click an <Subclient> and click Backup.
  3. Select Full or Differential backup type and click Immediate.
  1. Click Advanced.
  2. Click Start Log Backup After Successful Backup.
  3. Click OK.

    You can track the progress of the job from the Job Controller.  When the backup has completed, the Job Controller displays Completed.  Note that the simultaneous running of file or file group backup jobs is not supported.

Run Transaction Log Backups without Running a Full Backup

Use the following steps to perform transaction log backups without having to run full backups first.

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server | <Instance>.
  2. Right-click a <Subclient> and click Properties.
  3. Click the SQL Settings tab and select Disable Log Consistency Check.
  4. Click OK.
  1. Click Backup Rules tab and clear Convert to disable the backup rules.
  2. Click OK.
  1. Run a full backup using SQL Management Studio (or any other application used to backup SQL Server).
  1. Run a transaction log backup using the CommCell Console.

Back Up Transaction Log of Damaged Database (Do Not Truncate Logs)

If you experience a database failure and you want to restore to the point of failure, a Transaction Log Backup with Do not truncate log must be initiated. This backups the database when it is damaged, regardless of its state.

It is used for capturing all transaction log events occurred since the last backup was run. This operation does not empty the active transaction log.

Use the following steps to disable log truncation during a backup:

  1. Ensure that the SQL Server database is in full or bulk-logged recovery mode.
  2. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server | <Instance>.
  3. Right click a <Subclient> and click Backup.
  4. Select Transaction Log as backup type.
  5. Select Do Not Truncate Log.
  6. Click Immediate.
  7. Click OK.

    You can track the progress of the job from the Job Controller.  When the backup has completed, the Job Controller displays Completed.

Tail Log Backup

When backing up transaction logs, you can choose to back up the tail of the log to capture the log records that have not yet been backed up. A tail-log backup prevents work loss and keeps the log chain intact. A tail-log backup allows you to recover a database to the point of failure; otherwise you can only recover a database to the end of the last backup that was created before the failure. For example, if a database was damaged or a data file was deleted, you should run a tail-log backup before attempting a file/file group restore. After the log tail is backed up, the database will be left in the RESTORING state.

Use the following step to backup the tail of a transaction log:

Ensure that the SQL Server database is in full or bulk-logged recovery model. To view or change the recovery model of a database:
  1. After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
  2. Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.
  3. Right-click the database, and then click Properties, which opens the Database Properties dialog box.
  4. In the Select a Page pane, click Options.
  5. The current recovery model is displayed in the Recovery model list box.
  6. Select either Full or Bulk-logged.
  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server | <Instance>.
  2. Right click a <Subclient> and click Backup.
  3. Select Transaction Log as backup type.
  4. Click Immediate.
  1. Click Advanced.
  2. Click Tail Log Backup.
  3. Click OK.

    You can track the progress of the job from the Job Controller.  When the backup has completed, the Job Controller displays Completed.  Note that the simultaneous running of file or file group backup jobs is not supported.

Configure Number of Log Backups before Running a Full Backup

Create the additional setting nLogThreshHoldValue to set the number of log backups that you want to run automatically before a full backup is run. If one log backup is corrupt, it invalidates all the log backups performed afterwards. So, you should run full backups at regular intervals as it reduces any chance of data loss.

After the set number of transaction log backups are run, a minor event is generated in the Simpana event viewer for reminding users to run a full backup.

Use the following steps to configure the number of log backups to run before a full backup:

  1. From the CommCell Browser, navigate to Client Computers.
  2. Right-click the <Client> and then click Properties.
  3. Click the Advanced button and select Additional Settings tab.
  4. Click Add.
  5. Click Lookup besides the Name field.
  6. In the Lookup Additional Settings dialog box search for the key by performing one of the following:

    A global parameter SQLiDALogThreshHoldCount can be used to configure the number of transaction log backups to run before a full backup (for all SQL subclients within the same CommCell). You can use the Command Line Interface to do so, see qcommand execscript for details.

  7. Enter a number in the Value field. Range is [1 - <max_integer>].
    This value specifies the number of transaction log backups that will be taken before a minor event is issued to remind users to run a full backup.
  8. Click OK.

Running Transaction Log Backups without a CommServe Connection

Run transaction log backups without a CommServe connection to reduce the CommServe load and to decrease the total number of jobs running simultaneously. To run such transaction log backups, you must create and then enable the following additional settings on the client computer.

Additional Settings Value Mandatory/Optional Description
SqlLogBackupStageFolder Path to a stage folder, such as C:\StageFolder Mandatory

(Only for standalone environment.)

Sets a path to the stage folder in a standalone environment.

Ensure that the stage folder has enough space to contain the backed up data. The data is automatically deleted after it is backed up to the MediaAgent. If needed, you can set SQL compression from the SQL Management Studio.

<clustername>StageFolder Path to a shared stage folder, such as O:\StageFolder Mandatory

(Only for clustered environment.)

Sets a path to the stage folder in a clustered environment. Ensure that the following conditions are true:
  • The shared path is accessible by all cluster computers.
  • Ensure that the stage folder has enough space to contain the backed up data.

The data is automatically deleted after it is backed up to the MediaAgent. If needed, you can set SQL compression from the SQL Management Studio.

bTLBackupToFS 1 Mandatory Enables the backup of transaction logs to the stage folder. By default, the backup runs every ten minutes.
backupFromFSForLog 1 Mandatory Enables the backup of transaction logs from the stage folder to the MediaAgent.
nSQLLogBackupInterval Number of minutes Optional Sets the frequency for transaction log backups to the stage folder. By default, the backup runs every ten minutes.
nDBperCollectFile 30 or greater Optional Sets the number of databases per collect file. A collect file contains those databases associated with the subclients configured in the CommCell Console.

Backup performance is slower when you run simultaneous transaction log backups on several databases. To speed performance, group 30 or more databases under each collect file and then run parallel transaction log backups on the databases under each collect file.

nAutoDiscoverSQLInstanceTimeout Number of minutes Optional Reduces the time interval for running an SQL instance discovery, if the databases are created or renamed frequently. By default, the SQL instance discovery runs every 24 hours.
DisableTlog_<InstanceName> 0, 1 Optional Enables and disables staging of transaction log backups without a CommServe connection for an instance.

Creating this setting overrides the Enable Transaction Log staging to disk check box.

SQLTimeout_<InstanceName> Number of minutes Optional Sets the connection timeout value for single SQL instance. Set the value as 0 for infinite timeout of SQL instance.
BackupInterval_<InstanceName> 10 or greater Optional Sets backup interval for an instance.
  1. From the CommCell Console, create the additional settings. For more information on creating the additional setting, see Add or Modify an Additional Setting.
  2. Install the service pack with updates for this feature.
  3. Restart the CVd services.
  4. Optional: From the Properties of SQL Server dialog box, select the Enable Transaction Log staging to disk check box.

    Note: Select this check box only if you have not created the additional setting DisableTlog_<InstanceName> in step 1.

  5. From the CommCell Console, schedule a transaction log backup at regular intervals.

  • In a clustered environment, set the additional settings on all the client computers in the cluster.
  • Database names with characters such as \/:*?"<>| cannot be backed up.

Disabling Transaction Log Backups without a CommServe Connection

If you set up your environment to run the transaction log backups without connecting to a CommServe, you can disable this feature and re-establish the connection.

  1. From the CommCell Console, disable bTLBackupToFS by setting its value as zero. For more information on modifying the additional setting, see Add or Modify an Additional Setting.
  2. Restart the CVd services.
  3. To collect the remaining logs from the stage folder, from the CommCell Console, run the transaction log backup.
  4. Disable backupFromFSForLog by setting its value as zero. For more information on modifying the additional setting, see Add or Modify an Additional Setting.

Differential Backups

A differential backup contains only the data that is new or has been changed since the last full backup. Differential backups consume less media and use less resources than full backups. Differential backups are cumulative. This means that each differential backup contains all changes accumulated since the last full backup. Each successive differential backup contains all the changes from the previous differential backup.

Use the following steps to run a differential backup:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server | <Instance>.
  2. Right-click a <Subclient> and click Backup.
  3. Select Differential as the backup type and click Immediate.

Copy-Only

A Copy-Only backup is an independent backup and does not affect the usual chaining involved in traditional backups. This type of backup can be performed for a special purpose and does not affect the overall backup and restore procedures. It applies to full backups, full Volume Shadow Services (VSS)-enabled backups, and full SnapProtect backups.

After taking a full Copy-Only backup, log backups cannot be run unless a traditional full backup is present. Differential backups can be run but they cannot be restored unless a traditional full backup exists prior to the differential.

Use the following steps to run a Copy-Only backup:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server | <Instance>.
  2. Right-click a <Subclient> and click Backup.
  3. Select Full as the backup type and click Immediate.
  4. Click the Advanced button.
  5. On the Data tab, select Copy-Only and click OK.
  6. Click OK.

Compressing Backups with Native SQL Compression (For SQL Server 2008 or Later)

Backups can be compressed before it is backed up to reduce the size of the backup. Typically, compressing a backup will require less device I/O which should increase backup speed significantly. However, CPU usage may increase for compressed backups and you may need to evaluate performance counters. Scheduling the backup during off-peak hours or compressing only low-priority backups may be desirable.

When using compression, there is no need for deduplication as the data will already be compressed and deduplication will not consequently save any more space.

Use the following steps to enable compression:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server | <Instance>.
  2. Right-click a <Subclient> and click Backup.
  3. Select a backup type and click Immediate.
  1. Click Advanced.
  2. Click Native SQL backup compression.
  3. Click OK.

    You can track the progress of the job from the Job Controller.  When the backup has completed, the Job Controller displays Completed. Note that the simultaneous running of file or file group backup jobs is not supported.

Perform a Partial Backup to Exclude Read-Only FileGroups

A partial backup contains the following:

  • All the data in the primary file group,
  • Every read/write filegroup,
  • Any optionally-specified read-only files

Partial backups are useful whenever you want to exclude read-only file groups. A partial backup is not supported when backing up transaction logs.

Use the following steps to enable partial backups:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server | <Instance>.
  2. Right-click a <Subclient> and click Backup.
  3. Select a backup type and click Immediate.
  1. Click Advanced.
  2. Click Partial (Excludes read-only Filegroups).
  3. Click OK.

    You can track the progress of the job from the Job Controller.  When the backup has completed, the Job Controller displays Completed.  Note that the simultaneous running of file or file group backup jobs is not supported.

Scheduling a Backup

Follow the steps given below to schedule a backup:

  1. From the CommCell Browser navigate to Client Computers | <Client> | SQL Server, and then click an <Instance>.
  2. Right-click  a <Subclient> and click Backup.

  3. From Backup Options Window, select the Backup type and:
    • Click Schedule to schedule the backup for a specific time.
    • Click Configure to set the schedule for the backup job. The Schedule Details dialog displays.
  4. Select the appropriate scheduling options.  For example:
    • Click Weekly.
    • Check the days you want the run the backup job.
    • Change the Start Time to 9:00 PM.
    • Click OK to close the Schedule Details dialog.
    • Click OK to close the Backup Options dialog.

    The backup job will execute as per the schedule.

Managing Jobs

Jobs can be managed in a number of ways. The following sections provide information on the different job management options available:

Restarting Jobs

Jobs that fail to complete successfully are automatically restarted based on the job restartability configuration set in the Control Panel. Keep in mind that changes made to this configuration will affect all jobs in the entire CommCell.

To Configure the job restartability for a specific job, you can modify the retry settings for the job. This will override the setting in the Control Panel. It is also possible to override the default CommServe configuration for individual jobs by configuring retry settings when initiating the job. This configuration, however, will apply only to the specific job.

Backup jobs for this Agent are resumed from the point-of-failure.

Configure Job Restartability at the CommServe Level

  1. From the CommCell Console toolbar, click Control Panel.
  2. Under the System section, click Job Management.
  3. Click Job Restarts tab and select a Job Type.
    • Select Restartable to make the job restartable.
    • You can change the value for Max Restarts to change the maximum number of times the Job Manager will try to restart a job.
    • You can change the value for Restart Interval (Mins) to change the time interval between attempts for the Job Manager to restart the job.
  4. Click OK.

Configure Job Restartability for an Individual Job

  1. From the CommCell Console, navigate to <Client> | SQL Server | <Instance>.
  2. Right-click the Subclient and select Backup
  1. Click Advanced.
  2. In the Advanced Backup Options dialog box, click the Job Retry tab.
  3. Select Enable Total Running Time and specify the maximum elapsed time before a job can be restarted or killed.

    Select Kill Running Jobs When Total Running Time Expires to kill the job after reaching the maximum elapsed time.

  4. Select Enable Number Of Retries and specify the number of retries.
  5. Click OK.

Controlling Jobs

The following controls are available for running jobs in the Job Controller window:

Suspend

Temporarily stops a job. A suspended job is not terminated; it can be restarted at a later time.

Resume

Resumes a job and returns the status to Waiting, Pending, Queued, or Running. The status depends on the availability of resources, the state of the Operation Windows, or the Activity Control setting.

Kill

Terminates a job.

Suspending a Job

  1. From the Job Controller of the CommCell Console, right-click the job and select Suspend.
  2. The job status may change to Suspend Pending for a few moments while the operation completes. The job status then changes to Suspended.

Resuming a Job

  1. From the Job Controller of the CommCell Console, right-click the job and select Resume.
  2. As the Job Manager attempts to restart the job, the job status changes to Waiting, Pending, or Running.

Killing a Job

  1. From the Job Controller of the CommCell Console, right-click the job and select Kill.
  2. Click Yes when the confirmation prompt appears if you are sure you want to kill the job. The job status may change to Kill Pending for a few moments while the operation completes. Once completed, the job status will change to Killed and it will be removed from the Job Controller window after five minutes.

Additional Options

The following table describes the available additional options to further refine your backup operations:

Option Description Related Topics
Startup Options

The Startup Options are used by the Job Manager to set priority for resource allocation. This is useful to give higher priority to certain jobs. You can set the priority as follows:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server | <Instance> .
  1. Right-click the Subclient in the right pane and click Backup.
  2. Click Advanced and click Startup tab.
  3. Select the Change Priority checkbox.
  4. Enter a priority number - 0 is the highest priority and 999 is the lowest priority.
  5. Select the Start up in suspended State check box to start the job in a suspended state.
  6. Click OK.
Refer to Job Priority and Priority Precedence. 
Alerts

This option enables users or user groups to get automatic notification on the status of the data protection job. Follow the steps given below to set up the criteria to raise notifications/alerts:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server | <Instance>.
  1. Right-click the Subclient in the right pane and click Backup.
  2. Click Advanced and select the Alert tab.
  3. Click Add Alert.
  4. From the Add Alert Wizard dialog box, select the required threshold and notification criteria and click Next.
  5. Select the required notification types and click Next.
  6. Select the recipients and click Next.
  7. Click Finish.
  8. Click OK.
Refer to Alerts and Notifications.
CommCell Readiness Report The CommCell Readiness Report provides you with vital information such as connectivity and readiness of the Client, MediaAgent, and CommServe. It is useful to run this report before performing a backup or a recovery operation. Follow the steps given below to generate the report:
  1. On the CommCell Console menu bar, select the Reports tab.
  2. Click Readiness.

    The Report Selection dialog box appears.

  3. If necessary, select Reports | CommServe | CommCell Readiness in the left pane.
  4. Select the Computers tab, and then click Modify below the Computers list.
  5. In the dialog box that appears, clear Include All Client Computers and All Client Groups.
  6. Select the appropriate client from the Exclude list, and then click Include >.
  7. Click OK.
  8. Select the MediaAgent tab, and then clear Include All MediaAgents.
  9. Select the appropriate MediaAgent from the Exclude list, and then click Include >.
  10. Click Run.

    The report appears in your default Web browser.

Refer to CommCell Readiness Report.
Backup Job Summary Report The Backup Job Summary Report provides you with information about all of the backup jobs that are run in last 24 hours for a specific subclient. You can get information such as status, time, data size, and so on for each backup job. It is useful to run this report after performing the backup. Follow the steps given below to generate the report:
  1. On the CommCell Console menu bar, select the Reports tab.
  2. Click Summary.

    The Report Selection dialog box appears.

  3. If necessary, select Reports | Jobs | Job Summary in the left pane.
  4. On the General tab, select Data Management.
  5. Select the Computers tab.
  6. Select Subclient, and then select the Edit sub tab.
  7. Navigate to Client Computers | <Client> | File System | Backup Set | <Subclient>.
  8. Click Run.

    The report appears in your default Web browser.

Refer to Backup Job Summary Report.
Data Path Options

Data Protection operations use a default Library, MediaAgent, Drive Pool, and Drive as the Data Path. You can use this option to change the data path if the default data path is not available. Follow the steps given below to change the default data path:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server | <Instance>.
  1. Right-click the Subclient in the right pane and click Backup.
  2. Click Advanced and select the Data Path tab.
  3. Select the MediaAgent and Library.
  4. Select the Drive Pool and Drive for tape library.
  5. Click OK.
Refer Change Data Path.
Start New Media

The Start New Media option enables you to start the data protection operation on a new media. This feature provides control over where the data physically resides. Use the following steps to start the data protection operation on a new media:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server | <Instance>.
  1. Right-click the Subclient in the right pane and click Backup.
  2. Click Advanced and select the Media tab.
  3. Select the Start New Media check box.
  4. Click OK.
Refer to Start New Media
Mark Media Full on Success

This option marks the media as full, two minutes after the successful completion of the data protection job. This option prevents another job from writing to this media. Follow the steps given below:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server | <Instance>.
  1. Right-click the Subclient in the right pane and click Backup.
  2. Click Advanced and select Media tab.
  3. Select the Mark Media Full on Success check box.
  4. Click OK.
Refer to Export Media.
Allow Other Schedules To Use Media Set

The Allow Other Schedules to use Media Set option allows jobs that are part of the schedule or schedule policy and using the specific storage policy to start a new media. It also prevents other jobs from writing to the same set of media.

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server | <Instance>.
  1. Right-click the Subclient in the right pane and click Backup.
  2. Click Advanced and select the Media tab.
  3. Select the Allow Other Schedules To Use Media Set check box.
  4. Click OK.
Refer to Creating an Exportable Media Set.
Extended Data Retention

This option allows you to extend the expiration date of a specific job. This will override the default retention set at the corresponding storage policy copy. Follow the steps given below to extend the expiration date:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server | <Instance>.
  1. Right-click the Subclient in the right pane and click Backup.
  2. Click Advanced and select the Media tab.
  3. Select one of the following options:
    • Infinite - Select this option to extend the expiration date by infinite number of days
    • Number of day - Select this option to specify the number of days to extend the expiration date and then enter the number of days.
  4. Click OK.
Refer to Extended Retention Rules.
Vault Tracker

This feature provides the facility to manage media that is removed from a library and stored in offsite locations. Depending on your Vault Tracker setup, select the required options. Use the following steps to access and select the Vault Tracker options.

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server | <Instance>.
  1. Right-click the Subclient in the right pane and click Backup.
  2. Click Advanced and select the Vault Tracking tab.
  3. Select the required options.
  4. Click OK.
Refer to Vault Tracker  or  Vault Tracker Enterprise.