SnapProtect - Advanced Restore - SQL Server iDataAgent

Table of Contents

Restoring a Database

By default, a database is restored in the same location from where it was backed up and the existing database files are overwritten. This restore leaves the database in an online state. Follow the steps given below to restore a database:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server.
  2. Right-click the <Instance> and then click All Tasks | Browse and Restore.
  3. Click View Content.
  1. In the right pane of the Browse window, select a non-system database you want to restore and click Recover All Selected.
  2. Select Unconditionally overwrite existing database or files checkbox.
  3. Click OK to start the restore.

Recovering a Database

By default, a database is left in the online state after a restore. However, you might need to change the state of the database depending on your needs (e.g., if you want to make the database inaccessible to the users). By recovering a database, you can select the state in which the database is to be left. Follow the steps given below to recover a database:

Recover a Database in the Offline State

If you want the database to be offline after the restore, follow the steps given below:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server.
  2. Right-click the <Instance> and then click All Tasks | Browse and Restore.
  3. Click View Content.
  1. In the right pane of the Browse window, select the database to be recovered and click Recover All Selected.
  2. Click Norecovery.
  3. Select Unconditionally overwrite existing database or files checkbox.
  4. Click OK.

Recover One or More Databases in the Online State

If you have a database in the offline or standby state and you want to bring the database back to the online state, follow the steps given below:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server.
  2. Right-click the <Instance> and then click All Tasks | Browse and Restore.
  3. Click View Content.
  1. In the right pane of the Browse window, select the databases you want to recover and click Recover All Selected.
  2. In the Restore Options window, click the Advanced button.
  3. Select the Options tab and click Recover Only.
  4. Click OK.

Restoring System Databases

System databases are essential for the operation of the SQL server instance. If you need to restore the SQL Server in the event of a system failure, the system databases (master, msdb and model) must be backed up. The tempdb does not get backed up as it is re-created by the SQL Server every time the server is started.

By default, system databases are restored in the same location from where they were backed up and their data files are overwritten. Follow the steps given below to restore the system databases:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server.
  2. Right-click the instance, point to All Tasks, and then click Browse and Restore.
  3. Click View Content.
  4. In the left pane of the Browse window, navigate to the instance node.
  5. Select the system databases (master, msdb, and model) in the right pane and click Recover All Selected.

    If you choose to restore the system databases individually, it is recommended to follow this order: master, msdb, model.

  6. Select Unconditionally overwrite existing database or files checkbox.
  7. Click OK to start the restore.
  8. Click Yes. The system stops and restarts the SQL Server service in single-user mode.

Restoring SQL Databases to a Point-in-Time

If any undesired transaction occurs in the database, you can revert the database to a state just before the transaction. The point in time option enables you to restore the database to a specific point-in-time. This option is useful in the following scenarios:

  • If any undesired transaction occurs in the database, you can revert the database to a state just before the transaction.
  • If a database fails, you can restore to the state just before the point of failure.
  • You can restore multiple databases to a consistent time. This will be useful for the absolute synchronization of the databases.

 Before performing the point-in-time restore of a database, ensure that transaction logs for the database are backed up. Follow the steps given below to restore a database(s) to a point in time:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server.
  2. Right-click the <Instance> and then click All Tasks | Browse and Restore.
  3. Click View Content.
  1. In the right pane of the Browse window, select one or more databases and click Recover All Selected.
  2. Select the Point-in-Time check box.
  3. From the Restore Time list, select the date and the time.
  4. Select the Unconditionally overwrite existing database or files check box.
  5. Click OK to start the restore.

The database(s) will be restored to the selected date and time.

When you perform a point-in-time restore for a database, the next scheduled backup for that database will automatically convert to a Full backup.

 

Performing an Out-of-Place Restore

Use one of the methods below to restore a SQL database to a different location.

Copying a Database to a Different Location

Follow the steps below to restore a database to a different location on the same destination server:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server.
  2. Right-click the <Instance> and then click All Tasks | Browse and Restore.
  3. Click View Content.
  1. In the right pane of the Browse window, select the databases you want to restore and click Recover All Selected.
  2. Rename the database under the Database column and change the path of the database and log files under the Physical Path column.

    • If there are many files, use the Find & Replace option to enter the new destination path for all of them at once.
    • If the database file path is changed without changing the database name, the existing database will be overwritten and will point to the new location.

  3. Click OK to start the restore.

Moving a Database

You can restore the database to a different location without changing its name. You cannot move a system database to a different location.

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server.
  2. Right-click the <Instance> and then click All Tasks | Browse and Restore.
  3. Click View Content.
  1. In the right pane of the Browse window, select a database and click Recover All Selected.
  2. Change the path of the database under the Physical Path column.
  3. Click OK to start the restore.

Moving a Database Back to the Original Location after a Move

If you perform a restore operation to move the database back to its original location, the database files will not be moved back as the database already exists on the server. The restore job will only check the database name and will restore the database on top of the same database regardless of its location. To move the database files back to the original location, use one of the following workarounds:

  • Detach and re-attach the database.
  • Perform a full backup of the database before moving the database back to its original location.
  • Drop the database from the SQL Server instance and perform a restore with the default values.

Restore to Disk

You can restore the backed up data to a disk on either the same SQL Server or to a different SQL server. This restored data on the disk can be used later to perform database restore using SQL Management studio or any other third party tools.

For Snap/VSS Backups, during restore to disk the actual database files (MDF/LDF/NDF) will be copied to the folder location provided by the user on the destination SQL server machine. In this scenario the database can be restored with different names while physical names should be left intact. All log backups which will be restored as *.bak files cannot be applied to restored Full or Differential backups and can be discarded. This option is applicable for VSS and SNAP backups performed using SnapProtect 10.0 or higher.

Selection of Restore To Disk option, overrides all other options within the SQL Server Restore screen.

Follow the steps given below to restore the data to a disk.

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server.
  2. Right-click the <Instance> and then click All Tasks | Browse and Restore.
  3. Click View Content.
  1. In the right pane of the Browse window, select a database(s) you want to restore and click Recover All Selected.
  2. Click the Advanced button.
  3. Click the Options tab and select the Restore To Disk checkbox.
    • In the Path field, type the path location of the destination SQL server, where you want to restore.

      Use Browse, if you plan to restore to the same SQL server

    • For Snap/VSS Backups, if needed, select Attach to SQL Server. This option will allow to attach the database files (MDF/LDF/NDF), that were restored to the selected SQL server.
    • Click OK.
  4. If needed, select the Destination Server from  the available list. The destination SQL Server should be of the same or higher SQL Server version as that of the source SQL Server.
  5. Click to select Unconditionally overwrite existing database or files.
  6. Click OK.

Exclusive Database Access during Restores

Restore of database fails if exclusive access to database cannot be obtained. In such scenarios  Drop Connections to Database option is used to drop all existing connections to the database. By default this option is set to false. Follow the steps given below to enable this option:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server.
  2. Right-click the <Instance> and then click All Tasks | Browse and Restore.
  3. Click View Content.
  1. In the right pane of the Browse window, select a non-system database you want to restore and click Recover All Selected.
  2. Click the Advanced button.
  3. Click the Options tab and select the Drop Connections to Database checkbox.
  4. Click OK.

Restoring Databases to a Different Instance

Microsoft SQL databases (along with the logs) backed up using SnapProtect can be restored to a different instance. This is useful in cases where the log needs to be applied, or where a point-in-time restore is required.

We recommend that you perform a Restore to Disk operation when only the databases are restored from an SnapProtect backup.

Note: You can restore databases from one instance to another only if all the databases that you selected are SnapProtect protected. If you need to restore databases from traditional and SnapProtect backups, you can restore them separately.

Before You Begin

  • Verify that the account used for impersonation on the source instance has access to the destination client.
  • Verify that the account used for impersonation on the destination client has sysadmin role for attaching restored databases.
  • Verify that the staging location or the UNC path does not contain files with the same names as the databases being restored.

Procedure

  1. From the CommCell Browser, click Client Computers > client > SQL Server.
  2. Right-click the instance, and then click All Tasks > Browse and Restore.
  3. In the Browse and Restore Options dialog box, click View Content.
  4. Select the data that you want to restore, and click Recover All Selected.

    The SQL Restore Options dialog box is displayed.

  5. On the General tab, select an SQL server from the Destination Server list.
  6. In the Database column, rename the database. In the Physical Path column, change the path of the database and the log files.

    Note: Changed database names should be unique on both the source server and the destination server.

  7. Optional: In the Staging Path on Source Client box, type path of a staging location where you want to stage the databases before they are restored to the destination server.

    Notes:

    • Verify that the staging location has enough space to stage the databases.
    • If you do not specify a staging location, a UNC path is created based on the path of the selected databases.
  8. Select the Unconditionally overwrite existing database or files check box.
  9. Click OK to start the restore.

Scheduling a Restore

Follow the steps given below to schedule a restore job:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server.
  2. Right-click the <Instance> and then click All Tasks | Browse and Restore.
  3. Click View Content.
  1. In the right pane of the Browse window, select a database to restore and click Recover All Selected.
  2. Select the required restore settings.
  3. Click the Job Initiation tab and click Schedule.
  4. Click Configure.
  5. From the Schedule Details dialog box, select the appropriate scheduling options and click OK.
  6. Click OK.

The restore job will start as per the schedule set.

Setting Up Pre-Post Processes

You can run batch files or shell scripts before and/or after restore jobs. Follow the steps given below to setup a process before or after the restore job:
  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server.
  2. Right-click the <Instance> and then click All Tasks | Browse and Restore.
  3. Click View Content.
  1. In the right pane of the Browse window, select a database and click Recover All Selected.
  1. Click Advanced and then click the Pre/Post tab.
  2. If you want perform a process before the restore job, enter the path for the batch file in the Pre Recovery Command box or click Browse to select the batch file.
  3. If you want perform a process after the restore job, enter the path for the batch file in the Post Recovery Command box or click Browse to select the batch file.
  4. Select one of the following options:
    • Use Local Accounts  - Select this option if the local account has permissions to execute the processes on the destination client.
    • Impersonate User  - Select this option and enter the username and password, that has the permissions to execute the processes on the destination client.
  5. Click OK.
  6. Click OK to start the restore.

Setting Up a Pre-Post Process to Run after Each Attempt

By default, a specified post process command is executed only on successful completion of the restore operation.
Use the following steps to run a post process even if the restore operation did not complete successfully. For example, this may be useful to bring a database online or release a snapshot.

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server.
  2. Right-click the <Instance> and then click All Tasks | Browse and Restore.
  3. Click View Content.
  1. In the right pane of the Browse window, select a database and click Recover All Selected.
  1. Click Advanced and then click the Pre/Post tab.
  2. Enter the path for the batch file in the Post Recovery Command box or click Browse to select the batch file.
  3. Select the Run Post Process for all attempts check box.
  4. Click OK.
  5. Click OK to start the restore.

Managing Restore Jobs

Once you initiate the restore operation, a restore job is generated in the Job Controller. Jobs can be managed in a number of ways. The following sections provide information on the different job management options available:

Restarting Jobs

Jobs that fail to complete successfully are automatically restarted based on the job restartability configuration set in the Control Panel. Keep in mind that changes made to this configuration will affect all jobs in the entire CommCell.

To Configure the job restartability for a specific job, you can modify the retry settings for the job. This will override the setting in the Control Panel. It is also possible to override the default CommServe configuration for individual jobs by configuring retry settings when initiating the job. This configuration, however, will apply only to the specific job.

Configure Job Restartability at the CommServe Level

  1. From the CommCell Console toolbar, click Control Panel.
  2. Under the System section, click Job Management.
  3. Click Job Restarts tab and select a Job Type.
    • Select Restartable to make the job restartable.
    • You can change the value for Max Restarts to change the maximum number of times the Job Manager will try to restart a job.
    • You can change the value for Restart Interval (Mins) to change the time interval between attempts for the Job Manager to restart the job.
  4. Click OK.

Controlling Jobs

The following controls are available for running jobs in the Job Controller window:

Suspend

Temporarily stops a job. A suspended job is not terminated; it can be restarted at a later time.

Resume

Resumes a job and returns the status to Waiting, Pending, Queued, or Running. The status depends on the availability of resources, the state of the Operation Windows, or the Activity Control setting.

Kill

Terminates a job.

Suspending a Job

  1. From the Job Controller of the CommCell Console, right-click the job and select Suspend.
  2. The job status may change to Suspend Pending for a few moments while the operation completes. The job status then changes to Suspended.

Resuming a Job

  1. From the Job Controller of the CommCell Console, right-click the job and select Resume.
  2. As the Job Manager attempts to restart the job, the job status changes to Waiting, Pending, or Running.

Killing a Job

  1. From the Job Controller of the CommCell Console, right-click the job and select Kill.
  2. Click Yes when the confirmation prompt appears if you are sure you want to kill the job. The job status may change to Kill Pending for a few moments while the operation completes. Once completed, the job status will change to Killed and it will be removed from the Job Controller window after five minutes.

Additional Restore Options

Several additional options are available to further refine your restore operations. The following table describes these options, as well as the steps to implement them.

Be sure to read the overview material referenced for each feature prior to using them.

Option Description Related Topic
Use hardware revert capability if available This option allow you to revert the data to the time when the snapshot was created. Selecting this option brings back the entire LUN to the point when the snapshot was created, overwriting all modifications to the data since the snapshot creation. This option is only available if the storage array used for SnapProtect Backup supports the revert operation.
  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server.
  2. Right-click the <Instance> and then click All Tasks | Browse Backup Data.
  3. In the Browse Options dialog box, click OK.
  4. In the Client Browse window, select the database you want to restore and click Recover All Selected.
  5. In the SQL Restore Options dialog box, click Advanced.
  1. Select Use hardware revert capability if available.
  2. Click OK.
To avoid restore failure that occurs while performing a hardware specific revert on a cluster, see the troubleshooting article Revert failure occurs for cluster restore.
Startup Options

The Startup Options are used by the Job Manager to set priority for resource allocation. This is useful to give higher priority to certain jobs. You can set the priority as follows:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server.
  2. Right-click the <Instance> and then click All Tasks | Browse Backup Data.
  3. In the Browse Options dialog box, click OK.
  4. In the Client Browse window, select the database you want to restore and click Recover All Selected.
  5. In the SQL Restore Options dialog box, click Advanced.
  1. In the Advanced Restore Options dialog box, click Startup.
  2. On the Startup tab, select Change Priority.
  3. Enter a priority number - 0 is the highest priority and 999 is the lowest priority.
  4. Select the Start up in suspended State check box to start the job in a suspended state.
  5. Click OK.
Refer to Job Priority and Priority Precedence.
Copy Precedence By default, the system retrieves data from the storage policy copy with the lowest copy precedence. If the data was pruned from the primary copy, the system automatically retrieves data from the other copies of the storage policy in the lowest copy precedence to highest copy precedence order. Once the data is found, it is  retrieved, and no further copies are checked.

You can retrieve data from a specific storage policy copy (Synchronous Copy or Selective Copy). If data does not exist in the specified copy, the data retrieve operation fails even if the data exists in another copy of the same storage policy. Follow the steps given below to retrieve the data from a specific storage policy copy:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server.
  2. Right-click the <Instance> and then click All Tasks | Browse Backup Data.
  3. In the Browse Options dialog box, click OK.
  4. In the Client Browse window, select the database you want to restore and click Recover All Selected.
  5. In the SQL Restore Options dialog box, click Advanced.
  1. In the Advanced Restore Options dialog box, click Copy Precedence.
  2. On the Copy Precedence tab, select the Restore from copy precedence check box.
  3. Enter the copy precedence number.
  4. Click OK.
Refer to Recovering Data From Copies.
Data Path Options

The data recovery operations use a default Library, MediaAgent, Drive Pool, and Drive as the Data Path. You can use this option to change the data path if the default data path is not available.

Note: If you used a UNIX MediaAgent to perform the SnapProtect backup:

  • Change the value of the Allow restore jobs to use alternative MediaAgent and Drive Pool to access required media on the Resource Manager Configuration tab of the Media Management Configuration dialog box to 1. For more information, see Media Management Configuration: Resource Manager Configuration.
  • Select a Windows MediaAgent on the Data Path tab of the Advanced Restore Options dialog box to perform the restore operation.

Otherwise, the restore job will fail.

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server.
  2. Right-click the <Instance> and then click All Tasks | Browse Backup Data.
  3. In the Browse Options dialog box, click OK.
  4. In the Client Browse window, select the database you want to restore and click Recover All Selected.
  5. In the SQL Restore Options dialog box, click Advanced.
  1. In the Advanced Restore Options dialog box, click Data Path.
  2. On the Data Path tab, select the MediaAgent and Library.
  3. Select the Drive Pool and Drive for tape library.
  4. Select the name of the Proxy server if you wish to restore using a proxy server.

    You can select the Proxy server only when you are restoring from a snapshot. This option is not available for a regular restore.

  5. Click OK.
Refer to Change Data Path.
Encryption

If the client's data is encrypted with a pass phrase, you must enter the pass-phrase to start the data recovery operation. Follow the steps given below to enter the pass-phrase:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server.
  2. Right-click the <Instance> and then click All Tasks | Browse Backup Data.
  3. In the Browse Options dialog box, click OK.
  4. In the Client Browse window, select the database you want to restore and click Recover All Selected.
  5. In the SQL Restore Options dialog box, click Advanced.
  1. In the Advanced Restore Options dialog box, click Encryption.
  2. Enter the Pass Phrase.
  3. Click OK.
Refer to Data Encryption.
Alerts

This option enables users or user groups to get automatic notification on the status of the data recovery job. Follow the steps given below to set up the criteria to raise notifications/alerts:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | SQL Server.
  2. Right-click the <Instance> and then click All Tasks | Browse Backup Data.
  3. In the Browse Options dialog box, click OK.
  4. In the Client Browse window, select the database you want to restore and click Recover All Selected.
  5. In the SQL Restore Options dialog box, click Advanced.
  1. In the Advanced Restore Options dialog box, click Alerts.
  2. Click Add Alert.
  3. From the Add Alert Wizard dialog box, select the required threshold and notification criteria and click Next.
  4. Select the required notification types and click Next.
  5. Select the recipients and click Next.
  6. Click Finish.
  7. Click OK.
Refer to Alerts and Notifications.
CommCell Readiness Report The CommCell Readiness Report provides you with vital information such as connectivity and readiness of the Client, MediaAgent, and CommServe. It is useful to run this report before performing a backup or a recovery operation. Follow the steps given below to generate the report:
  1. On the CommCell Console menu bar, select the Reports tab.
  2. Click Readiness.

    The Report Selection dialog box appears.

  3. If necessary, select Reports | CommServe | CommCell Readiness in the left pane.
  4. Select the Computers tab, and then click Modify below the Computers list.
  5. In the dialog box that appears, clear Include All Client Computers and All Client Groups.
  6. Select the appropriate client from the Exclude list, and then click Include >.
  7. Click OK.
  8. Select the MediaAgent tab, and then clear Include All MediaAgents.
  9. Select the appropriate MediaAgent from the Exclude list, and then click Include >.
  10. Click Run.

    The report appears in your default Web browser.

Refer to CommCell Readiness Report.
Restore Job Summary Report

The Restore Job Summary Report provides you with information about all the data recovery jobs that are run in last 24 hours for a specific client and agent. You can get information such as failure reason, failed objects, job options, and so on. It is useful to run this report after performing the restore. Follow the steps given below to generate the report:

  1. On the CommCell Console menu bar, select the Reports tab.
  2. Click Summary.

    The Report Selection dialog box appears.

  3. If necessary, select Reports | Jobs | Job Summary in the left pane.
  4. On the General tab, select Data Recovery Jobs.
  5. Select the Computers tab.
  6. Select the appropriate client and the appropriate agent.
  7. Click Run.

    The report appears in your default Web browser.

Refer to Restore Job Summary Report.