LiteSpeed for SQL Server 8.9.5 - User Guide

Use the Backup Template Wizard

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.

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 differential backups and 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 select Databases matching regular expression then you are limited to deploying as a Maintenance Plan. Selecting Databases matching wildcard expression is supported by both Maintenance Plans and Jobs. For more information, see Use Wildcard and Regular Expressions in LiteSpeed. You can exclude databases. Select to exclude Unavailable, LogShipping and ReadOnly databases from the backup. Optionally enter a AlwaysOn Availability Group wildcard mask, wildcard mask or regular expression for a database name; databases matching the wildcard mask or regular expression will be excluded. For more information, see Use Wildcard and Regular Expressions in LiteSpeed. TIP: In addition, databases can be excluded by name using the Deployment Wizard. TIP: Unavailable databases include databases with the following statuses: Offline, Restoring, In recovery, Not recovered, In standby, In load, Suspect, Detached, Emergency, Shutdown. You can select whether to back up databases participating in AlwaysOn Availability Groups. For more information, see Back Up SQL Server 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. You can either supply the backup destination at deployment time or save the default backup destination in the template. The backup destinations include disk, cloud, TSM Backup and TSM Archive. Cloud - You can additionally select Cloud Accounts and Proxy Settings. For more information, see Cloud Account Settings. TSM - By default LiteSpeed sets the option to "Use PASSWORDACCESS GENERATE from TSM configuration file".. You can additionally set TSM Backup and Archive options: client node, client owner password, configuration file, management class and the amount of time to wait for a device to become available. NOTE: The %TSMDEFAULTPATH% variable means that LiteSpeed will automatically detect the default TSM configuration file path. Use the Overwrite setting to overwrite existing media when a full backup template is created. Click Add Mirror to copy the entire backup file to multiple locations. Backup Templates support backup mirroring to disk or the cloud, same as for the backup of individual databases. For more information, see About Backing Up/Restoring Databases.

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 a Double Click Restore Loader that allows you to restore a backup on a server instance that does not have LiteSpeed installed. If you additionally select to Create one Double-Click Restore executable file then note the following warning. The executable may be greater than 4GB for large databases. Windows Server is unable to run executable files larger than 4GB. However, the file will be convertible/restorable by LiteSpeed file. 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.

   Click here 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 and Cleanup	TIP: "Cleanup" means Smart Cleanup Policies or Smart Cleanup. For more information, refer to Smart Cleanup Policies. 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: Ability to select Smart Cleanup Policies from previously defined list. Create new Smart Cleanup Policies. Set a list of locations where the policy will be applied to. TIP: Use the Ctrl button in Locations browse window to select several locations. 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:

   • http://msdn.microsoft.com/en-us/library/ms189635.aspx
   • http://technet.microsoft.com/en-us/sqlserver/dd939167.aspx

7. For a Differential backup ("Full" backup type)

   • On the Diff Schedule tab, tick Schedule differential backup and fill in the details of the schedule type and frequency. The Diff Destination and Diff Compression tabs are now added to the Backup Template Wizard.
   • On the Diff Destination tab set the filename format and default backup path.
   • On the Diff Compression tab use the same compression and encryption settings as for a full backup or customize using the available options.

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

   • You can set the behavior for databases without existing full backup (new databases): raise an error (default), raise a warning, suppress an error. Do this at the T-Log Destination step with the "Sequence check" option.

   • On the T-Log Schedule page you can skip Transaction log backups for databases being configured for Log Shipping. Click the Exclude LogShipping databases for transaction backups option. This option is available only when Exclude LogShipping is set to off on the Select Databases page. It allows you to run regular Full/Diff backups for Log Shipping databases and not brake the log shipping transaction log backup sequence.

9. Specify backup cleanup options. For more information, see Smart Cleanup Policies.

   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.

10. 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. For more information, see About Collecting Statistics in Repositories. 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 Topics

Deploy Backup Templates

Use the Deployment Wizard

NOTES:

• A LiteSpeed backup template can only be deployed on a server that has LiteSpeed installed.
• If the target server LiteSpeed version does not support Adaptive Compression, the created jobs and maintenance plans will instead use the specified compression level.
• Regular expressions are only supported in Maintenance Plans.

To deploy a LiteSpeed Backup Template with Deploy Wizard

1. Select the Backup Manager pane (CTRL+1).
2. In the Backup Templates tab, click Deploy on the toolbar. If you saved the template to file and the central repository is not selected for use, click beside Backup Templates on the toolbar, select the Deploy option and double-click the template file you want to deploy.

3. Complete the wizard.  Review the following for additional information:

   Select Instances	Use the Show selected instances only option to see server instances and databases where the selected template was deployed. NOTE: You can select individual databases only if the backup template is saved with the Custom Database Selection at Deployment option.
   Backup Options	Enter backup destinations for individual instances or for several instances at a time or use the default backup destination you specified in the template. Use the TLog Backup Location column to enter the Transaction Log backup folder for the given instance. Use the Diff Backup Location column to enter the Differential backup folder for the given instance. Use the Exclude Databases column to exclude individual databases from the backup. Use the TSM Settings column to customize TSM settings for the backup. You can change the scheduled time manually or you can right-click a cell in the Scheduled Time column and select one of the following options: Default to template—To change the current cell back to the template default. Default all to template—To change all cells back to the template default. Stagger by n minutes—To stagger all scheduled times by n minutes, starting at the template time and adding n minutes for an instance. Spread over n hour(s)—To spread the scheduled times evenly over an n hour range, starting with the template time. NOTE: You can only change the time for the instance. Each Backup Template remembers if you override these settings to make future redeployments easier. For example, if you specify a different scheduled time for an instance, it will retain the job/plan start time for the next deployments and will not change if you edit this setting in the template.
   Notifications	If you enabled notification in the template, review the Notification page and complete the fields as necessary. You can use the notification profiles only if they are already configured within a SQL Server instance. 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: http://msdn.microsoft.com/en-us/library/ms189635.aspx http://technet.microsoft.com/en-us/sqlserver/dd939167.aspx
   Deployment Type	Owner - Optionally, enter the owner of the SQL job (or Maintenance Plan) that will deploy the template. If the name you enter does not exist as a SQL user then the job will fail. If this field is left blank the job will run with the creator of the template or "running" user as the owner.

For each of the selected instances, the Deployment wizard will create either a SQL Agent job or a maintenance plan. Maintenance Plans names and subplans jobs names are prefixed with the template name. SQL Agent job names have the following format: LiteSpeed Backup Template <template_name> (version n).

Tip: To remove a deployed template, run the Deployment wizard, select the Remove deployed template option on the Select Template page and complete the wizard.

To deploy a LiteSpeed Backup Template with powershell script

Related Topic

Create Backup Templates

Back Up Using the Backup Wizard

The Backup Manager wizard guides you through the process of backing up a single database or multiple databases. For single database backups you can perform file, filegroup, or transaction log backups, and you can also mirror, stripe, compress, encrypt, and add attachments to the backup file. For multiple database backups you can create both the native SQL Server and LiteSpeed backups, mirror, compress and encrypt backups, run all backups immediately or schedule via a SQL Server job.

Tip: Database backups created with LiteSpeed Version 8.x cannot be restored using older versions of LiteSpeed.

NOTES:

• Running individual database backups or multi-database backups both use the same Backup wizard. When backing up multiple databases at the same time For more information, see Multi-Database Backup.

  Alternately, you can create a maintenance plan. For more information, see About Creating Maintenance Plans.

• If you select Fast Compression Backup, the wizard creates a Fast Compression job and does not create backup files immediately.
• When using this wizard to back up databases participating in AlwaysOn availability groups, LiteSpeed does not check whether the replica is preferred for backups. You can automate backing up of preferred replicas using the LiteSpeed Backup Templates feature. For more information, see Create Backup Templates.

• Fast Compression is not supported on secondary replicas in AlwaysOn Availability Groups. Backups must be performed on the primary because differential backups are not supported on secondaries.
• Performing full, file and filegroup backups on secondary replicas in an AlwaysOn Availability Group will always produce copy-only backups. Please refer to the SQL Server product documentation (SQL Server 2012 and above) for information about supported backup types.

• For information about full file backups, see http://msdn.microsoft.com/en-us/library/ms189860.aspx.

To back up multiple databases

1. In the Navigation pane, select a database to backup and click the Multi-Database Backup button. Alternately you can right-click a database and select Multi-Database Backup from the menu.
2. Select the databases for backup. For more information, see LiteSpeed's Logic for Backing Up Multiple Databases.
3. Complete the wizard.  For more information, see To back up databases using the Backup wizard. on backup options.

In case you select to schedule the backups to run at the specified times, the wizard creates a job with 'Multiple Databases' appended to the job name.

To back up databases using the Backup wizard

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

2. In the Navigation pane, select a database to backup and click the Backup button. Alternately you can right-click a database and select Backup from the menu.

3. On the Select Backup Type page, review the following:

   Recovery model	Indicates the recovery model for selected database. Possible values: Full, Simple, Bulk-Logged, Mixed (in Multi-Databases Wizard if databases with different recovery models are selected for backup).
   Backup type	Select one of the following backup types: Fast Compression backup. Using this option you can schedule 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. Regular backup (Full, Differential or Transaction Log). Scenario: Select the Fast Compression backup type.
   Backup component	Select one of the following backup components: NOTE: This field is only available for databases with a full or bulk-logged recovery model and for a regular (not fast compression) backup. Database - Select to backup the database. Files and filegroups - Select to backup specific files and filegroups. When selected, the Specify Filegroups and Files window is displayed. In the window you can select the entire database, or any combination of filegroups or individual files to backup.
   Use LiteSpeed	Select to create a LiteSpeed backup. If you clear this checkbox, LiteSpeed creates a native backup script. NOTE: The LiteSpeed version number is displayed.

   TIP: You can specify custom backup names and descriptions using variables. For more information, see LiteSpeed Variables.

4. On the Backup Destination page, review the following:

   Caution: The Cloud only runs backup to new files. Appending to an existing file is not supported by the Cloud. Existing backups can only be overwritten. The option "Overwrite existing object" is available in the Backup Wizard and MP (Back Up Database task). The option is turned off by default.

   Scenario: Select Disk and enter the existing or new location for Fast Compression backups:

   Backup to	Select one of the following options: Disk Cloud TSM Backup TSM Archive Tape
   Add	Click to add multiple backup destinations. The Select Backup Destination window is displayed. From here you can enter file names or backup devices.
   Remove	Click to remove backup destinations.
   Contents	Click to view the contents of the backup destination including general properties of file content and backup sets properties.
   Overwrite	Select one of the following options: TIP: This option is only available for regular backups (and not fast compression backups). Append to media—This option appends the backup set to existing media set; previous backups remain. Overwrite existing media—This option replaces previous backups in the existing media set with the current backup. Create unique media—This option generates a new media set; all previous backups are erased.
   Add Mirror	Click to copy the entire backup file to multiple locations. TIP: This option is only available with the Overwrite existing media option above. You can create multiple mirrors by clicking this button more than once. You can add mirrors to cloud platforms and different cloud platforms at the same time. After clicking the button once, the Mirror 1 tab is displayed. Click the Add button to display the Select mirror destination window. Enter the mirror destination. Choices are Disk and Cloud. If you selected Disk, click the ellipsis button to display backup locations. You can browse the network, add files, delete files, and rename files. If you selected Cloud then fill in the Cloud Account Settings. Review the Cloud Account Settings information. Click Cloud Account Settings for field descriptions.

   NOTES:

   • Mirroring is not the same as striping, which divides the backup file and stores the pieces at different destinations.
   • LiteSpeed automatically stripes the backup files if you include more than one backup destination.
   • You can mirror backups regardless of what SQL Server version you use, but you cannot mirror TSM or tape backups.

   • LiteSpeed backup mirroring behavior:

     Cloud (backup) + Cloud (mirror), Disk + Disk, Cloud + Disk

     • If the mirror failed, the primary will complete. The backup operation will be marked as completed.
     • If the primary failed, then both mirror and primary will fail. The backup operation will be marked as failed.

     Disk (backup) + Cloud (mirror)

     • If the mirror failed, the primary backup will fail. The backup operation will be marked as failed.
     • If the primary failed, then both mirror and primary will fail. The backup operation will be marked as failed.

   If you selected Cloud, review the Cloud Account Settings information. click Cloud Account Settings for field descriptions.

   If you selected TSM Backup or TSM Archive, review the following for additional information:click here for field descriptions.

   Client node	Enter the node name for the TSM session. This field is not case-sensitive.
   Client owner password	Enter the access password for the specified node.
   Configuration file	Select the configuration file. (Usually, dsm.opt.) NOTE: This file contains session options such as the TSM server's TCP address. If you select the Use PASSWORDACCESS GENERATE from TSM configuration file checkbox and your options file is configured to support this option, you do not need to specify the client node and client owner password.
   Management class	Select the management class (policy) to associate with the backup object being created. LiteSpeed will use the default management class, if this option is not selected.
   TSM Filespaces (Fast Compression backups)	Click TSM Filespaces and select the existing or enter new file space name(s). This step is optional, if you do not specify the file space name, Fast Compression will automatically create one. NOTE: Fast Compression handles the naming of files automatically. For more information, see Backup Files and Folders.
   TSM Object (Regular backups)	Click Select TSM Object. Enter the filespace and the high-level and low-level names and click Query TSM to pick the object name from the list of available TSM objects. From the Available TSM Objects list, double-click the objects you would like to select. NOTE: If you leave the High level and Low level fields blank, LiteSpeed will query all TSM server levels. Querying all levels may take longer to complete. For a new object, you can manually enter the full three-part name.
   Check for existing objects with same name (Regular backups)	Select this option to check for objects with the same name. LiteSpeed aborts the backup if it finds one.

5. If you selected the Fast Compression backup type, review the following:

   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.

6. On the Options page, review the following:

   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). Select the Enable backup escalation checkbox to maintain high recoverability. This option is enabled by default. 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. Select the Optimize Object Level Recovery speed checkbox to enable speed. The default is enabled. Select the Perform checksum before writing to media checkbox to verify backup data checksum before writing the backup data to the media. Select the Continue on error checkbox to continue running the backup even if an invalid checksum is encountered.

   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 a Double Click Restore Loader that allows you to restore a backup on a server instance that does not have LiteSpeed installed. If you select to Create one Double-Click Restore executable file then note the following warning. The executable may be greater than 4GB for large databases. Windows Server is unable to run executable files larger than 4GB. However, the file will be convertible/restorable by LiteSpeed file. 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.
   Select directories to mirror the backup to (Overwrite must be selected)	Click Disk to backup to disk. Click Cloud to backup to cloud.
   Select files/folders to attach to the backup set	Select Add to attach files or folders to the backup set.

   NOTE: Some options are only available with certain backup types.

7. On the Compression page, review the following:

   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.

8. On the Attached Files page, select Attach files and directories to store selected files with the backup. Click here for examples of when to use this feature.

   This feature lets you bundle items with the backup file that may be necessary to restore it. LiteSpeed appends the files to the data section of the backup file, and consequently the attached files are encrypted and compressed with the backup if those options are selected.

   Some examples of when to use this feature include the following:

   • Extended stored procedures packaged in DLLs may exist outside of the database, but you may need them to restore it. These external DLLs can be attached to the backup file.
   • If you are going to upgrade a database application, you can back up the database along with the current version of the application. If anything goes wrong during the upgrade, you can restore both the database and the related application files.
   • When sending a backup file offsite to another person (such as an auditor or consultant), you can attach schema diagrams, username and password lists, instructions, and other documentation.

   To retrieve the attachment, select Restore Attached Files and Directories on the Attach Files page of the Restore wizard. You can also use xp_restore_attachedfilesonly. For more information, see xp_restore_attachedfilesonly.

   TIP: To restore attached files only using the Restore Wizard, right-click a database in the server tree and select Restore | Attached Files...

9. On the Backup Schedule page, select Run immediately, Run in background, or Schedule. Selecting Schedule launches a page where you can fill out the schedule name, schedule type, occurs, daily frequency, duration, and description.

   If you selected Fast Compression the backup schedule has options to configure backups to run as a SQL Agent job at a scheduled time. The options are: daily at, custom, and do not run full backup on specified days of the week. If you select custom, you can add schedule name, schedule type, occurs, weekly, daily frequency, duration, and description.

10. On the Notification page, if you selected the Fast Compression backup type, you can configure the Fast Compression job to send notifications when any type of failure occurs. The failure might be due to a failed backup, a validation issue, like a missing Full or Differential backup, an LSN verification issue, or a failed Verify operation. Select one of the following:

    • Do not use notification - All failure notifications (including operator selections) are disabled.
    • Notify every time - Notify for all successes and failures. An operator can be selected and configured to receive notifications. Click the ellipsis button on the far right to create new or edit existing operators.
    • Failure only - Notify for failure only. An operator can be selected and configured to receive notifications. Click the ellipsis button on the far right to create new or edit existing operators.

    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:

    • http://msdn.microsoft.com/en-us/library/ms189635.aspx
    • http://technet.microsoft.com/en-us/sqlserver/dd939167.aspx
11. On the T-Log Schedule page, specify scheduling for the transaction log backup job execution. Select Schedule transaction log and fill out the new job schedule screen. Entries include schedule name, schedule type, occurs, daily frequency, duration and description.
12. On the Verification and Cleanup pages, specify the verification and cleanup options after each backup.

    TIP: "Cleanup" means Smart Cleanup Policies or Smart Cleanup. For more information, refer to Smart Cleanup Policies.
    

    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:

    • Ability to select Smart Cleanup Policies from previously defined list.
    • Create new Smart Cleanup Policies.
    • Set a list of locations where the policy will be applied to.

      TIP: Use the Ctrl button in Locations browse window to select several locations.

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

    Scenario: Specify when full/differential backups are eligible for cleanup, according to your company's retention policy. For more information, see Smart Cleanup Policies.

13. On the Fast Compression Job page, create the fast compression backup job script.

14. Complete the wizard.

    NOTE: If you selected to run the backup in the background, you can view the progress in the Background Tasks pane (View | Background Tasks). If you scheduled the backup, you can view and edit it from the LiteSpeed Jobs tab for the database.

Related Topics

Smart Cleanup Policies

Fast Compression

Restore Databases Using the Restore Wizard

Using the Cloud

Back Up Multiple Databases

Higher compression levels result in smaller backup files, but they also may consume additional CPU. If the server does not have sufficient CPU, then the backup may take longer to complete. The Backup Analyzer evaluates different settings, such as compression level, striping, and backup destinations, to help you determine optimal backup settings.

NOTE: When running the Backup Analyzer, follow these guidelines for the best results:
Minimum: 1 GB sample size.
Recommendation: 10 GB sample size. The combination of compression levels, encryption options, and backup locations should be considered. For example, if you sample 4 GB of data with 10 different tests, LiteSpeed will take the same amount of time as it takes to back up 40 GB. On very large databases, you can speed up the analysis by reducing the number of backup combinations or by reducing the amount of sample data.

Backup Analyzer Wizard

The Backup Analyzer wizard guides you through selecting the backup parameters to test. The wizard tests all of the different combinations of your selected parameters by backing up a portion of the database. It does not interfere with existing backup schedules or sets.

To run the Backup Analyzer wizard

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

2. Right-click the database and select Backup Analyzer Wizard.

   Tips:

   • To test striped backups, click Add to add several destinations and select the Test backup striping to selected backup locations checkbox on the Backup Location page.
   • If you add more than two destinations, select both checkboxes to test all destination combinations.
   • If you select a large number of backup parameters, you may want to schedule the tests to run at a specified time within the LiteSpeed Analyzer job.
3. Click Next.
4. On the Backup Location screen, select Backup to (Disk, Cloud, or TSM Backup) and Backup locations. Add or remove backups from the locations. Additionally select striping options.
5. Click Next.
6. On the Data Sample screen, enter the amount of data to sample in megabytes or the percentage of data to test.
   Note: Database space used value calculates as sum of "data" and "index_size" values returned by sp_spaceused stored procedure.
7. Click Next.
8. On the Compression Level screen, select the compression levels to test. Any combination of compression levels from 0 to 8 are available. You can also specify additional backup advanced options.

   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.
   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.
   TIP: 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.

   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.

9. Click Next.
10. On the Execute Script screen, the list of databases to test is displayed.
11. Click Next. The Backup Analyzer Wizard test scrip runs and displays the test results summary.
12. Complete the wizard.

NOTE: After the required number of bytes is received for analysis, the process is intentionally aborted. This generates the VDI error messages in the LiteSpeed log files and the SQL Server error log. Please ignore them. Click here to see the examples of the error messages.

• BackupMedium::ReportIoError: write failure on backup device 'VDI_B0F1C91D-201F-48DD-9D4B-5B16A1445D53_0'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).
• BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device 'VDI_B0F1C91D-201F-48DD-9D4B-5B16A1445D53_1'.
• BackupVirtualDeviceFile::ClearError: failure on backup device 'VDI_B4677313-6D48-4BC0-AC74-B3D0976E6077_1'.
• SQLVDI: Loc=TriggerAbort. Desc=invoked. ErrorCode=(0).
• SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0).

Scheduling Backup Analyzer

You can schedule analyzing from within the Backup Analyzer Wizard. An SQL Server job with the name "LiteSpeed Analyzer SERVERNAME.DATABASENAME" is created. View the list of Backup Analyzer jobs directly from the Backup Analyzer tab by clicking "Show Analyzer Jobs." Right-click on the job. You can change schedule, start/stop, disable or even delete the job. To view the script, select the "View in Job Manager" option and open the job properties from the Job Manager module. You can then go to the job step details.

Backup Analyzer Tab

The Backup Analyzer tab consists of the toolbar, graph, and grid. The Backup Analyzer toolbar is used to select the following items:

• Existing test - View the existing test or use the drop down to select other tests.
• Purge All - Click to remove all Backup Analyzer tests for the database.
• Show Analyzer Jobs - Click to view all Backup Analyzer jobs.
• New Test - Click to run the Backup Analyzer Wizard and begin a new test.
• - Click to export the grid to Microsoft Excel.
• - Click to print the grid.

The Backup Analyzer tab presents the test results in a graph and grid format:

• Graph—Displays the backup duration and compression amount for each test in a bar graph so you can easily compare the results between size and duration. If you hover the mouse over the bar graph, test number, size, and duration are displayed. If you select a test in the grid, LiteSpeed indicates the corresponding test in the bar graph with a yellow border around it. You can view previous tests by changing the Existing test field. 

• Grid—Displays the details of each test in columnar format. LiteSpeed indicates its recommendation with the number indicated first in the Test Number column and a yellow box around the test bar graph. Review the following for additional information:

  Tip: .Use the Grid horizontal scroll bar to view other table columns.

  Column	Description
  Test Number	The number assigned by the Backup Analyzer.
  Level	The compression level that is used for a particular database backup test.
  Encryption	Indicates enabled encryption for the database backup.
  Estimated Duration	The approximate time allotted to run the database backup.
  Estimated Backup Size (MB)	The approximate size of the backed up database.
  Database Size (MB)	The amount of disk space the database occupies.
  Compression (MB)	The amount of disk space you can save with this compression ratio.
  Compression (%)	Data compression ratio.
  Read Speed (MB/Sec)	Maximum read speed.
  Throughput (MB/Sec)	Actual speed of processing data (compression and encryption).
  Backup Speed (MB/Sec)	Virtual write speed of the backup. Compare this value to Throughput and Read Speed to determine if you are experiencing a write-bound issue.
  Write Speed (MB/Sec)	Actual write speed. It shows how quickly the compressed data stream is written to the destination disk.
  Stripes	Number of stripes associated with the backup.
  Destination	Backup destination directory location.
  Threads	Number of threads associated with the backup.

  You can double-click a row for more information about the test.

  Tip: For panes that have grids, you can sort, group, move, and remove the columns: To sort and group the records, right-click a column header and select the appropriate options. To sort records against multiple columns, click column headers while holding the SHIFT key. For example, to sort by type and then by name, click the Type column header and then SHIFT+click the Name column header. To add or remove columns, right-click a column header and select Column Chooser. Add a column by dragging it from the list into the column headers. Remove a column by dragging its column header into the list. To move a column, drag the column header to the new location.

Related Topics

Encryption Methods

Compression Methods