Back Up Databases Using Maintenance Plans
This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
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". |
To configure database backups
- Drag the Back Up Database task or the Fast Compression Backup task to the Design pane.
-
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.
Scenario: Select the Databases matching regular expression option, enter the following in the Mask field: ^(?!SoftwareCMSS)(C|Quest Software|LiteSpeed) and click Add. Use Wildcard and Regular Expressions in LiteSpeed
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, Azure Blob Google Storage or S3 Compatible Storage, 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 and S3 Compatible Storage only)Enter the name of the Web service secret key that was assigned when you initially setup your cloud account.
Service account ID (Google Cloud Storage only) Enter the name of your Google Cloud Storage Service Account ID. Private key (Google Cloud Storage only) Enter the name of your Google Cloud Storage Private Key. Project ID (Google Cloud Storage only) Enter the name of your Google Cloud Storage Project ID. 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 and Google Cloud Storage only)Select a Web service region to use for a bucket with the drop-down list. Endpoint
(Amazon S3 custom region and S3 Compatible Storage only)Specifies the endpoint for S3 Compatible Storage and custom endpoint for Amazon S3 service (in case of VPC). Format: servername:port . Example values: "servername", "servername:port", "ip:port", "10.0.0.200:9021". Bucket
(Amazon S3, and S3 Compatible Storage and Google Cloud Storage 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.
- Google Cloud Storage advanced options include Automatic Striping, Storage Class and Proxy Settings.
- S3 Compatible Storage advanced options include: use SSL and automatic striping,
- Use Manual PartSize option allows to set manual parameters for the following cloud options
If you selected TSM Backup or TSM Archive,
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) This step is optional. You can click TSM Filespaces to select the existing or enter new file space name(s).
NOTE: Fast Compression handles the naming of files automatically. For more information, see Backup Files and Folders.
TSM Objects (Regular backups)
Click TSM Objects and specify the filespace, the high-level name and the format of the low-level name.
If the option Stripe on TSM object and the number of stripes are selected, the mask %@ is added by default to the end of the defined file format.
If the option Stripe on TSM object and the number of stripes are selected, the mask %@ can be added by the user to any place of file format.
-
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.
Force a full backup if one has not been created Select this option to run a force full backup if one has not been created. This is a useful option for differential and transaction log backups that require an initial full backup at first. 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%.
- Data change calculation - Use the checkbox to select and query actual data pages changed since the last full backup or select compare the size of the last differential backup to the last full backup.
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.
- Differential backup fails for reasons other than out-of-disk .
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.
Specify the cleanup options. Smart Cleanup Policies
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.
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:
-
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.
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.
Review the following additional information about the advanced options:
Compression threads
Type: Integer
Determines the number of threads used for the backup
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.
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 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.