Restoring an SQL Server database to an alternate instance

By using Microsoft Management Console (MMC), you can restore an SQL Server database backup to an alternate SQL Server instance or database. You can also restore availability databases to an alternate location on any availability replica in an availability group.

Before you begin

Install Data Protection for SQL Server on both systems. Unlike legacy backups, you cannot restore VSS backups to an SQL Server that has a different name.

About this task

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 select only one database at a time when you restore a database to an alternate location.

Procedure

  1. Copy the Data Protection for SQL Server options file (dsm.opt) from the source system to the target system.
    Source system
    The system from which the original backup (to be restored) is created.
    Target system
    The alternate system to which the backup is to be restored.
    By default, the dsm.opt file is in the C:\Program Files\Tivoli\TSM\TDPSql directory. If passwordaccess generate is specified in the dsm.opt file, you might need to reset the password for this node on the Tivoli® Storage Manager server.
  2. Start MMC.
  3. On the Recover tab for the SQL Server instance, specify the type of SQL Server data to restore.
    Table 1. Database backup views
    Task Action
    View a list of SQL Server databases that are available for a restore operation Click All Backups.
    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. Verify the options for the restore operation. If the restore options are not displayed, click Show Restore Options.
    1. Ensure that Wait for Tape Mounts for Restore is set to Yes.
    2. Ensure that Wait for Tape Mounts for File Information is set to Yes.
    3. If the database to be restored is to replace an existing database on the target system, click Replace.
    4. Use the Instant Restore option to turn Instant Restore on or off. Click Yes to use Instant Restore. Click No to disable Instant Restore if you want to use Fast Restore.
      Attention: Instant Restore operations overwrite all files on the destination file system.
  5. To start the backup operation, in the Actions pane, take one of the following actions:
    1. Click Restore to Alternate Location.
    2. Click Restore VerifyOnly to Alternate Location. The Restore VerifyOnly to Alternate Location task is available only if all the selected database backups are legacy backups.
  6. In the Restore Into section of the Alternate Location Restore Settings window, click Restore to new database, and specify a target SQL Server instance name and target database name to restore a backup object to. VSS backups cannot be restored into an SQL Server that has a different name.
    Tip: Any type of Restore Into processing automatically disables the VSS instant restore function.
  7. In the Relocate section of the window, filter the restore processing operations.
    Table 2. Database backup views
    Task Action
    Specify new destination locations in which to restore backed up SQL Server databases, logs, and FILESTREAM files (SQL Server 2008 or later versions) Click Restore all files into one directory.
    Restore the log files into a location that is different from where the SQL Server database and other related files are restored Select Relocate logs into and specify a new path in the text entry field.
    Restore FILESTREAM files (SQL Server 2008 or later versions) into a location that is different from where the SQL database and logs are restored Select Relocate other files into, and specify a new path in the text entry field.
    Restore one or more individual SQL Server database, log, and FILESTREAM files Click Relocate files individually, and click Browse to open a folder selection window. Select a folder or create a new folder, and click OK. The path of the selected files entries is set to use the folder. This option is available for legacy backups only.
    Restriction: You cannot relocate database files and logs with a partial restore operation in MMC. You must use the command-line interface to complete a partial restore operation that requires these parameters.
  8. Click Restore to close the Alternate Location Restore Settings window and begin the restore.
  9. To view the status of the restore operation, click Task List in the lower half of the results pane. Click Task Details to view detailed status information.