You can restore a SQL database backup to an alternate SQL
Server system or database by using the MMC GUI. Data Protection for SQL Server must
be installed on both systems. Also, unlike legacy backups, VSS backups
cannot be restored into a SQL Server that has a different name.
Before you begin
You can also restore availability databases to an alternate
location on any availability replica in an availability group.
About this task
This procedure uses the following terms:
- Source system
- The system from which the original backup (to be restored) was
taken.
- Target system
- The alternate system to which the backup is to be restored.
When restoring availability databases, refer
to the following guidelines:
- Legacy restore
- You can restore an availability database on either a primary or
secondary replica.
During the restore process, the restored database
is removed from the availability group. When a database is removed
from the availability group, the database becomes a local database
on that replica. The database is restored as a local database. After
this restore is complete, manually add the database back to the availability
group. However, before you add the database to the availability group,
verify that the data on all replicas is transactionally consistent.
To verify data is transactionally consisent, verify that the
backup copy contains data and transaction log records. Full backups
and differential backups contain data and transaction log records
so that the restored database is transactionally consistent.
After
you verify that the data is transactionally consistent, the database
can be added to the availability group.
- VSS restore
- Because of a SQL Server limitation, you cannot restore a VSS backup
to an alternative SQL server instance. Therefore, VSS backups must
be restored to the same SQL server instance where the snapshot was
taken.
Procedure
To restore a SQL database to an alternate location:
- Copy the Data Protection for SQL Server options
file (dsm.opt) from the source system to the
target system. 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.
- Start the Management Console.
- In the Recover tab for the SQL instance,
select a database to restore. Optionally click All Backups in
show all active and inactive backups. You can select only one database
at a time when restoring it to an alternate location.
- For availability databases, click DP
Node Backups to show all AlwaysOn node backups.
The name of the button changes to AlwaysOn Node Backups.
The DP Node Backups / AlwaysOn Node
Backups button toggles between the standard database view
and the availability database view. The label reflects the type of
databases that are displayed in the view.
- Verify restore options. If the restore options are not
currently displayed, click Show Restore Options.
- Ensure that Wait for Tape Mounts for Restore is
set to True.
- Ensure that Wait for Tape Mounts for File
Information is set to True.
- If the database to be restored is going to replace an
existing database on the target system, click Replace.
- Use the Instant Restore option
to turn Instant Restore on or off. Click True to
use Instant Restore. Click False to disable
Instant Restore if you want to use Fast Restore.
Attention: Instant Restore overwrites all files on the destination
file system.
See Restore options for
descriptions of additional restore options.
- Click Restore to Alternate Location in
the Actions pane.
- Complete the Alternate Location Restore Settings window.
- In the Restore Into section of the
window, click Restore to new database, and
specify a target SQL server instance name and target database name
to restore a backup object to. The target database must exist. VSS backups cannot
be restored into a SQL Server that has a different name.
Attention: Any type of Restore Into processing
automatically disables VSS Instant Restore.
- If you want to specify new destination locations in which
to restore backed up SQL databases, logs, and FILESTREAM files (SQL
Server 2008, SQL Server 2008 R2, or later), click Restore
all files into one directory in the Relocate section
of the window.
If you want to restore the log files into a location
that is different from where the SQL database and other related files
are being restored, select Relocate logs into and
specify a new path in the text entry field.
If
you want to restore FILESTREAM files (SQL Server 2008, SQL Server
2008 R2, or later) into a location that is different from where the
SQL database and logs are being restored, select Relocate
other files into, and specify a new path in the text entry
field.
- If you want to restore one or more individual SQL database,
log, and FILESTREAM files, click Relocate files individually,
select one or more file entries, and click Browse to
open a folder selection window. Select a folder or make 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 partial restore operation in the Management Console
(MMC) GUI. You must use the command-line interface to do a partial
restore that requires these parameters.
- Click Restore to close the Alternate
Location Restore Settings window and begin the restore.
What to do next
You can view the status of the restore operation by clicking
Task
List in the bottom half of the results pane. Click
Task
Details to view detailed status information.