Restoring SQL Server data

Edit online

You can restore SQL Server databases or parts of databases only from full, differential, and log backups. You can also restore availability databases with SQL Server 2012 and later versions.

About this task

Attention: When you restore a database, existing data is overwritten by the restored data and is no longer available after the restore operation is complete.
  • The Regional settings, which are defined in the Regional property page, must match the date format that is defined for the Microsoft™ SQL Server.
  • You can use VSS to run backup operations of type full. You can apply legacy differential and legacy log backups after a full VSS backup is restored.
    • When Virtual Environment restore operations are configured from the IBM Storage® Protect server, you can restore and view these databases from the Recover tab.
    • You can also restore availability databases that you backed up with the AlwaysOn node with SQL Server 2012 and later versions. Backups of availability databases can be restored to any availability replica in an availability group.
    • You can restore a legacy database backup that is verified as valid and complete with the Verify Only option in Microsoft Management Console (MMC), or with the /VERIFYOnly option of the restore command on the command line.

Procedure

  1. Start MMC.
  2. Select the SQL Server instance in the tree.
  3. On the Recover tab for the SQL Server instance, specify the type of SQL Server data to restore.
    Table 1. Database restore views
    Task Action
    View a list of SQL Server databases that are available for a restore operation Click View: Databases.
    View a list of SQL Server database backup files that are available for a restore operation Click View: Files.
    View a list of SQL Server 2012 and later version availability databases that are available for a restore operation Click DP Node Backups to show AlwaysOn node backups.

    Toggle the DP Node Backups / AlwaysOn Node Backups button for the respective database views.
  4. On the Recover tab of an SQL Server instance, select an option for viewing databases.
    In the Results pane, browse to the databases that are available to restore. The following options are available:
    Table 2. Database restore selection options
    Option Action
    Search Enter a keyword in the Search field to refine and filter the list of databases.
    Filter Use the filter options to refine and filter the list of databases.
    1. Click Show Filter Options and Add Row.
    2. In the Column Name field, click the down arrow and select an item to filter.
    3. In the Operator field, select an operator.
    4. In the Value field, specify a filter value.
    5. If you want to filter on more items, click Add Row.
    6. Click Apply Filter.
    Backups Select the database to restore. You can click Active Backups to show only active backups, or click All Backups to show both active and inactive backups.
    Refresh Click Refresh to update the view with your changes.

    If you applied a filter, the objects on the server that match the filter or search criteria are listed on the Recover tab. The status area indicates the number of items that match the criteria n of x displayed, where n equals the number of objects that match the filter criteria, and x is the number of objects that are retrieved from the server. For example, "5 of 20 displayed." If you specify refresh options to further narrow your results, and click Refresh again, the objects on the server that match the filtered and refresh options are displayed. Each time that you click Refresh, another query is run against the IBM Storage Protect server.

  5. Verify the options for the restore operation. If the restore options are not displayed, click Show Restore Options.
  6. Optional: Choose a mode for the current task:
    • Run Interactively: Click this item to run the current task interactively. This selection is the default.
    • Run Scheduled: Click this item to convert the current action into a scheduled task. When you select this item, the schedule wizard runs the command that is required to complete the task.
  7. To start the restore operation, in the Actions pane, take one of the following actions:
    • Click Restore.
    • Click Restore VerifyOnly. The Restore VerifyOnly task is available only if all the selected database backups are legacy backups.
      Important:

      When you select the Restore VerifyOnly action, the number of stripes that are used for the restore must be the same or greater than the number of stripes that are used for the backup you are verifying. If it is not, the Restore VerifyOnly operation terminates with an error.

  8. To view the status of the restore operation, click Task List in the results pane. Click Task Details to view detailed status information.