Restore Data - Microsoft SQL Server - How To

From the CommCell Browser, right-click the database you want to recover, click All Tasks and then click Recover Only.
Click OK to confirm that you want to Recover the database. The SQL iDataAgent recovers the selected database to an ONLINE status.

Recovering All Configured Databases in a Single Instance

Before You Begin:

Review the general and agent-specific restore requirements accessed from Restore Backup Data prior to performing any restore operation.

Required Capability: See Capabilities and Permitted Actions

To RECOVER all configured non-system databases in a single instance:

From the CommCell Browser, right-click the instance node, click All Tasks and then click Recover SQL Server.
Click OK to confirm that you want to Recover all non-system databases. The SQL iDataAgent recovers the SQL Server to an ONLINE status.

Apply SQL Server Transaction Logs (Step Restore)

Before You Begin

Review the general and agent-specific restore requirements accessed from Restore Backup Data prior to performing any restore operation.

Required Capability: See Capabilities and Permitted Actions

To perform a Step Restore on a database:

Begin any SQL Server restore procedure.
When you reach the SQL Restore (General) dialog box:
1. Select the desired Destination Server.
2. Select Database Restore as the Restore Type.
3. Select the Full backup from the Restore Time.
4. Select NORECOVERY or STANDBY as the Recovery Type.
The Step Restore option is only available when the database is already in a NORECOVERY or STANDBY state and an additional log is available for restore.
When restoring encrypted data, refer to Data Encryption.
Click OK to start the database restore.
When the database restore is completed, repeat Step 1 in order to return to the SQL Restore (General) dialog box.
1. Select the desired Destination Server.
2. Select Step Restore as the Restore Type
3. Select the desired LOG backup from the Restore Time.
4. Select NORECOVERY or STANDBY as the Recovery Type
Start the restore. The SQL Server iDataAgent applies the next log in the sequence.
To apply additional transaction logs, repeat steps 5 and 6 as needed.

When you are applying the last transaction log, select RECOVER as the Recovery Type. This returns the database to a recovered state.

If you have already applied the last transaction log and the database is in NORECOVERY or STANDBY mode, and you want to return the database to a ready state, you can manually recover the database.

Restore a SQL Server Database to a Point in Time

Before You Begin

Review the general and agent-specific restore requirements accessed from Restore Backup Data prior to performing any restore operation.
In order to restore a database to a point in time, a transaction log backup must exist.

Required Capability: See Capabilities and Permitted Actions

To restore to a point in time:

Begin any restore procedure.
When you reach the SQL Restore (General) dialog box, select Point in Time and select a Restore Time.
Continue your restore.

NOTES

After performing a point in time restore job, the next backup job for that database is automatically converted to a full backup.

Restore a SQL Server Database to a Transaction Mark

Before You Begin

Review the general and agent-specific restore requirements accessed from Restore Backup Data prior to performing any restore operation.

Required Capability: See Capabilities and Permitted Actions

To perform a transaction mark restore job:

Begin any restore procedure. For file/file group restore jobs, you must perform a complete restore of all database files in order to restore to a Transaction Mark.
When you reach the SQL Restore (General) dialog box, select Transaction Marks and select a marked transaction from the list. When multiple databases are being restored, the list of transaction marks includes only those marks that are common in name and timestamp to all the selected databases within the selected backup cycle.
Select Stop at Mark or Stop Before Mark.
Continue your restore.

NOTES

After performing a transaction mark restore job, the next backup job for that database is automatically converted to a full backup.

Performing Partial ("Piecemeal) Restore of a SQL Server Database

Before You Begin

Review the general and agent-specific restore requirements accessed from Restore Backup Data prior to performing any restore operation.
In order to restore a database to a point in time, a transaction log backup must exist.

Required Capability: See Capabilities and Permitted Actions

To restore to a point in time:

Begin any restore procedure.
When you reach the SQL Restore (General) dialog box, select Partial Restore.
Continue your restore.

NOTES

After performing a partial ("piecemeal") restore job, the next backup job for that database is automatically converted to a full backup.

Restore Latest Backup Data (Warm Database Restore)

Before You Begin

Review the general and agent-specific restore requirements accessed from Restore Backup Data prior to performing any restore operation.
To schedule a restore of pass-phrase protected, encrypted data, you must first export the pass-phrase to the specific destination client using the Client Properties Encryption tab.
Be sure to confirm the database and file locations on the receiving server. These locations do not need to exist prior to performing the latest backup data restore job.

Required Capability: See Capabilities and Permitted Actions

To restore the latest backup data:

From the CommCell Browser, right-click the SQL server or database you want to restore, click All Tasks, and click Browse SQL Server or Browse SQL Instance as appropriate.
From the Browse Options dialog box, click Database, make any other desired selections in the dialog box, and then click OK.
From the Latest Data window, click the database that you want to restore and then click Recover All Selected.
From the SQL Restore Options dialog box, select the destination server that is to receive the database.
From the Restore Types list, click Latest Backup Data. Then make any other selections in this dialog box as appropriate.

If you do not need to view the database you can select a recover type of NORECOVER.
Click Advanced | Options to verify that the destination drive appears in the Advanced Restore Options dialog box. Click OK to return to the SQL Restore Options dialog box.
When restoring encrypted data, refer to Data Encryption.
After completing your selections, you can schedule the restore.
- Click the Job Initiation tab from the SQL Restore Options dialog box, click Schedule, click OK, and enter your selections in the Schedule Details (Schedule Details) dialog box. Note that clicking OK from this dialog box saves your schedule. See Scheduled Data Recovery Operations for an overview of this feature.
You can track the progress of the restore job using the Job Controller. While the job is running, you can right-click the job in the Job Controller and select Detail to view information on the job.
After the data has been restored, you will see a job completion message in the Job Controller and Event Viewer.

NOTES

When scheduling, remember to coordinate backup jobs and restore jobs such that there are no resource conflicts.

Skip Full Backup

Before You Begin

Review the general and agent-specific restore requirements accessed from Restore Backup Data prior to performing any restore operation.

Required Capability: See Capabilities and Permitted Actions

To skip the restore of the last full backup:

Begin any restore procedure.
When you reach the SQL Restore (General) dialog box, select Skip Full Backup.
Continue your restore.

NOTES

Even if a full backup is displayed in Restore Time, the last full backup will not be restored.

Unconditionally Overwrite Existing Database or Files

Before You Begin

Review the general and agent-specific restore requirements accessed from Restore Backup Data prior to performing any restore operation.

Required Capability: See Capabilities and Permitted Actions

To unconditionally write to the specified location and overwrite the files of any database:

Begin any restore procedure.
When you reach the SQL Restore (General) dialog box, select Unconditionally overwrite existing database or files.
Continue your restore.

Restore with a Different Data File Path (Copy a Database)

Before You Begin

Review the general and agent-specific restore requirements accessed from Restore Backup Data prior to performing any restore operation.
You can change the path for data files being restored for any level of restore. This is useful in situations in which you are performing a restore and the default path cannot be used for any reason, such as the following examples:
- a destination server does not have the corresponding path
- when restoring the database to the same server, but restoring the database under a different name.

Note, that if the database file path is changed, without changing the name of the database, then the existing database will be overwritten and will point to the new location.

Required Capability: See Capabilities and Permitted Actions

To restore with a different data file path:

Begin any SQL Server restore procedure.
When you reach the SQL Restore (General) dialog box, click Advanced.
From the Options tab of the Advanced Restore Options dialog box, in the Physical Path column, click each of the files of the database you want to redirect and change the path and/or filename to a new value. If there are many files in the database you want to move, click Find & Replace and enter the necessary values to change them all at once. If the path entered does not exist, it will be automatically created during the restore operation.
Continue the restore procedure.

Restore to a Different SQL Server Instance

Before You Begin

Review the general and agent-specific restore requirements accessed from Restore Backup Data prior to performing any restore operation.
When this restore takes place it attempts to restore the data files to the same path on the destination server as it was using on the original server. If you want to change the path, refer to Restore with a Different Data File Path (Copy a Database).

Required Capability: See Capabilities and Permitted Actions

To restore to a different SQL Server instance:

Begin any SQL Server restore procedure.
When you reach the SQL Restore (General) dialog box, select the target server from the Destination Server list.
Continue the restore procedure.

Back Up the Tail of the Transaction Log

In the event of a database failure, if it is unacceptable to lose changes made to your database since the last backup, you need to back up the tail of the transaction log prior to running a restore.

Before You Begin

Review the general and agent-specific restore requirements accessed from Restore Backup Data prior to performing any restore operation.

The requirements to accomplish this are:

SQL Server database must be in full or bulk-logged recovery mode
Microsoft SQL Server iDataAgent: Transaction log file(s) must be intact

Required Capability: See Capabilities and Permitted Actions

To back up the Log Tail:

Right-click the database, click All Tasks (if appropriate), and click Backup or Backup SQL Server.
Select Run Immediately, the Transaction Log backup type, the Do Not Truncate Log check box, and click OK.

The use of a log tail backup in your restore job is no different than using a standard log backup and, in fact, appears in your Restore Options dialog box as a standard log backup.

Now you're ready to restore. Refer to Restore a Database to the Point of Failure to select the appropriate restore job.

Restore a Single Database Without Browsing

Use this procedure when you want to retrieve the latest backup job.

Required Capability: See Capabilities and Permitted Actions

To restore a database without browsing:

From the CommCell Browser, right-click the database that contains the data you want to restore, click All Tasks and then click Restore Database.
In the SQL Restore (General) dialog box, from the Restore Time list, select the backup you want to restore. Based on the selected Restore Time options, the necessary backups are restored.
When restoring encrypted data, refer to Using Data Encryption.
Start the restore.
If you are restoring the master database, you are informed that the SQL server must be stopped. Click Yes to continue or No to cancel the restore job without shutting down SQL server.

Restore One or More Files or File Groups

Before You Begin:

Review the general and agent-specific restore requirements accessed from Restore Backup Data prior to performing any restore operation.
Review the discussion Restore Files or File Groups.
Make sure you have backed up the log tail. See Back up the Tail of the Transaction Log for step-by-step instructions.
For a client on a Windows Server 2003 platform or later, if you are running VSS backups, the Restore Files/File Groups command is unavailable.

Required Capability: See Capabilities and Permitted Actions

To perform a SQL Server file/file group restore job:

From the CommCell Browser, right-click the database that contains the files or file groups you want to restore, click All Tasks and then click Browse SQL Instance.
In the Browse Options dialog box, click File/File Groups. Then click OK to accept all defaults to browse the latest backups for the selected data or see Browsing Data for a list of customized browse operations and their step-by-step instructions.
From the Browse window, select one or more files or file groups. See Select Objects From the Browse Window for Restore/Recover for more information.
Continue with your restore. When restoring encrypted data, refer to Data Encryption.
Click OK to start the restore.

Restore the Entire Database at the File/File Group Level

Before You Begin:

Make sure you have backed up the log tail if you want to restore the database to the point of failure. See Select Objects From the Browse Window for Restore/Recover for step-by-step instructions.
For a client on a Windows Server 2003 platform or later, if you are running VSS backups, the Restore Files/File Groups command is unavailable.

Required Capability: See Capabilities and Permitted Actions

To restore an entire SQL Server database through File/File Groups:

From the CommCell Browser, right-click the database you want to restore, click All Tasks and then click Browse SQL Instance.
In the Browse Options dialog box, click File/File Groups, select the appropriate browse time option, and click OK.
In the Browse window, select the database in the left pane, and click Recover All Selected.

If you are planning to restore only certain files or file groups, use Restore One or More Files or File Groups procedure.
In the SQL Restore dialog box, the Database Restore option should be already selected. Select additional restore options as needed.
When restoring encrypted data, refer to Data Encryption.
Click OK to start the restore.

Restore a SQL Server Database with a Different Name (Move a Database)

Before You Begin

Review the general and agent-specific restore requirements accessed from Restore Backup Data prior to performing any restore operation.
VSS-enabled backup jobs must be restored to the exact server, database, and file name from which they originated.
If you are attempting to rename a database and keep its original path, you must first delete the original database from SQL Server before performing this restore. This procedure attempts to restore the renamed database to the same path as the original database. Should the original database still exist at this path, the restore will fail. Alternately, you can rename the database and change its path without deleting the original database. For this option, refer to Restore with a Different Data File Path (Copy a Database).

For File/File Group restore jobs, you must perform a complete restore of all database files in order to restore to a different SQL Server instance.

System databases cannot be moved.
System databases cannot be restored on top of exiting system databases on a different instance (also considered a move operation).
System databases can restore on top of the same database on the same instance provided the database name and filenames are not changed (i.e., an in�place restore).
System databases can be restored to any SQL instance provided both the database name and filenames are changed.

Required Capability: See Capabilities and Permitted Actions

To restore a SQL Server database with a different name:

Begin any SQL Server restore procedure.
When you reach the SQL Restore (General) dialog box, click Advanced.
From the Options tab of the Advanced Restore Options dialog box, in the Database column, click each database you want to rename, and change the name to the new value. When a change is made to one database in this column, the change is automatically propagated to the other files of the same database.
Continue your restore.

Restore Multiple SQL Server Databases to a Consistent Time

Before You Begin

Review the general and agent-specific restore requirements accessed from Restore Backup Data prior to performing any restore operation.
Run log backups for each database
Run log tail backup on all databases to be synchronized, refer to Restore a Database to the Point of Failure

Required Capability: See Capabilities and Permitted Actions

To restore multiple databases to a consistent time:

For each instance: From the CommCell Browser, right-click an instance, click All Tasks and then click Browse SQL Instance.
Browse for databases based on the criteria correct for your situation. For more information, refer to Browsing Data.
Select the non-system databases you want to restore and click Recover All Selected.
In the SQL Database Restore Options dialog box, follow the procedure you want to execute:
- Restore to Transaction Marks
- Restore to a Point in Time

Set the VDI Time-out Value for Large SQL Database Restores

Before You Begin

The VDI Timeout setting will be applied to all SQL Server iDataAgents on this client.

Required Capability: See Capabilities and Permitted Actions

To set the VDI time-out value:

From the CommCell Browser, right-click the SQL Server instance that contains the data files you want to restore, and click Properties from the shortcut menu.
From the General tab, increase the VDI Timeout, and click OK.
After changing the VDI Timeout setting, begin any restore procedure as usual.