> Expert > Backup and Restore Agents > Databases > Microsoft SQL Server > Online Help

Advanced Restore Options (Options)

Restore Types

Specifies the type of restore job.

Database Restore

Specifies that the restore operation must restore the selected databases beginning with the most recent full backup and then applying differential and/or transaction log backups up to the selected restore time.

Step Restore

To view this option, add the bEnableAdvancedRestoreOptionsMSSql additional setting to the CommServe computer. For instructions on adding additional settings from the CommCell Console, see Add or Modify an Additional Setting.

Specifies that the restore operation must perform a step restore by applying the selected transaction log.

Whether the logs are applied to a SQL step restore operation, this option is available only under the following conditions:

  • Single database restores

  • The restore destination is the source computer

  • The database backup and restore histories have not been removed from the SQL Server's history tables

  • The selected database was previously restored to STANDBY state for a SQL step restore

This option is not available for:

  • Instance restores

  • Multiple database restores

  • File or File Group restores

  • When restoring data protection operations that used VSS to create the snapshot

Recover Only

Specifies that the restore operation must recover the databases to an available online state. No data is actually written. All other options are disabled when this option is selected.

Transaction Log Marks

Restores transaction marks that exist in the transaction log. For transaction mark restores, you must administer and maintain your Microsoft SQL Server transaction marks so that they are available for use by the SQL Server Agent. This option is enabled when the database selected for restore has transaction marks in the transaction log.

  • Stop at mark

    Restores data to the selected transaction mark. After restoring a database to a selected transaction mark, the subsequent backup job for that database is automatically converted to a full backup.

  • Stop before mark

    Restores data to a previous transaction mark. After restoring a database to a previous transaction mark, the subsequent backup job for that database is automatically converted to a full backup.

  • Restore Time

    Displays a list of transaction marks that exist in the selected database for the given restore time. For some SQL Server environments, you may have multiple databases for which absolute synchronization of the databases after a restore is critical. When you want to restore multiple databases, you must restore each database individually to the common mark.

Partial Restore

Partial restores, known as "Piecemeal Restore" in SQL Server 2005, allow databases that contain multiple filegroups to be restored and recovered in stages. The process starts with a restore of the primary filegroup, then a restore or recovery of one or more secondary filegroups.

Latest Backup Data

Allows restores of the latest backup data on one or more non-system secured databases in the SQL server. You can use this feature to create a spare copy of the primary SQL Server database (hot standby server) within the same domain, a different domain, or across a Wide Area Network (WAN).

Apply Log backups only

Only available when the Latest Backup Data option is selected. Select this option if you wish to include only the transaction log backups completed since the last restore operation. The restore operation will then skip any full backup jobs completed since the last restore operation.

Note

  • Before selecting this option verify that the databases are in standby state or restoring state.

  • If you do NOT select the Apply Log Backups Only option and the database already exists, all backup jobs completed since the last restore operation will automatically be restored.

  • If you do NOT select the Apply Log Backups Only option and the database does not exist, the software will automatically perform a full restore of the database.

Drop Connections to Database

Select this option to obtain exclusive access to a database for restore. The database restore operation fails if exclusive access to the database cannot be obtained.

Skip Full Backup

Select this option to restore the differential backups and/or the transaction log backups since the last specified date and time instead of the full backup. Select this option when you have restored the latest full backup by using third-party software, which means that you do not need to restore the full SQL Agent backup. Note that in such a case, you only select this option when a SQL Agent full backup exists that is more recent than the full backup currently restored on the client by third-party software.

Full Backup Only

Select this option to restore only the full backup of a database.

Restore to Disk

Select this option to restore the backed up data to a disk that is either on the same SQL server or a different SQL Server. When you select this option, you can restore the data to the database by using a third-party tools such as SQL Management Studio.

To restore the data to disk on the same SQL Server, in the Path box, enter the path where you want to restore the data.

To restore the data to disk on a different SQL server, on the General tab of the SQL Restore Options dialog box, select a server from the Destination Instance list, and then select the Unconditionally overwrite existing database or files check box. The existing database files are overwritten. If you do not select this check box, the restore job prevents accidental overwrite of data files by failing the job.

Notes

  • You must rename the databases before restoring them to a disk, either on the same SQL server or a different SQL Server.

  • For IntelliSnap or VSS-enabled backups, the log backups that are restored as *.bak files cannot be applied to the restored full and differential backups. You can discard these files.

  • For each backup type, one file is created per stream. The naming convention of the file is <DB_name>_<Stream#>_<BackupType>_<DayofWeek, Month, Day, Time, Year>.bak, where date and time are the date and time of the database backup.

  • For IntelliSnap, VSS-enabled, or availability group backups, verify that the destination database and the source database have different names.

  • For IntelliSnap or VSS-enabled backups, if the destination database and the source database have the same name, during the Restore to Disk operation, the database to be restored does not get attached to the destination SQL instance. To avoid this situation, manually detach the database on the destination SQL instance before running the Restore to Disk operation.

  • For IntelliSnap or VSS-enabled backups, you can rename the database, but you cannot change the physical names.

Attach to SQL Server (Applies only for VSS and SNAP)

For VSS and IntelliSnap backups, during the Restore to Disk operation, the actual database files (MDF, LDF, NDF) are 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, but physical names should be left intact. All log backups that are restored as *.bak files are not applied to restored Full and Differential backups. This option is applicable for VSS and IntelliSnap backups that are performed using Commvault version 10 or a more recent version.

Verify Only

Select this option to run a restore validation job.

On successful validation, the restore job returns a successful message in the Commvault Event Viewer:

"Query Result [the backup set on file 1 is valid]"

The restore validation job validates the following before running a restore operation:

  • The volumes have sufficient permission to restore the database files.

  • The header fields of the database pages exist at the destination.

  • The restore destination device has sufficient space.

Checksum

Select this option to calculate the checksum on a page during backup as it writes to a disk and then calculates the checksum on the page during restore as it reads from disk. Mismatched values returned during the validation indicate a corrupted database or page corruption on disk.

Note

If you backed up data without selecting the Checksum check box, do not select this check box while restoring that data, because the restore job will fail.

Continue After Error

Select this option to configure the SQL Server to ignore the errors that occur during restores and continue until the job completes.

Note

When data is restored, the Continue After Error check box overrides the Checksum check box.

Leave Database in Restricted User Mode

This option is available only if the bEnableAdvancedRestoreOptionsMSSql setting is set to "true".

Select this option to restore the database to a state in which only the owner of the database can access the database.

Preserve Replication Settings

This option is available only if the bEnableAdvancedRestoreOptionsMSSql setting is set to "true".

Specifies to restore backups of replicated databases. SQL Server replication allows distributing data to various servers throughout an organization. A published database can be restored to a server other than the server on which the database was created. This is relevant only to full backups and if the database was part of a replication implementation. This option is available only when the Recovery type option is selected.

Back up the following replicated databases and their associated system databases regularly:

  • The publication database at the Publisher

    The master and msdb system databases at the Publisher and the publication database must be backed up at the same time.

  • The distribution database at the Distributor

    The master and msdb system databases at the Distributor and the distribution database must be backed up at the same time.

  • The subscription database at each Subscriber

    The master and msdb system databases at the Subscriber and the subscription database must be backed up at the same time.

Verify that, after the restore operation, the master and msdb databases are consistent with the associated system database in terms of replication configuration and settings.

For backup and restore recommendations and strategies, refer to "Back Up and Restore Replicated Databases" on the Microsoft SQL Docs website.

Keep Change Data Capture

This option is available only if the bEnableAdvancedRestoreOptionsMSSql setting is set to "true".

Specifies to restore SQL 2008 or more recent databases, if they were backed up with the Change Data Capture property enabled. With this property enabled, you can query a database for changed data. Querying a database for changed data is useful because all change activity (insert, update, and delete) is captured and applied to SQL tables. The details of the changes are available in an easily consumed relational format. This option does not apply to backups that were VSS-enabled or to IntelliSnap backups.

The keep_cdc option is not applicable for restores with norecovery. The option can only be used during a database restore with recovery.

Loading...