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

Automate Similar Backup Tasks on Multiple Instances

Scenario

You need to schedule full, differential and t-log database backups on several servers and automate backup cleanup according to your company’s retention policy.

You are going to create a maintenance plan on one SQL Server instance and then simply copy it to the other server instances.

To automate backups on server instances

  1. Select Maintenance Plans in the Navigation pane (CTRL+4).
  2. In the Server tree, right-click an instance and select Create New Maintenance Plan.
  3. Drag and drop the Back Up Database task in the middle of the Design pane and double-click it.
    1. Select the Full backup type.
    2. Click and select User databases (excluding master, model, msdb).

      NOTE: This scenario describes how to back up all user databases. Similarly, you can configure maintenance plans to back up system databases and databases matching wildcard or regular expressions.

    1. Select the Create backup file for every database option and specify the Destination folder and file extension for the backups.
    2. Select the Options tab and select the Remove files older than option. Specify when the full backups are eligible for cleanup according to your company’s retention policy.
    3. Click Ok to save the task.
  4. Click on the tool bar. The task you just created will serve as a base for differential and t-log backups.
  5. Create two new subplans. To create a subplan, click , then Ok.
  6. Select each of the two new subplans and click to paste.
  7. Configure the copied subplan tasks to create the differential and t-log backups instead of full backups. Specify when the differential and t-log backups are eligible for cleanup according to your company’s retention policy.
  8. Double-click every subplan in the plan. Enter the name and description. Click to set schedule properties. Each subplan should have its own reoccurring schedule. For example,

    • Full backups occurring every week
    • Differential backups occurring every day
    • T-log backups occurring every 20 minutes

    When you are done editing the subplan properties, select Enabled and click Ok.

  9. Save the plan, click Copy Plan.
  10. For all instances where you want to paste the plan, right-click the instance and select Paste Maintenance Plan.

 

Related Topics

Back Up Databases Using Maintenance Plans

Copy Maintenance Plans

Restore Databases

Restore Databases Using the Restore Wizard

The Restore wizard guides you through the process of restoring a database (full or differential), transaction log, files, or filegroups.

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

NOTES:

Scenario

You need to restore a LiteSpeed disk backup to a new database on another SQL Server. Copy the backup files needed for restore to another server and run the Restore wizard. ClosedClick here to see additional information for this scenario.

To run the Restore wizard

  1. Select the Backup Manager pane (CTRL+1).
  2. In the Navigation pane, select a database to restore and click the Restore or Automated Restore button. Alternately you can right-click a database and select Restore or Automated Restore from the menu. If you clicked Restore, click one of the following. If you clicked Automated Restore, review the automated restore information below.

    • Database (full or differential)—Restore database only.
    • Files and Filegroups—Restore files and filegroups only.
    • Transaction Log—Restore transaction log only.

      Tip: The database (full or differential), files and filegroups, and transaction log restore types use the restore wizard pages listed in the steps below: restore destination, backup source, backup content, recovery options, data files, scheduled restore, execute script, and finish.

    • Attached Files—Restore attached files only.

      Tip: The attached files type uses the restore wizard pages listed in the steps below: restore destination, backup source, backup content, data files, attached files, scheduled restore, execute script, and finish.

    • Automated Restore—Restore the most recent full backup and optionally differential and transaction log backups. If you select this option, the Restore wizard creates an Automated Restore job. Backup files can be restored immediately or restored in the background.

      Tip: The automated restore type uses the restore wizard pages listed in the steps below: restore destination, backup source, backup files, restore options, database integrity database files, scheduled restore, notification, execute script, and finish.

  1. Review information on the Restore Destination page. Select the Kill all current connections before restore checkbox to obtain exclusive access to the selected database. Additionally, you can select the View current activity link to view the database connection activity table. A database cannot be restored unless the restore process has exclusive access to the database. No user connections can exist when performing a database restore.

    Caution: Automated Restore for multiple databases - When running restores for multiple databases, we recommend that you use the %DATABASENAME% variable to automatically generate an original database name for database(s) going to be restored. When running restores from multiple servers, different databases with the same server name can be overwritten. To prevent this, the variable %SOURCESERVER% adds the source server name (server+instance) to the target database name when running multiple automated restores. For example: %SOURCESERVER%_%DATABASENAME%.

  2. On the Backup Source page, select Database to restore from a specific database's backup history, or select Device to manually select files to restore.

    Database

    If you select Database, the database you are restoring is displayed. You can select another database using the Database drop down selector. Use the SQL Server drop down selector if the database is on a different SQL Server instance.

    TIP: The restore type option Restore Verify Only in combination with the backup type Verify the latest full or differential backup is less time consuming for full and differential backups or fast compression backups where you only want to verify the latest backup, rather than the full and the latest differential. For backups that are run every day this will serve to verify all backups.

    Device

    If you select Device, the options are Disk, Cloud, TSM Backup, TSM Archive, and Tape.

    If you selected Cloud, review the Cloud Account Settings information below:

    Cloud account

    Select the Cloud account from the drop-down list.

    Select the following items to add or edit the registered cloud account settings.

    • Add - (For Amazon S3 only), click to add cloud vendor, display name, authentication, region, storage class (standard, infrequent access, reduced redundancy storage), and bucket. Select: use SSL, use server side encryption (AES-256), GovCloud (US) Region, and automatic striping (auto, 10, 25, 50, 100, 500, 1000, 1995) GB. Select 'Use Amazon S3 Transfer Acceleration Speed' to use Amazon's S3 Transfer Acceleration feature which allows for increased upload speed to S3 storage up to 200% in some cases by using local CloudFront endpoints.
    • Add - (For Azure Blob only), click to add cloud vendor, display name, storage account name, access key, storage type (block blobs or page blobs), container, use SSL, government account, and automatic striping . Options for block blobs are: auto, 10, 25, 50, 100, 250, 500, 1000, 2000, 3000, 4000 and 4300 GB. Options for page blobs are: auto, 10, 25, 50, 100, 250, 500, and 995 GB.
    • Edit - (For Amazon S3 only), click to edit display name, authentication, region, storage class (standard, infrequent access, reduced redundancy storage), and bucket. Bucket name must conform to DNS naming requirements and must not contain periods ("."). Select: use SSL, use server side encryption (AES-256), GovCloud (US) Region, and automatic striping. Options for automatic striping are: auto, 10, 25, 50, 100, 250, 500, 1000, and 1995 GB.

      Select 'Use Amazon S3 Transfer Acceleration' to use Amazon's S3 Transfer Acceleration feature which allows for increased upload speed to S3 storage up to 200% in some cases by using local CloudFront endpoints. Additional data transfer charges may apply. See Amazon S3 pricing for more details.

    • Edit - ( For Azure Blob only), click to edit display name, access key, storage type (block blobs or page blobs), container, use SSL, government account, and automatic striping. Options for block blobs are: auto, 10, 25, 50, 100, 250, 500, 1000, 2000, 3000, 4000 and 4300 GB. Options for page blobs are: auto, 10, 25, 50, 100, 250, 500, and 995 GB.
    • Delete - Click to delete the Cloud account from the console.
    • Import - Click to import a saved Cloud account in XML format.
    • Export - Click to export and save a Cloud account in XML format.

    Proxy Settings

    Select the following items to edit the Cloud account proxy settings.

    • Use LiteSpeed Server proxy settings - Click to use the server proxy setting. This is the default selection. You can also edit the proxy settings from this item.
    • Use LiteSpeed Console proxy settings - Click to use the console proxy setting. You can also edit the proxy settings from this item.
    • Specify custom proxy settings - Click to add your own custom proxy address, port, username and password.

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

    If you selected Automated Restore, review the following for additional information.

    Restore from

    Select SQL Server and databases to specify the source from where to search the backups for automated restore.

    Tip: Use the drop-down treeview to add multiple sources from the System and User parent nodes. Select User databases and all subordinate user databases are automatically selected. Individually select and deselect databases using the checkbox next to it.

    For AlwaysOn availability groups, you may need to specify both SQL Server (primary and secondary) to allow LiteSpeed to search backups among all replicas. To specify a secondary SQL Server, use the semicolumn as a separator between SQL Servers (e.g. SQL-PROD\AG1; SQL-PROD\AG2) in the Source list (Server column).

    Latest backup search method

    Select one of the following options:

    • Folder scan—LiteSpeed will search the specified folder for the most recent database backups. You can configure LiteSpeed to search subfolders and filter backups using the specified file extensions.

    • Specific backup file name—You can use this option to automate restore operations if the same file gets overwritten during the backup or if new backups are always appended to the same file.

    NOTES:

    • If restoring a striped backup, you can specify multiple locations/filenames.
    • You can enter several backup extensions per path. Separate them with commas or semicolons.
    Backup type

    Specify backup types to use for the restore. Select one of the following options:

    • Full—The most recent full database backup.
    • Full and differential—The most recent full database backup and any existing differential backups based on this full.
    • Full, differential and transaction logs—The most recent full database backup and any existing differential and/or transaction log backups created after the most recent full backup.
    • Include copy only backups—Select the Include copy only backups checkbox to add copy only backups in the restore.
  3. If you selected Automated Restore, on the Backup Files page, select the backup file locations to search.

    Restore from

    Select the restore from location (disk or cloud) using the drop down menu.

    Add

    Add the backup file location to restore to the list by selecting and using the Backup File Location wizard.

    Remove

    Select and remove the backup file location from the list.

  4. If you selected Automated Restore, on the Database Integrity, define the options to check database integrity after restore.

    Check database integrity after restore (DBCC_CHECKDB)

    Use this option to run a CHECKDB on the restored database and report the results to the repository for review. This option is selected by default. Select a combination of the following database integrity options:

    • Check physical structure only (PHYSICAL_ONLY).This option is selected by default.
    • Check the database for column values that are not valid or out-of-range (DATA_PURITY).
    • Perform logical consistency checks on indexed views. XML indexes and spatial indexes (EXTENDED_LOGICAL_CHECKS).
    • Do not perform intensive checks of nonclustered indexes for user tables. This option is selected by default.
    • Use locks instead of using an internal database snapshot.
    • Do not include informational messages in notification report (NO_INFOMSGS). This option is selected by default.
  5. If you selected Automated Restore, on the Restore Options page, specify options for automated restore.

    Drop databases after restore

    Use this option if you no longer need the restored database. For example, if you are only restoring the latest backup for testing purposes. This option contains two additional options to select. One or both options can be selected.

    • On success restore and check database integrity operations - The database is dropped after a successful restore and database integrity check.
    • On failure any of restore or check databases integrity operations - The database is dropped after failing the restore or database integrity check.
    Overwrite the existing database Use this option if you want to overwrite the existing database with the restored database.
    Include databases that are part of a replication plan

    Use this option to include databases that are part of a replication plan.

    Recovery state

    The options are as follows:

    • Select to leave the database in an operational state (RESTORE WITH RECOVERY). The default is selected.
    • Select to leave the database in a non-operational state and allow restoration of additional transaction logs (RESTORE WITH NORECOVERY). The default is not selected.
    Password

    Provide a password for encrypted backups.

    NOTE: Automated Restore requires that you use the same password for all encrypted backups.

  6. Review the following additional information about the Backup Content page. Skip this page for Automated Restore.

    Select server instance Select the server that contains the backup you want to restore.
    Select database backup Select the database that you want to restore.
    First backup to recover
    1. Click the ellipsis to launch a window containing a list of backups to restore.
    1. Scroll down the list and select a backup. The backup to recover table is populated with a list of backups. The table includes backup name, type, destination, encrypted, server, database, position, begin date, finish date, size, user, expiration, and copy only. See the partial table below.
    • indicates a successful full backup.
    • indicates a successful differential backup.
    • indicates a successful transaction log backup.
    • indicates a warning. Possible reasons: encrypted backup that requires entering an encryption key, backup no longer exists, or corrupted backup.
    • Red text indicates the backup is not available. This could be because the backup was local to the source database and not available from the target or because the backup does not exist.
    • indicates a selected backup.

    IntelliRestore

    Select to have LiteSpeed automatically select the backups needed to restore the database successfully.

    Verify backups

    Select to verify the backup file integrity before completing the wizard.

  7. On the Recovery Options page, select the database recovery state following the restore. Skip this page for Automated Restore.

    Overwrite the existing database Select to have the current database overwritten with the restored database. The default is not selected.
    Preserve the replication settings Select to preserve the replication settings for the restored database. The default is not selected.
    Restrict access to the restored database

    Select to restrict access to the database after it is restored. The default is not selected.

    Recovery state

    The options are as follows:

    • Select to leave the database in an operational state (RESTORE WITH RECOVERY). The default is selected.
    • Select to leave the database in a non-operational state and allow restoration of additional transaction logs (RESTORE WITH NORECOVERY). The default is not selected.
    • Select to leave the database in read-only mode. Undo committed transactions and save the undo actions in a standby file (RECOVER WITH STANDBY). The default is not selected. When selected, the default listed standby file can be used. Or select the ellipsis button to browse and select another standby file.
  8. Review the following additional information about the Data Files page:

    Prompt before restoring each backup

    Select this option if you would like to receive a prompt notification before restoring each backup. The default is not selected.

    Eject tapes (if any) after restoring each backup Select this option if you would like to eject any tapes after restoring each backup. The default is not selected. This option is available only for tapes.
    Restore as compressed, read-only database

    Using this option, you can restore a user database into an NTFS compressed folder or restore a tlog to a read-only database in a compressed folder.

    NOTES:

    • When using an NTFS-compressed folder for a database, it can only be restored as read-only.
    • You can only use this feature on Windows NTFS file systems.

    Specify a compressed folder for the data files by editing the Restore As paths. If a folder does not exist, LiteSpeed will create it as NTFS compressed.

    Restore the database files as

    Although you can manually enter DATA and LOG locations, including secondary data files locations, it is recommended that you use locations generated by LiteSpeed.

    If restoring database (full or differential), the following links can be selected (not available for Automated Restore):

    • Keep original database (full or differential)—Click to display in the table below the original database locations.
    • Use SQL Server instance default locations—Click to display in the table below the SQL Server instance default locations.
    • Select new location—Click to launch the Database Files Destination window and select a new database output file location. You can browse the network, add, delete, and rename files.
    • Restore to locations from backup set—Click to get locations from the backup set.

    If the source and target locations do not match or if they are set to other than the default, LiteSpeed you can select one of the following options in Automated Restore:

    • Use SQL Server instance default locations—To use DATA and LOG directories of the existing database you are restoring the backup to. The default is selected.
    • Custom locations for data and log files—To use DATA and LOG directories of the database which backup you are restoring. The default is not selected.
    • Folder for data files—To enter a new location for all DATA files. Click the ellipsis button to browse and select other folders for data files.
    • Folders for log files—To enter a new location for all LOG files. Click the ellipsis button to browse and select other folders for log files.
    Processor affinity

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

    Logging level

    Select one of the following options:

    • None—LiteSpeed does not write a log file for the backup or restore operation.

    • Verbose—LiteSpeed writes a log file for the backup or restore operation.

    • Verbose. Log file is removed on success—LiteSpeed only saves log files if the backup or restore operation fails. If it succeeds, LiteSpeed does not save the 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.

  9. If you added an attachment to the backup file, select the Restore Attached Files and Directories on the Attached Files page.

  10. Review the following information about the Schedule Restore page. Select Weekly on (for Automated Restore only), Run immediately, Run in background, or Schedule (Custom Schedule for Automated Restore. Selecting Schedule launches a page for adding the schedule name, schedule type, occurs, weekly, daily frequency, duration, and description.
  11. Review the following information about the Notification page (for Automated Restore only). You can specify the notification of failure options that are sent after each restore. 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.

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

  12. Complete the wizard.

 

Related Topics

Restore Double-Click Restore Executables

Restore Double Click Restore Executables

To restore a Double Click Restore executable, do one of the following:

  • Double-click the Double Click Restore executable and complete the LiteSpeed Double Click Restore dialog.

  • Run the command line, change the directory until you are in the directory containing the Double Click Restore executable and run the following:

    backup.exe -R database -F backup_file -W replace

    where

    backup.exe is the name of the Double Click Restore executable.

    backup_file specifies the path to the file containing backup data. You can supply multiple instances of this argument. Use this argument to list all backup files except the executable being run:

    • The filename of the backup if there is a Double Click Restore loader created for this backup
    • The filenames of any other stripes that were not converted to an executable

    NOTE: The syntax is exactly the same as that for sqllitespeed.exe. For more information, see LiteSpeed Command-Line Arguments.

  • Restore as any other backup using the Restore Wizard, command-line interface or procedures.

NOTE: If logging is enabled during a restore, the log file is written to:

  

Related Topic

Manually Restore a Master Database

To restore the master database from a LiteSpeed backup, start the server instance in single-user mode and execute the LiteSpeed restore statement.

To manually restore a master database

  1. From a command prompt, run as administrator, change the directory until you are in the directory containing sqlservr.exe. Usually:

    C:\Program Files\Microsoft SQL Server\MSSQL\Binn

  2. Run the following to start the server instance in single-user mode:

    sqlservr.exe -c –m"LiteSpeed"

    For named instance:

    sqlservr.exe -c –m"LiteSpeed" –s"<instance name>"

    NOTE: You must switch to the appropriate directory (for the instance of Microsoft SQL Server you want to start) in the command window before starting sqlservr.exe.

  3. Using another command prompt, change the directory until you are in the directory containing SQLLiteSpeed.exe. Usually:

    C:\Program Files\Quest Software\LiteSpeed\SQL Server\Engine

  4. Execute the LiteSpeed restore statement to restore a full database backup of master.

    SQLLiteSpeed.exe -R"Database" -D"master" -F "<path to backup file\backup file name>" -W"REPLACE" -S "<server name>\<instance name>" –T

    Example Script
    Restore a master database to the default instance

    sqllitespeed.exe –R"Database" -D"master" -F"c:\backup\master.bak" –W"REPLACE" -S "<server name>" -T

    Restore a master to the named instance

    sqllitespeed.exe –R"Database" -D"master" -F"c:\backup\master.bak" –W"REPLACE" -S "<server name>\<instance name>" –T

    Restore with encryption

    sqllitespeed.exe –R"Database" -D"master" -F"c:\backup\master.bak" -Kpassword –W"REPLACE" -S "<server name>\<instance name>" –T

    Restore with replace

    sqllitespeed.exe –R"Database" -D"master" -F"c:\backup\master.bak" -Kpassword –W"REPLACE" -S "<server name>\<instance name>" –T -W"MOVE 'master' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf'" -W"MOVE 'master_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master_Log.ldf'"

    Restore using the Tivoli Storage Manager

    sqllitespeed.exe –R"Database" -D"master" –W"REPLACE -i"filespace\highlevel\lowlevel" -c"nodename" -k"password" -j"c:\program files\Tivoli\TSM\baclient\dsm.opt"

  5. Restart SQL Server and LiteSpeed. If the process hangs, stop the following services and retry them:

    • Alerter

    • Cluster

    • Computer Browser

    • Event Log

    • License Logging

    • Logical Disk Manager

    • Messenger

    • Net Logon

    • NTLM Security Support Provider

    • Plug and Play

    • Remote Procedure Call (RPC) Locator

    • Remote Procedure Call (RPC)

    • Server

    • Print Spooler

    • TCP/IP NetBIOS Helper

    • Windows Time

    • Workstation

NOTE: To restore the master database from a native full backup, refer to msdn.microsoft.com. For example, SQL Server 2012: http://technet.microsoft.com/en-us/library/ms190679(v=sql.110).aspx.

 

 

Related Documents