Chat now with support
Chat with Support

LiteSpeed for SQL Server 8.5 - Configure Log Shipping Guide

Create Backup Templates

Create Backup Templates

A LiteSpeed backup template contains a set of backup parameters that describe the types of databases and the types of backups you want to perform. Using a backup template you define Full backup or Fast Compression backup schemes for your environment, with or without transaction log backups, and define the LiteSpeed compression and encryption options to use for the backup. When you deploy a template to a server instance, this creates a backup job or a maintenance plan that uses the parameters you specified in the template.

Notes: This feature is only supported for disk and cloud LiteSpeed backups.

Backup Templates can be easily updated and changes deployed to all instances. LiteSpeed tracks the instances where each template is deployed, making re-deployment very easy. Backup Templates can also be easily removed from an instance if they are no longer required.

Using LiteSpeed backup templates in your backup routine can help you manage multiple SQL Server instances. You do not have to manage backup jobs for each database or manage maintenance plans for one server at a time. Instead, create and deploy a backup template.

If your company's policy changes, you can quickly edit the template to comply with the new standards and re-deploy. LiteSpeed versions the templates and the deployments, so it is easy to see if an instance needs to be updated.

To create a Backup Template

  1. Select the Backup Manager pane (CTRL+1).

  2. In the Backup Templates tab, click New on the toolbar. If you do not use the central repository, click Backup Templates on the toolbar.

  3. Review the following additional information about the backup type and destination:

    Backup Type

    Select what backups will occur after you deploy this template on a server instance.

    • Full—Full backups and optionally transaction log backups.
    • Fast Compression—Full and Differential and optionally transaction log backups. Fast Compression automatically decides when to issue a Full or Differential backup based on the amount of database changes and some other conditions. For more information, see Fast Compression.

    The default backup name and description use the following information:

    • %D—Database name
    • %T—Backup type (Full, Diff or Log)
    • %z—Timestamp
    • %AG% — AlwaysOn Availability Group Name. This allows you to group databases into folders based on the AlwaysOn Availability Group name. It is ignored for databases that are not in an AlwaysOn Availability Group.

    You can specify custom backup name and description using both the LiteSpeed variables and text. For more information, see LiteSpeed Variables.

    Select Databases

    Select databases you want to back up.

    NOTES:

    • You can select the Custom Database Selection at Deployment option to specify individual databases for each instance at the deployment time. When you redeploy this template, you can see if a database was included in the previous deployment and change the database selection as needed. For more information, see Deploy Backup Templates.
    • If you use the Regular Expression option, then you are limited to deploying as a Maintenance Plan. Wildcards are supported by both Maintenance Plans and Jobs. For more information, see Use Wildcard and Regular Expressions in LiteSpeed.
    • Select to exclude Offline, LogShipping and ReadOnly databases from the backup. This applies to All databases, System databases, User databases and databases matching a wildcard expression or regular expression. In addition, databases can be excluded by name using the Deployment Wizard.
    • You can select whether to back up databases participating in AlwaysOn Availability Groups. For more information, see Back Up SQL Server 2012 AlwaysOn Availability Groups.
    Backup Destination

    Decide whether you want to save a default backup destination name in the template or specify it at the deployment time. If you specify a backup destination in a template, you can override backup destinations for each instance at deployment time. Each Backup Template remembers if you override these settings to make future redeployments easier.

    Backup file name as well as the backup folder can include the %SERVER% variable, that may be particularly useful when backing up many instances to the same network share.

    NOTE: Fast Compression handles the naming of files automatically. For more information, see Backup Files and Folders. Please refer to the LiteSpeed documentation for more information.

    You can either supply the backup destination at deployment time or save the default backup destination in the template. The backup destinations include disk and cloud. You can additionally select Cloud Accounts and Proxy Settings. For more information, see Cloud Account Settings.

    We have a few customers who would like to be able to create and deploy backup templates but require the previous backup be overwritten.

    In order to do this, when a Full backup template is created, we need to add an option (that defaults to Append as it is now) to allow for an Overwrite option (there is no need for the unique option we provide in the Backup Wizard).

    TIP: You can select the option to overwrite existing media when a full backup template is created.

  4. Set Backup options. Review the following for additional information:

    Optimize the Object Level Recovery speed

    Select to create an index of objects in the backup file. This option is only available for LiteSpeed backups. The default is enabled.

    NOTE: Before you can recover objects or execute a SELECT statement, you must read the backup file to create an index of restorable objects. The index is an .lsm file. During the backup process the .lsm file is created in the temp directory and attached to the backup file after the backup is completed.

    If you select this option, LiteSpeed uses the index in the backup file to read the backup file, which makes the object level recovery process much faster.

    Create Double Click Restore executable

    Select to create an executable backup file or a Double Click Restore Loader that allows you to restore a backup on a server instance that does not have LiteSpeed installed. For more information, see Double Click Restore Executables.

    NOTE: A Double Click Restore can only be created for a disk file.

    Perform checksum before writing to media

    Select to verify checksums when a backup is created.

    Additionally, you can control the response to an error. If you select the Continue on error option, the backup is executed despite encountering an invalid backup checksum.

    Continue on error

    Select this option to continue running the backup even if an invalid checksum is encountered.

    Review the following if you selected the Fast Compression backup type.

    Fast Compression Type

    Select whether you prefer to create a unique file for each backup or you prefer to manage a single file for each backup set (a backup set is composed of one full database backup plus all associated differential backups):

    • Separate backup files—(Default) Creates a unique file for each backup in the backup set. This option provides the convenience of having to move less data to tape or across the network when copying individual backup files. Using this option means that up to two physical files may be needed to restore the database (full backup plus the associated differential for the day in question).
    • Self-contained backup sets—Provides the convenience of only having to manage a single file per backup set. Only one file needs to be saved to or pulled from tape or copied from the backup location to a secondary location. If backing up more than one database, a file for each database will be created.

    The Self-Contained Backup Sets option automatically verifies the Full backup exists. The Separate Backup Files option performs the same validation by default.

    Note: For cloud backups only "Separate backup files" type is supported.

    Fast Compression Backup Options

    You can set the following thresholds to define when to issue a full backup:

    • Force a full backup every - The amount of time elapsed since the last full backup. The default is 14 days.
    • Data change threshold - The amount of database changes since the last full backup. The default is 35%.

    Fast Compression measures the amount of data change by either querying SQL Server or by comparing the size of the last differential to the last full backup. The default option is to query actual data pages. It provides the most accurate way to determine the amount of data change. If the query fails for any reason, Fast Compression will automatically run a size comparison to the last Differential backup.

    For example, set this parameter to 20%, and should the database change by 20% or more, Fast Compression will automatically run a Full backup. The larger the threshold, the larger the differential backups can grow before Fast Compression triggers the next Full backup.

    Regardless of how much underlying database data has changed, when exceeding the maximum interval (in days) between full backups, Fast Compression will force a full backup.

    NOTES:

    • Before a differential Fast Compression backup is available, the last full backup must have been created in the Fast Compression backup folder.
    • When backing up the master database as part of a Fast Compression maintenance plan or job, Fast Compression always executes a full backup.
    • The copy-only full backups cannot serve as a base for differential backups.

    Select the Extension for backup files checkbox to enter or change the backup file name extension. The default is set to bkp.

    NOTE: You can select the backup file extension for Fast Compression and make the new default, bak, for new items. For an existing item that does not have an extension defined, bkp is displayed when the item is edited (maintenance plans and templates).

    Backup Escalation

    This option causes LiteSpeed to issue a full backup, if one of the following problems is discovered in the current backup set:

    • The full backup is missing.
    • A differential backup is missing from the backup set (excludes backups automatically removed after the specified retention period).
    • LSN verification fails in the backup set.
    • Verify operation fails on full or differential backup.

    NOTE: If a problem is detected and a full backup is created through escalation, an error will be returned.

    Verification

    TIP: "Cleanup" means SmartCleanup. For more information, refer to SmartCleanup.

    Make sure the backup files in the backup set have integrity. This provides an added level of insurance the backup files can be restored. Verification failures appear in the LiteSpeed UI Console and, optionally, as job failure notifications. A verification failure after a differential backup will trigger the backup escalation process, if selected.

    The Verification options include:

    • Do not verify backup (default).
    • Verify last backup.
    • Verify both the last full and latest differential backup.
    • Verify the last full and all associated differential backups.

    The Cleanup options include:

    • Clean up full/differential backups older than 28 (default) days.
    • Clean up transaction logs older than 7 (default) days.

      Note: Clean up transaction log options are available if you set up a transaction log within the wizard (or template).

    • Do not delete if archive bit is set.
  5. Set Compression and Encryption options. Note that you can specify both the compression level and Adaptive Compression option. LiteSpeed will select and use Adaptive Compression if it is supported by the target server LiteSpeed version. Otherwise, the specified compression level will be used.

    Adaptive Compression

    LiteSpeed automatically selects the optimal compression based on throughput and CPU usage and optimize backups either for size or for speed (default).

    NOTE: Adaptive Compression is only available with LiteSpeed 6.5 or later; Enterprise license.

    Compression level

    Select 0 for no compression or 1-8 (default 2) to compress the file. For more information, see Compression Methods.

    NOTE: Higher compression levels result in smaller backup files, but they also take longer to complete. For assistance determining the best compression options, use the Backup Analyzer. For more information, see Test Optimal Backup Settings.

    Tip: For cloud backups, set the default compression level to 7. Using a higher compression level has real savings. Reducing the number of bytes sent to the cloud makes for faster backups and reduces Internet bandwidth.

     

    Encrypt backup

    Select this checkbox to encrypt the backup. Then, select the encryption level and enter the encryption password. For more information, see Encryption Methods.

    Review the following additional information about the advanced options:

    Compression threads

    Determines the number of threads used for the backup. You will achieve the best results by specifying multiple threads, but the exact value depends on several factors including: processors available, affinity setting, compression level, encryption settings, IO device speed, and SQL Server responsiveness. The default is n-1 threads, where n is the number of processors.

    Max transfer size

    Enter the maximum backup file size in bytes. . The possible values are multiples of 65536 bytes (64 KB) ranging up to 4,194,304 bytes (4 MB). The default is 1048576.

    Buffer count

    Enter the number of SQL Server buffers available for a LiteSpeed operation. The default is set by SQL Server.

    CPU throttle

    Enter the maximum percentage of CPU that LiteSpeed can use for the process. The default is 100.

    Processor affinity

    Click to select which processors LiteSpeed can use. The default is 0, which allows LiteSpeed to use all available system processors.

    Processor priority

    Select the priority of the backup over other transactions or processes running on the same server. The default is Normal.

    Comment

    User comment written into the backup header. Is blank by default.

    Logging level

    Select a logging level to define what events to log for the console. You can find the log events in the Application Event Log.

    Network resilience

    If LiteSpeed fails to write disk backups or reads from disk, it waits and retries the operation. You can enable and disable and control the number of times to retry and the amount of time to wait before retrying.

    • Number of times to retry any given read/write attempt—The default is 4 retries. The maximum allowed setting is 1000 retries.
    • Wait period before each retry attempt (in seconds)—The default period to wait before retry is 15 seconds The maximum allowed setting is 300 seconds.

    For more information, see Network Resilience.

    TIPS:

    • Before you start tuning the CPU Throttle or Affinity parameters to adjust backup performance, try limiting the number of threads. If you decide to use an affinity value other than default, it is recommended that you limit the threading as well.
    • You may also want to consider using Adaptive Compression to maintain backup performance. For more information, see Adaptive Compression.

    NOTE: LiteSpeed defaults typically result in the best performance. You should only modify advanced options after careful planning and testing. For more information, see Configure LiteSpeed Defaults.

  6. Configure notification options. You can select an existing notification profile or specify operators at deployment.

    TIP: Variables defined in the maintenance plan can be used to format the Subject Line. There is also help information inside the task that lists the available Subject Line variables.

    NOTE: The SQL Server Agent must be configured to send email using Database Mail. Review the following for additional information:

  7. For a Transaction Log backup you can nominate a backup folder independent of the backup folder for Fast Compression / Full backups. Do this at the T-Log Destination step. Further to this, the backup folder can be stipulated for individual instances using the Deployment Wizard.

  8. Specify backup cleanup options. For more information, see SmartCleanup.

    NOTE: The "Do not delete if Archive bit is set" option is available for full and fast compression backups in the Backup Template when it is deployed as a maintenance plan. If a backup template is created for a full backup with the option "Do not delete if Archive bit is set" selected, and the backup template is deployed as a job, the option is passed to the job's script to run the xp_slsSmartCleanup procedure. When the backup template is deployed as a maintenance plan, the option is omitted because the “Remove files older than” option (not the SmartCleanup option) for full backup is run in the maintenance plan.

  9. Complete the wizard.

Backup Templates are saved in the central repository (if one is used) and available for editing and deployment on the Backup Templates tab in the Backup Manager pane (CTRL+1). If the central repository is not configured, LiteSpeed will save each template as file.

In the Backup Templates tab, you can create, edit, clone, import, export and deploy templates, view the template contents, deployment details and modification history.

NOTES:

  • The Backup Templates tab is only available, if the central repository is configured and selected for use. To edit, deploy or remove a template when the central repository is not used, click beside Backup Templates on the toolbar and select the appropriate option.
  • The template deployment history is not exported when you export a template.
Related Documents