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
- Select Maintenance Plans in the Navigation pane (CTRL+4).
- In the Server tree, right-click an instance and select Create New Maintenance Plan.
- Drag and drop the Back Up Database task in the middle of the Design pane and double-click it.
- Select the Full backup type.
-
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.
- Select the Create backup file for every database option and specify the Destination folder and file extension for the backups.
- 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.
- Click Ok to save the task.
- Click
on the tool bar. The task you just created will serve as a base for differential and t-log backups.
- Create two new subplans. To create a subplan, click
, then Ok.
- Select each of the two new subplans and click
to paste.
- 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.
-
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.
- Save the plan, click
Copy Plan.
- For all instances where you want to paste the plan, right-click the instance and select Paste Maintenance Plan.
Back Up Databases Using Maintenance Plans
Restore Databases
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:
- For information about performing file restores, see http://msdn.microsoft.com/en-us/library/ms190710.aspx.
- For a description of restoring file and filegroup backups in SQL Server, see http://support.microsoft.com/kb/281122.
|
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. |
To run the Restore wizard
- Select the Backup Manager pane (CTRL+1).
-
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.
-
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%.
Scenario: Select the server instance to restore the backup to and enter a new database name.
-
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:You can do point in time restores, restoring from a specific date and time or a time period (for example four hours prior to recovery).
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.
Scenario: Select Device and then Disk and specify the backup files you copied.
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.
- Add - ( For Google Storage only), click to add cloud vendor, display name, service account ID, private key, project ID, storage class, region and bucket. Use SSL is always selected as Google always uses it.
-
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.
- Edit - ( For Google Storage only), click to edit display name, service account ID, private key, project ID, storage class, region and bucket. Use SSL is always selected as Google always uses it.
- 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,
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.
TSM Object
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.
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.
NOTE: The restore from parameters help LiteSpeed to narrow down its search for the required backup files in the source folders.
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 check box to add copy only backups in the restore.
- Specify a point in time to restore to. By default LiteSpeed restores to the most recent state possible. Alternatively, specify a specific date and time or a date and time relative to the restore time. For example, specify a time measured in days, hours, minutes and seconds from the restore time.
-
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.
-
If you selected Automated Restore, on the Database Integrity page, 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.
-
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.
-
Review the following additional information about the Backup Content page. Skip this page for Automated Restore.
-
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.
-
Review the following additional information about the Data Files page:
-
If you added an attachment to the backup file, select the Restore Attached Files and Directories on the Attached Files page.
- 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.
- 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:
-
Complete the wizard.
to launch a window containing a list of backups to restore. 
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.
indicates a selected backup. 
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:
- The default output directory—For more information, see Configure Logging in LiteSpeed.
- The root of C:\—On a server that does not have LiteSpeed installed.
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
-
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
-
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.
-
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
-
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"
-
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.