This sub section explains the necessary operating procedures for restoration using the Restore Wizard for SQL Server.
The flow of the restore operation is shown below.
Figure 10.23 Flow of Restoration Operation
Check the following conditions.
ETERNUS Disk storage system
The ETERNUS SF AdvancedCopy Manager for Microsoft SQL Server license is registered.
Check the backup
The backup is located on the head region of the ETERNUS Disk storage system.
Point
In the case of D2D2T (Disk to Disk to Tape) operation, and in the case that a failure has occurred in the backup destination region and a backup does not exist on the disk, the backup data is restored from the tape device to the backup destination region.
The database backup of the SQL Server has been obtained with the complete backup execution script generated with the backup wizard.
In the case that the configuration of the database of the SQL Server has been changed, a new backup has been created after the change has been made.
Refer to "10.5.7 Changing Configuration" for details on the tasks required in order to make changes to the configuration of the database.
Point
Execute the configuration removal script and release the backup settings registered with the configuration prior to the changes. After the configuration of the database has been changed, it is necessary to generate a complete backup execution script with the Backup Wizard for SQL Server and execute this script that has been generated.
Checking Volume
There exists no processes that are accessing the backup source / backup destination volumes of the database subject to restoration.
Whether explorer or the command prompt is accessing the targeted volume.
Whether an application accessing the targeted database is in operation.
Note
There may be cases when the restoration ends in a failure due to other processes using the backup source / backup destination volumes of the database subject to restoration. When re-executing the restoration wizard after removing the cause of the failure, the only restoration that may be selected is "Backup Point".
In order to restore to the latest point, refer to "10.4.2.2 Performing Restore".
Checking SQL Server
It is possible to use the SQL Server.
In cases when the system database (master) cannot be used, the SQL Server will be unavailable. When the system database has been damaged, it will be necessary to restore the system database prior to executing the Restore Wizard for SQL Server.Refer to the documentation of the SQL Server for details on the restoration of the system database.
When using SQL Server 2012 or later, the sysadmin server role has been added to the Local System (NT AUTHORITY\SYSTEM) account.
Confirm this with the following steps from the Management Studio of the SQL Server.
Develop the SQL Server instance that can be used with the [Object Explorer] and double click on LOCAL SYSTEM (or NT AUTHORITY\SYSTEM) from [Login] under [Security].
From the [Login Properties] window, select the [Server Role] option.
Confirm that [sysadmin] has been selected in the [Server Role] list.
Note: If [sysadmin] had not been selected, select [sysadmin] and click OK.
The default database of the user executing the restoration (the authentication user input in the "authentication information input screen" listed under "Restoration of the SQL Server Database with the Wizard" in the ETERNUS SF Web Console Guide) is "master".
In cluster operation, tempdb is not included in the same volume as a database to be restored.
If included in the same volume as a database to be restored, move tempdb to other volume.
For the method to move tempdb, refer to "Move Database File" in the SQL Server Books Online.
Confirmation when performing restoration to the latest point
The recovery model is "Full" or "Bulk-Logged".
Information
When the recovery model is "Simple", it is only possible to perform restoration with "restoration to backup point" and it will not be possible to perform "restoration to latest point".
When the recovery model is "Bulk-Logged", bulk operations have not been executed in relation to the SQL Server database after the full backup.
Information
In the case that bulk operations have been executed in relation to the SQL Server database after the full backup, it is possible to perform restoration only with "restoration to backup point".
The log backups taken after the latest full backup are all in order.
A backup of the latest transaction log has been obtained right before the execution of the restoration wizard.
Information
When the log backup execution script is executed with the -restoreall option, it will be possible to obtain a backup of the latest transaction log, and it is possible to perform restoration with "restoration to most recent point".
Note
In cases when a backup of the transaction log has not been obtained after the most recent complete backup, the only method that can be selected for the restoration method is "Backup Point".
Check the server/storage
The environment listed in "10.5.3 Prerequisites" has been configured
The replication volume information of the AdvancedCopy Manager is configured as a combination of the volume to be backed up and the backup destination volume of the SQL Server database to be restored.
The procedures for confirming whether the replication volume information of the AdvancedCopy Manager is configured are listed below.
Log into the backup source Management Server as the AdvancedCopy Manager command execution user.
Bring up the command prompt.
Execute "<Program directory when AdvancedCopy Manager's manager is installed>\ACM\bin\swsrpvolinfo -h serverName" from the command prompt. For serverName, specify the database server name.
The row for Original-Volume in the command execution result indicates the device name corresponding to the replication source volume while the row for Replica-Volume indicates the device name corresponding to the replication destination volume. Confirm that the device name corresponding to the volume to be backed up and the device name corresponding to the backup destination volume of the SQL Server database being restored is displayed as a pair in the row for Original-Volume and the row for Replica-Volume.
See
For information swsrpvolinfo command, refer to "13.3.1.2 swsrpvolinfo (Replication Volume Information Display Command)".
No fault in the volumes used for restore is found.
Use the ETERNUS Web GUI to check the status of the restore source and destination volumes.
If any fault is found in the volumes, refer to "10.5.8.1 Corrective Action for Hardware Fault" to take corrective action.
When performing restoration to the latest point, obtain a backup of the end of transaction log with the following procedures. When restoring to a backup point, this step is not needed.
Login to the database server.
The required login privileges are as follows:
Windows Roles
When domain authentication is used:: (It is necessary to assign all of the following roles to the account.)
Administrators, Domain Admins, Domain Users, Enterprise Admins, Group Policy Creator Owners, Schema Admins
When local accounts authentication is used:
Administrators
SQL Server Roles
sa(SystemAdmin privileges)
Backup of the end of transaction log.
The log backup execution script is executed with the -restoreall option.
For information of the log backup execution script, refer to "Log Backup Execution Script".
Point
The transaction logs of all databases within the backup execution script will be backed up.
When performing a backup of only part of the database, backup the tail end of the transaction log by using such as SQL Server Management Studio or Transact-SQL.
Backup by referring to either "Backup of Tail End of Log (SQL Server)" or "How to Backup the Tail End of a Transaction Log (SQL Server Management Studio)" in the "SQL Server Books Online".
Note
When the recovery model is "Simple", it will only be possible to execute "restoration to a backup point". (It will not be possible to execute a backup of the transaction log.)
In the case that a failure has occurred in the backup destination region and a backup does not exist on the disk, restore the data of the data file region and the transaction log region, backed up onto a tape device, to the backup destination volume.
Change to a default database.
Connect to the instance in which the database subject to restoration is included and change the default database of the user executing the restoration (authorized user input in the "Input of Authentication page" explained in "Restore SQL Server Database Using Wizard" in the ETERNUS SF Web Console Guide) to "master" by following the steps listed below. However, if this has already been set to "master", it will not be necessary to make changes.
Log on to the database server with the operation account.
Start the SQL Server Management Studio.
From the object explorer, go through Security > Login and right-click on User, and then click on Property.
Click on Selection of Page > General and change Default Database to "master".
End the SQL Server Management Studio.
Execute restore using the Restore Wizard for SQL Server provided on the Web Console.
Refer to "Restore SQL Server Database Using Wizard" in the ETERNUS SF Web Console Guide for the operating procedure.
Restore process status can be checked in the Web Console Job Status pane. Refer to "Job Status Pane" in the ETERNUS SF Web Console Guide for information on the Job Status pane display.
When a restore is executed using the Restore Wizard for SQL Server, the following information is displayed in the Job Status pane.
Item | Display Contents |
|---|---|
Action | Restore Wizard for SQL Server |
Status | Executing |
Results | - |
Item | Display Contents | |
|---|---|---|
Action | Restore Wizard for SQL Server | |
Status | Completed | |
Results | Normal case | Success |
Abnormal case | Failed | |
After completing a restore using the Restore Wizard for SQL Server, confirm that the SQL Server database has been correctly restored using the transaction application or SQL Server Management Studio.
When the default database has been changed to "master" with "10.5.5.4 Changing Default Database", return this to the default database prior to making the change.
