Microsoft SQL Server tab field descriptions

Microsoft SQL Server Redirection tab field descriptions describe the restore options that are available when you restore SQL backups from Backup Exec images.

Table: Restore options on the Microsoft SQL Server tab

Item

Description

Recovery completion state

  • Leave database operational. No additional transaction logs can be restored.

    (With Recovery)

    Select this option to restore the last database, differential, or log backup in the restore sequence. This action causes the restore operation to roll back all incompleted transactions. After the recovery operation, the database is ready for use. If Leave database operational is not performed, the database is left in an intermediate state and is not usable.

    If Leave database operational is selected when an intermediate backup is applied, you cannot continue to restore backups.

    You must restart the restore operation from the beginning.

  • Leave database nonoperational but able to restore additional transaction logs

    (No Recovery)

    Select this option during a restore if you have additional differential or transaction log backups to be restored in another restore job.

  • Leave database read-only and able to restore additional transaction logs

    (Standby)

    Select this option during transaction log and database restore to create and maintain a standby database. See your SQL documentation for information on standby databases.

Replace databases or filegroups

Select this checkbox to replace a database or filegroup, even if another database or filegroup with the same name already exists on the server. If Replace Databases or Filegroups is not specified for a restore, SQL performs a safety check. This check ensures that a different database or filegroup is not accidentally overwritten. Refer to your SQL documentation for more information about the safety check that occurs when the REPLACE option is not selected.

Automate master database restore

Enable NetBackup to stop SQL so that the master database can be restored. All existing users are logged off, and SQL Server is put into single-user mode.

When this option is selected, only the master database can be restored; if this option is selected for any other database, those jobs fail.

NetBackup must have access to the following SQL registry keys:

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server

HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer

Without this access, a restore to the default directory may not work. Also, the option Automate master database restore does not work.

To ensure that NetBackup has access rights, verify that the user account of the NetBackup Client Service has administrator rights to the Windows server that the SQL instance is installed on.

Consistency check after restore

To recover the database after the restores are complete and when you selected the Leave database operational option, you need to select a consistency check option.

The following consistency checks are available:

  • Full check, excluding indexes.

    Select this option to exclude indexes from the consistency check. If indexes are not checked, the consistency check runs significantly faster but is not as thorough. Only the data pages and clustered index pages for each user table are included in the consistency check. The consistency of the nonclustered index pages is not checked.

  • Full check, including indexes.

    Select this option to include indexes in the consistency check. Any errors are logged. This option is selected by default.

  • Physical check only (SQL 2000 only).

    Select this option to perform a low overhead check of the physical consistency of the SQL Server 2000 database. This option only checks the integrity of the physical structure of the following: the page and the record headers, the consistency between the pages' object ID and index ID, and the allocation structures.

  • None.

    Select this option to perform sequential restores. Do not run a consistency check after a restore until all sequential restores are done. If a consistency check is selected during a restore, the restore completes but the consistency check is not performed. Check the job log for this information.

Alternate drive for restoring database files

Select a drive to which SQL database files can be restored. Use this option if the drive where one or more of the database files previously resided no longer exists.

Note:

Do not select the Alternate drive option when you restore filegroups. Filegroups must be restored to the same drive letter and path that they were backed up from.

In the backup set, SQL Server stores the physical file names of the files that make up the database. The physical file name includes the directory path. For the logical file, "pubs," the physical file name is stored as E:\MSSQL7\DATA\pubs.mdf. If the database is restored later, SQL uses these same physical file names for the target of the restore. During a restore, NetBackup automatically creates any necessary subdirectories that do not exist.

However, the behavior is different if the drive where one or more of the database files previously resided no longer exists. Then NetBackup moves those files to their original directory path, but on the alternate drive specified.

Using the same example, if drive C: is specified, then the file with the original directory path of E:\MSSQL7\DATA\pubs.mdf is restored to C:\MSSQL7\DATA\pubs.mdf.

If no alternate drive is specified in this situation, the job fails.

Restore to alternate drive

You can restore to an alternate drive in one of the following ways:

  • Only when original drive does not exist

    Select this option to restore all database files to their original directory path on the alternate drive. The restore is performed only if the drive where they originally resided exists. To make this option available, select a drive letter in Alternate drive for restoring database files list.

  • Even when original drive does exist

    Select this option to restore all database files to their original directory path on the alternate drive. The restore is performed even if the drive where they originally resided exists. To make this option available, select a drive letter in Alternate drive for restoring database files list.

Restore all database files to the target instance's data location

Select this checkbox to restore files to the default data and log directories of the destination instance. For example, to restore a database to a different instance of SQL. This option moves the database files to the correct location for the new instance.

Note:

Do not select the Restore all database files to the target instance's data location option when restoring filegroups. Filegroups must be restored to the same drive letter and path that they were backed up from.

If this option is not selected, then the files are restored to the directory that the master database is in.

Point in time log restore

Select this checkbox to restore transactions from a transaction log up to and including a point in time in the transaction log. After the point in time, recovery from the transaction log is stopped.

Select the part of the date you want to change. Then enter a new date or click the arrow to display a calendar from which you can select a date.

Select the part of the time you want to change, and then enter a new time or click the arrows to select a new time.

Restore log up to named transaction (SQL 2000)

Select this checkbox to restore transactions from a transaction log up to a named transaction (or named mark) in the transaction log. After the named transaction is restored,the recovery from the transaction log is stopped. The named transactions are case sensitive.

You can restore transactions in one of the following ways:

  • Include the named transaction

    Select this checkbox to include the named transaction in the restore; otherwise the restore stops immediately before the named transaction is restored.

  • Found after

    Select this checkbox to specify a date and time after which the restore operation is to search for the named transaction. For example, consider that you specified a restore from a log up to the named transaction "AfternoonBreak." This transaction was found after 6/02/2007, 12:01 P.M. In that case, the restore operation does not search for "AfternoonBreak" until after that time.