LiteSpeed for SQL Server 8.6 - User Guide

About Backing Up/Restoring with LiteSpeed LiteSpeed User Interface Configure LiteSpeed for First Use Cloud Back Up Databases Automate Maintenance Tasks Restore Databases Restore Objects View Activity and History Use Command-Line Interface Use Extended Stored Procedures Troubleshoot LiteSpeed Review Additional Resources

About Creating Maintenance Plans

About Creating Maintenance Plans

Use the Design pane to create a workflow of database maintenance tasks. Tasks can execute independent of another task's status, or can be dependent on another task's completion before they can begin execution.

You can add additional subplans in a maintenance plan to group related tasks or to schedule tasks to execute at different times.

To create a maintenance plan

  1. Select Maintenance Plans in the Navigation pane (CTRL+4).
  2. Right-click a server instance and select Create New Maintenance Plan in the Maintenance Plan pane.
  3. Drag tasks to the Design pane to add them to the subplan. Double-click a task to specify its properties.

    Tip: If you need to create several similar plans, you can simply copy existing plans or subplans and then make the necessary edits. Copy Maintenance Plans

    Tip: For legacy maintenance plans only:

    • You cannot have more than one task of the same type in a subplan. To add a duplicate task, create a new subplan and add the task.  
    • You cannot have both a Fast Compression Backup task and Back Up Database task added to the same subplan.
    • Any tasks in the subplan must use the same list of databases. If you select a different database in a task, LiteSpeed prompts you to apply the database change to the entire subplan.
    Task Add this task to... Available for legacy plans

    Fast Compression Backup

    Perform full or differential backups using the Fast Compression technology.

    NOTE: Fast Compression is only available with LiteSpeed 5.1 or later; Enterprise license. 

    Back Up Databases Using Maintenance Plans

    With LiteSpeed only

    Back Up Database

    Perform full, differential, or transaction log backups; with or without encryption. You can back up databases on multiple servers by adding a separate backup task to the maintenance plan for each server.

    Back Up Databases Using Maintenance Plans

    Yes

    Check Database Integrity

    Validate the following:

    • Disk space allocation
    • Page and structural integrity for tables and indexed views
    • Catalog consistency
    • Contents of indexed views
    • Service Broker data

    Yes

    Reorganize Index

    Defragment and compact existing indexes to improve performance. With LiteSpeed only 

    Execute Job

    Execute an existing SQL Server Agent job.

    No

    Clean Up Maintenance Plans

    Remove obsolete backup files and reports created by a maintenance plan.  

    Clean Up Maintenance Plans

    With LiteSpeed only

    Clean Up History

    Remove historical data from the msdb database and LiteSpeed Local and Central repositories for the following:

    • Back up and restore history

    • SQL Server Agent jobs

    • Maintenance plans

    • LiteSpeed activity

      NOTE: LiteSpeed activity is removed from the Central repository, only if it is located on the same server, where the Local repository resides.

    • Any information for deleted databases

    • Log shipping history

    • Status history (Job, DTS, Maint Plans)

    With LiteSpeed only

    Notify Operator

    Send an email notification to one or more existing operators. 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:

    With LiteSpeed only

    Rebuild Index

    Drop and recreate an index to improve performance.

    Yes

    Shrink Database

    Reduce the size of data and log files in a database that grow beyond a specified size.

    Yes

    Execute T-SQL

    Execute statements or batches on one or more databases.

    NOTE: SSIS maintenance plans support only the Transact-SQL command type.

    Yes

    Update Statistics

    Update column and index statistics.

    Yes

  4. (SSIS Plans only) Right-click a task to execute first and select Add Constraint to draw a line to the dependent task.

    Tips:

    • You can add multiple constraints for a single task, and can execute those tasks concurrently or based on the outcome of the previous task.  
    • To use edit the constraint or use an expression to evaluate precedence, right-click the constraint line and select Edit.

    NOTE: Constraints are not available for legacy plans. Legacy plan tasks are executed in the following sequence:
    1. Clean Up History
    2. Check Database Integrity
    3. Rebuild Index
    4. Shrink Database
    5. Update Statistics
    6. Reorganize Index
    7. Fast Compression Backup or Back Up Database
    8. Clean Up Maintenance Plans
    9. Notify Operator
    10. Execute T-SQL

  5. Click to enter or select a schedule for executing the current subplan.
  6. Click and repeat steps 2, 3 and 4 for additional subplans you want to add.
  7. (Optional) Set reporting options.

    Reporting and Logging

    Click to set the reporting and logging options. Reporting and Logging in Maintenance Plans

    Notifications Click to notify an operator of job (subplan) status when a job fails, succeeds, or completes. 

Tips:

  • To manually execute, edit, or delete a maintenance plan, right-click the plan in the Maintenance Plan pane and select an option.
  • To change the plan owner, open the plan in the Designpane and select the owner from the drop-down list in the upper-right corner of the pane. Note that the plan owner is only responsible for creating and editing plans. The account that executes packages is the SQL Agent service account (or a proxy account). Privilege and Grant Requirements For more information, please refer to the "Privilege and Grant Requirements" section of the LiteSpeed Installation Guide.
  • To remove a subplan, select it and click on the toolbar in the maintenance plan designer.
  • To disable a subplan, double-click the subplan in the Design pane.
  • To define any connections you want to use for tasks in a maintenance plan, click . Once you add a connection, you can select it from the Connection drop-down list in any task. You can also define a connection at the task level that applies to other tasks and subplans. For legacy plans, you can add only one connection for remote logging and you must use Windows Authentication.

 

Related Topics

Back Up Databases

Back Up Databases Using Maintenance Plans

NOTE: Backup options may vary depending on the LiteSpeed and SQL Server version and plan type (legacy or SSIS; native or LiteSpeed). About Automating Maintenance Tasks

Scenario

You need to create a maintenance plan to only back up databases which names start with "C", "DB1" and "LiteSpeed", except "SoftwareCMSS". ClosedClick here to see additional information for this scenario.

To configure database backups

  1. Drag the Back Up Database task or the Fast Compression Backup task to the Design pane.
  2. Double-click the task and review the following for additional information:

    Databases

    Click to select databases you want to include in and exclude from the maintenance plan. LiteSpeed's Logic for Backing Up Multiple Databases

    Back up database across one or more files/destination

    This option creates one backup file for all selected databases. If you need to create striped backups, provide multiple backup destinations.

    Tip: You can backup databases to disk, cloud, TSM backup, TSM archive, or tape. Fast compression backups can be run to disk, cloud, or TSM backups.

    Create backup file for every database

    Select this option, if you want to create separate disk backups for databases.

    LiteSpeed file format

    The default backup file format uses the following information:

    • %D—Database name
    • %T—Backup type (Full, Diff or Log)
    • %Y-%m-%d-%H%M%S—Date and time
    • %EXT%—File extension

    You can specify a custom backup file format using both the LiteSpeed variables and text. LiteSpeed Variables

    If you want LiteSpeed to remember a custom file format and use it for new backup tasks on this instance, modify the format in the LiteSpeed file format field as needed and click Set to Instance.

    NOTE: Fast Compression handles the naming of files automatically. For more information, see Backup Files and Folders.

    If you selected Cloud review the following:

    Cloud vendor

    Select the cloud vendor, Amazon S3 or Azure Blob, from the drop-down list, or use to choose an existing cloud account. You can also specify a custom cloud account directly in the task.

    Storage Account name
    (Azure blob only)

    Enter the name of your Azure blob storage account.

    Access key

    Enter the name of the unique Web service alphanumeric access key that identifies each user.

    Secret key
    (Amazon S3 only)

    Enter the name of the Web service secret key that was assigned when you initially setup your cloud account.

    Storage type
    (Azure blob only)

    Select the Azure storage type: block blobs or page blobs from the drop-down list.

    Container
    (Azure blob only)

    Select the Azure blob storage container from the drop-down list.

    Region
    (Amazon S3 only)

    Select a Web service region to use for a bucket with the drop-down list.

    Bucket
    (Amazon S3 only)

    Enter the name of the container for objects. Bucket names must be at least 3 and no more than 63 characters long. Alternately use the drop-down list to select an existing bucket.

    Folder name

    Enter the name of your Cloud folder.

    Overwrite existing fields

    Click to overwrite existing fields.

    Advanced options

    Click to specify cloud advanced options.

    • Azure blob advanced options include: use SSL, Government account, automatic striping, and proxy settings.
    • Amazon S3 advanced options include: use SSL, GovCloud (US) region, automatic striping, storage class, use server side encryption (AES-256), and Use Amazon S3 Transfer Acceleration.

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

  3. Select the Options page.

    If you selected the Backup Database task, review the following:

    Backup set expires

    Select to set the transaction log expiration. The expiration can be set to expire after a selected amount of days or on a particular day. Select one of the following:

    • After - The backup set expires after a selected number of days.
    • On - The backup set expires on a specifically selected day.

    Remove files older than

    This option only removes files that match the file format from the specified destination folder.

    • Delete empty subfolder - Select this option to delete the empty subfolder.

    TIP: To remove obsolete files from different locations, use the Clean Up task. Clean Up Maintenance Plans

    Verify backup when finished

    Select this option to verify that LiteSpeed successfully wrote all backup files and can read them.

    Set native backup compression

    Select one of the following options to manage whether to use SQL server native compression (available for native maintenance plans only):

    • Use the default server setting

    • Compress backup

    • Do not compress backup

    Copy Only Backup

    Select this option to enable copy-only backups.

    If you selected the Fast Compression Backup task, review the following:

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

    Specify the cleanup options. SmartCleanup

    NOTE: While transaction log backups can be automated within a separate Back Up Database task, you can configure cleanup of transaction log backups in the Fast Compression Backup task.

    Notification

    Send an email notification to one or more existing operators. 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:

  4. Select the LiteSpeed page. Review the following additional information about the encryption and compression options:

    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.

    Review the following additional information about the verification and recovery options:

    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.

    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.

 

 

Related Topics

Clean Up Maintenance Data

Clean Up Maintenance Plans

Use this task to remove obsolete backup files and maintenance plan reports.

To clean up maintenance plan data

  1. Drag the Clean Up Maintenance Plans task to the Design pane.

  2. Double-click the task and review the following for additional information:

    Delete files of the following types

    Select the type of files to clean up:

    • Backup files (disk and cloud)
    • Maintenance plan text reports
    • Any files
    Delete specific file Select this option to delete a specific file. Enter the file name or select to browse for the file.
    Search folder and delete files based on extension Select this option to remove any files with a specific file extension. Enter the folder name or select to browse for the folder.
    File extension Enter the extension of the files you want to remove.
    Include all subfolders

    Select this option to include all subfolders when searching for files to remove.

    • Delete empty subfolder - Select this option to delete the empty subfolder.
    Delete files based on the age of the file at task run time. Delete files older than the following:

    Select this option to automatically delete files based on their age in number of minutes, hours, days, weeks, months, and years.

 

Copy Maintenance Plans

Copy Maintenance Plans

To set up similar plans, you do not necessarily have to start from scratch. Create a plan and copy it to any server instances where you want the plan to run.

NOTE: Copying and importing plans and subplans between servers may require additional manual steps, if the source and target servers have:

  • Different SQL Server versions—You may need to review and edit the selected databases list. Back Up Databases Using Maintenance Plans

  • Different LiteSpeed versions—Some options may be lost if they are not supported by the previous LiteSpeed version.
  • Different LiteSpeed versions and different SQL Server versions—Back Up Databases Using Maintenance Plans

    - LiteSpeed versions on the source and the target are different, but database lists are the same.

    - LiteSpeed version is the same but the database list is different (warning indicates that some databases do not exist).

    - LiteSpeed versions and database lists are different.

To copy a maintenance plan

  1. In the Server tree, right-click a maintenance plan you want to copy and select Copy Maintenance Plan.
  2. Right-click the instance where you want to paste the plan and select Paste Maintenance Plan.

To copy a subplan

  1. In the Design pane, select a subplan and click to copy.
  2. Open a new or existing maintenance plan in the Design pane.
  3. (Optional) Click on the Design pane toolbar to add a new subplan.
  4. Select a subplan and click to paste.

To export or import a maintenance plan

  1. To export a plan, select a maintenance plan you want to export and click the Export Plan button on the toolbar. NOTE: The file extension is .mpp.
  2. To import a plan, right-click the SQL Server instance from where you want to import the plan and select Import Maintenance Plan.
  3. Select the .mpp file that relates to the maintenance plan you want to import.

To export or import a subplan

  1. To export a sublan, in the Design pane select a subplan you want to export and click . NOTE: The file extension is .mps.
  2. To import a subplan, open a new or existing maintenance plan in the Design pane.
  3. This step is optional. Click on the Design pane toolbar to add a new subplan.
  4. Select a subplan and click .
  5. Select the .mps file that relates to the subplan you want to import.

  

Related Topics

Related Documents

Please note our Privacy Policy recently changed to support GDPR. You may read it here. Continuing to use our website indicates you have accepted the new policy.