LiteSpeed for SQL Server 8.9.8 - User Guide

Logic for Backing Up Multiple Databases

LiteSpeed provides you with many flexible options for selecting databases to include in a backup job or in a backup task of a maintenance plan. If there are conflicting conditions (for example, you selected to back up databases, matching the 'DirectD*' pattern and skip read-only databases, while the name of a read-only database matches the pattern), the Exclude condition wins over Include.

NOTE: LiteSpeed wizards may have different Exclude/Include options for selecting databases.

Include Databases

You can specify databases:

• Manually

• Using one of the following built-in groups:

  • All databases
  • System databases
  • User databases

• Using wildcard or regular expressions. For more information, see Use Wildcard and Regular Expressions in LiteSpeed.

NOTE: New user databases will also be backed up as part of the "All databases" or "User databases" groups or if they match the specified pattern, unless they are excluded by an Exclude option. For more information, see Exclude Databases.

Depending on the LiteSpeed wizard, you cannot mix selection methods. Instead, you can schedule several subplans or jobs using different selection methods.

Exclude Databases

Using these options you can further define databases you want to back up.

You can exclude the following databases:

• Offline
• Selected—LiteSpeed will backup all included existing and new databases, except those you select manually and those excluded by a different Exclude option.
• Deleted
• Log Shipping
• Read Only

NOTE: Mirror databases and database snapshots are automatically excluded.

Back Up SQL Server AlwaysOn Availability Groups

Versions 7.0 and later of LiteSpeed provide enhanced support for databases participating in AlwaysOn availability groups (SQL Server 2012 and above). You can include availability databases in a maintenance plan or a backup job, as well as perform backups on individual replicas.

In maintenance plans and backup templates, the following options define which replicas are eligible for backup:

• Back up preferred—This is the default option. LiteSpeed will follow the logic defined for the availability group by the database administrator and back up databases accordingly, provided the jobs or maintenance plans are configured on every server instance that hosts an availability replica. The default AlwaysOn configuration is to back up a secondary replica only, unless the primary replica is the only replica online.
• Only back up the primary—The primary is backed up each time, regardless of AlwaysOn configuration.
• Back up all (primary and secondaries)— All primaries and secondaries are backed up each time, regardless of AlwaysOn configuration.
• Back up none—AlwaysOn databases are ignored during backup.

NOTES:

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

• In Maintenance Plans if you select the Differential backup type, all secondary replicas in an AlwaysOn Availability Group are automatically excluded. In the Multi-Database Backup Wizard they are not excluded and differential backup for secondary replicas will fail.
• In Maintenance Plans and in the Multi-Database Backup Wizard, if you select the Transaction log backup type and turn on the Copy Only option, backups for all secondary replicas will fail because transaction log backups with Copy Only are not supported on secondaries.

Related Topics

Create Backup Templates

About Creating Maintenance Plans

Multi-Database Backup

Use Wildcard and Regular Expressions in LiteSpeed

You can configure LiteSpeed to only back up databases that match the specified pattern. Using wildcard and regular expressions is particularly helpful when there are databases with similar names on the same instance or on multiple instances.

Tips:

• To back up databases with similar names on multiple instances, create and deploy a backup template, or create a maintenance plan on one instance and then simply copy it to other instances.
• LiteSpeed lets you enter multiple patterns. When selecting databases for your maintenance plan, you can click Validate selected or Validate all to display a list of databases names that match any of the specified patterns.

Wildcard Expressions

You can use wildcard characters '?' and '*' to select databases you want to back up. For example, "litespeed*" will include all databases that start with "litespeed" or "LiteSpeed".

Regular Expressions

Using regular expressions can provide you with more flexibility when selecting databases. For example, the following expressions will include a database if its name:

1. Begins with "LiteSpeed".

   ^LiteSpeed

2. Ends with "_PUBLISHER".

   _PUBLISHER$

3. Begins with "LiteSpeed" followed by digits.

   ^LiteSpeed\d+

4. Contains any number within the range 828500 to 828549.

   8285[0-4][0-9]

5. Begins with "LiteSpeed" or "Quest", except "QuestSoftwareCMSS".

   ^(?!QuestSoftwareCMSS)(Quest|LiteSpeed)

NOTES:

• Regular expressions are only supported in Maintenance Plans.
• LiteSpeed does not support regular expressions with the IgnoreCase option. To ignore case, use ([Aa][Bb][Cc]) instead of (?i:abc).

Click here for information about the regular expression constructs.

Review the following for additional information:

Related Topics

LiteSpeed's Logic for Backing Up Multiple Databases

Create Backup Templates

Back Up Databases Using Maintenance Plans

Multi-Database Backup Wizard

Multi-Database Backup launches the Backup wizard which allows you to configure backup options for several databases at once. With the Backup wizard, 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.

For each database, LiteSpeed generates uniquely named backup files to avoid collision.

NOTE: Fast Compression is now available for multi-database backup in the UI.

To back up multiple databases

1. Right-click the server instance and select Multi-Database Backup.... Alternately you can select the Multi-Database Backupbutton at the top.
2. Select 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 'Selected databases' appended to the job name.

Related Topic

About Creating Maintenance Plans

Create Backup Templates

Using the Cloud

Fast Compression

Fast Compression technology also known as SmartDiff in previous LiteSpeed versions allows you to maximize space savings and reduce backup time considerably over nightly full backup routines by intelligently backing up only database changes rather than the entire database. Fast Compression automatically chooses to create a full or differential backup based on user-defined full backup intervals and the percentage of the database that has changed. By only backing up database changes, users will see a significant reduction in backup storage and backup time.

NOTES:

• Fast Compression is not available for Tape and TSM Archive.
• Fast Compression is only available with LiteSpeed 5.1 or later; Enterprise license.

By default, Fast Compression selects the differential backup after first full, if the amount of changes is less than 35%.

The following table shows how much disk space can be saved for nightly Fast Compression backups for a 100 GB database that changes 5% daily and uses a 35% data change threshold.

Quick Start

To start do one of the following:

• Run the Backup wizard and select the Fast Compression backup type. For more information, see Back Up Databases.
• Create a maintenance plan with the Fast Compression Backup task. For more information, see Back Up Databases Using Maintenance Plans.

Tip: To create Fast Compression jobs or tasks on several instances, create and deploy a LiteSpeed backup template. For more information, see Create Backup Templates.

Backup Files and Folders

Fast Compression handles the naming of files automatically. Fast Compression backups have the following format:

• Disk backup: database_name.litespeed.f#[.d#][.s#][.m#].bkp
• TSM backup: file_space_name\database_name\litespeed.f#[.d#][.s#]

where:

• f# is full backup index
• d# is diff backup index (if used)
• s# is stripe index (if used)
• m# is mirror index (if used)

NOTE: All indexes start at zero.

Disk Backup Folders

Since the database name is incorporated into the backup name, you can safely select the same directory for all databases on an instance. If striping, you can select several directories. Also, you can add directories to mirror the entire backup file to multiple locations.

It is recommended that you create a new folder to use for Fast Compression backups. If you decide to back up to a folder that already has database backups, click here. Fast Compression performs some validations to see if a full backup already exists:

• If the last full backup was not performed by Fast Compression, but a valid, full backup exists in the designated Fast Compression folder, then Fast Compression uses it and begins the process with a differential backup, preventing the need to run an initial full backup on the database.
• If the last full backup was not performed by Fast Compression and that full backup is either missing or has an LSN verification issue, Fast Compression starts off by executing a full backup.
• If the last Full backup was performed by Fast Compression and is either missing or has an LSN verification issue, Fast Compression escalates to a full backup (if the escalate option is selected) or continues with a differential backup.

Disk Backup Files

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.

Full Backup Conditions

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

Additionally, you can prevent full backups from occurring on specified days of the week. If you select to exclude specific days of the week from Full backups and Fast Compression is set to execute the first time on an excluded day, assuming no full backup exists that can be used as described above, Fast Compression will not execute a full backup. This will continue until Fast Compression runs on a day that is not excluded.

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.

Full backup escalation is selected by default to maintain high recoverability level in the situations where recoverability may be limited (missing differential in set) or not available at all (missing full backup). This setting provides insurance against unanticipated errors. For example, if a backup file is missing from the backup set (someone accidentally deleted it), or there is some other type of issue like a Log Sequence Number (LSN) validation error or file corruption, you would not normally be able to restore the database. To correct for this potential issue with backups, Fast Compression automatically runs a full backup to put the database in a restorable state. Errors are still noted in the LiteSpeed UI Console and alerts will still be sent via the job.

If you uncheck this option and Fast Compression discovers an issue, you will have to correct the problem manually. If the physical file for the last full backup cannot be found, a differential backup may be executed successfully, but you will not be able to recover the database using these backups unless the correct full backup is located. Correction may require forcing a full backup using the @ForceFull parameter. xp_slsFastCompression Under normal operating conditions, you should not experience these types of issues as they are normally caused by accidental deletion of files or disk corruption, both of which occur very infrequently.

Backup Verification

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.

Cleanup

Cleanup provides a convenient way to remove old backups from disk without disrupting Fast Compression. Select this option to remove full and differential backup files and transaction log backups that are older than the specified time period.

The cleanup routine is backup set aware. This is important because the cleanup will never remove a full backup that is needed by a differential backup that is not being deleted. If you use the Separate Backup Files option in Fast Compression, you have the added flexibility of being able to remove differential backups from the active backup set that are no longer needed.

NOTE: Fast Compression does not raise errors if it detects a missing backup from a backup set that was removed via the cleanup process.

The backup retention will never delete:

• The backup files, if there are mixed backups in the same backup file. For example, if a user performs a backup of AdventureWorks and Pubs into the same mybackups.bak backup file.
• The full backup, if there are associated differential or t-log backups in the backup set that are not eligible for cleanup.

For more information, see Smart Cleanup Policies.

Backup Jobs

Completing the wizard will create the Fast Compression backup job. Using the Backup wizard, you can optionally schedule transaction log backups for the database. Transaction log backups are scheduled as a separate job from Fast Compression.

Related Topics

xp_slsFastCompression

Fast Compression Command-Line Arguments

Using the Cloud