ETERNUS SF AdvancedCopy Manager V16.1 Operation Guide

10.5.4 Backup Using Backup Wizard for SQL Server

This section explains the operations which use the Backup Wizard for SQL Server.

10.5.4.1 Creating Backup Execution Script

The Backup Wizard for SQL Server generates the following scripts:

• Full Backup Execution Script

• Log Backup Execution Script

• Configuration Removal Script

See

Refer to "Create SQL Server Backup Script and Script Execution Environment Setting Using Wizard" in the ETERNUS SF Web Console Guide for information on how to operate these scripts.

Full Backup Execution Script

The full backup execution script specification is shown below:

Table 10.17 Full Backup Execution Script Specification

Script Storage Destination Server	The Database Server on which the Database to be backed up is operating
Storage Directory Path Name	Non-cluster operation: <Program directory when AdvancedCopy Managers agent is installed>\Wizard\bat Cluster operation: <Shared disk for shared data for AdvancedCopy Manager>:\var\opt\swstorage\Wizard\bat
File Name	execute_backup_groupName.bat groupName : Replication group name set by backup wizard. Default: ApplinkForSQLGroup_yyyymmddhhmmss yyyymmddhhmmss is the time and date when creating the script using the wizard.
Execute Form	Non-cluster operation: <Program directory when AdvancedCopy Managers agent is installed>\Wizard\bat\execute_backup_groupName.bat Cluster operation: <Shared disk for shared data for AdvancedCopy Manager>:\var\opt\swstorage\Wizard\bat\execute_backup_groupName.bat
Encoding	MS932
Exit status	0: Exits normally Value except 0: Exits with an error

Note

Execute a script to execute a full backup with database online.

As of when a complete backup is obtained, the log backup files from before this point will not be necessary for restoration. For this reason, when the complete backup execution script is executed, the log backup files of the log backup destination corresponding to the database to be backed up that satisfy the 2 following conditions will all be deleted.

• The log is stored in a folder specified upon execution of the backup wizard

• The replication group name is included in the transaction log backup file name

The output messages of the complete backup execution script are listed below:

Output Message	Description
===== Backup Start. executionCommandName =====	The command to be executed within the script is displayed in executionCommandName.
commandResult	The results of execution of the swsrpbackup_sql command executed within this script will be displayed.
dbName	The name of the database backed up is displayed in dbName.
Database list file already exists.	This will be displayed when a file with the same name as the temporary file used within the script already exists.
execute_backup is successfully completed.	This will be displayed when the script ends with no errors.
execute_backup failed.	This will be displayed when the script ends in an error.

An example of the complete backup execution script is listed below.

@echo off

setlocal

setlocal disabledelayedexpansion

set WORKSPACE_PATH=C:\Win32app\AdvancedCopyManager\var\
set GROUP_NAME=XXXXX
set NOWTIME=%time: =0%
set NOWTIME=%NOWTIME:~0,2%%NOWTIME:~3,2%%NOWTIME:~6,2%%NOWTIME:~9,2%
set DATABASE_LIST_FILE=%WORKSPACE_PATH%%NOWTIME%_DBlist.lst
set INSTANCE_NAME=instance1
set INSTANCE_OPTION=-Xinstance %INSTANCE_NAME%
set SWSTGNODE=
set CLUSTER_OPTION=
set DATABASE_NAME_001=UserDatabase
set LOGBACKUP_PATH_001=C:\LOGBACKUP

set CHECK="swsrpbackup_sql completed"
set CMD=C:\Win32app\AdvancedCopyManager\bin\swsrpbackup_sql %CLUSTER_OPTION% -Xgroup "%GROUP_NAME%" %INSTANCE_OPTION% -T -Xdblist "%DATABASE_LIST_FILE%"
if exist "%DATABASE_LIST_FILE%" (echo Database list file already exists. & exit /b 1)

echo %DATABASE_NAME_001%>>%DATABASE_LIST_FILE%

echo ===== Backup Start. %CMD% =====
%CMD%
if not %ERRORLEVEL% == 0 (echo execute_backup failed. & del %DATABASE_LIST_FILE% >NUL 2>&1 & exit /b 1)

del "%DATABASE_LIST_FILE%" >NUL 2>&1

del "%LOGBACKUP_PATH_001%\%GROUP_NAME%*@*.bk" >NUL 2>&1
echo execute_backup is successfully completed.
exit /b 0

Information

With the full backup execution script, the swsrpbackup_sql command is executed.

In an error occurs in the execution of the full backup execution script, the error message of the command is output. Refer to the ETERNUS SF Messages to take the appropriate action.

Log Backup Execution Script

The log backup execution script specification is shown below:

Table 10.18 Log Backup Execution Script Specification

Script Storage Destination Server	The Database Server on which the Database to be backed up is operating
Storage Directory Path Name	Non-cluster operation: <Program directory when AdvancedCopy Managers agent is installed>\Wizard\bat Cluster operation: <Shared disk for shared data for AdvancedCopy Manager>:\var\opt\swstorage\Wizard\bat
File Name	execute_logbackup_groupName.bat groupName : Replication group name set by backup wizard. Default: ApplinkForSQLGroup_yyyymmddhhmmss yyyymmddhhmmss is the time and date when creating the script using the wizard.
Execute Form	Non-cluster operation: <Program directory when AdvancedCopy Managers agent is installed>\Wizard\bat\execute_logbackup_groupName.bat [-restoreall] Cluster operation: <Shared disk for shared data for AdvancedCopy Manager>:\var\opt\swstorage\Wizard\bat\execute_logbackup_groupName.bat [-restoreall] Parameter Omitted: The backup of the transaction log during normal operation will be executed (disposal of the transaction logs will be performed). The backup of the logs of all databases within the script will be executed. -restoreall : The backup of the latest transaction log prior to execution of "restoration to the latest point" will be executed (disposal of the transaction logs will not be performed). The backup of the logs of all databases within the script will be executed.
Encoding	MS932
Exit status	0: Exits normally Value except 0: Exits with an error

Note

Execute a script to execute a log backup with database online.

In the case that the backup wizard is executed in relation to a database of which the recovery model is "simple", a log backup script will not be generated.

The output messages of the log backup execution script are listed below:

Output Message	Description
===== Log Backup Start. Database:dbName =====	The name of the database for the backup log will be displayed in dbName.
commandResult	The results of executing the sqlcmd command executed within this script will be displayed.
Log backup file already exists.	This is displayed when a file of the same name as the transaction log file to be output already exists.
execute_logbackup is successfully completed.	This will be displayed when the script ends with no errors.
execute_logbackup failed.	This will be displayed when the script ends in an error.
Invalid Argument.	This is displayed in cases when there is a mistake in how the parameters are specified.

An example of the log backup execution script is listed below.

@echo off

setlocal

setlocal disabledelayedexpansion

SET GROUP_NAME=XXXXX
SET NOWDATE=%date:/=%
SET NOWTIME=%time: =0%
SET NOWTIME=%NOWTIME:~0,2%%NOWTIME:~3,2%%NOWTIME:~6,2%%NOWTIME:~9,2%

SET DATABASE_NAME_001=UserDatabase
SET DISP_DATABASE_NAME_001=UserDatabase
SET BACKUP_LOG_PATH_001=C:\LOGBACKUP\%GROUP_NAME%_%DISP_DATABASE_NAME_001%@%NOWDATE%%NOWTIME%.bk
SET SERVER_NAME=.
SET NO_TRUNCATE=
SET RETURN_CODE=0

if not "%1" == "-restoreall" if not "%1" == "" (echo Invalid Argument. 1>&2 & exit /b 3)
if "%1" == "-restoreall" if not "%2" == "" (echo Invalid Argument. 1>&2 & exit /b 3)
if "%1" == "" goto restoreall
if "%1" == "-restoreall" (SET NO_TRUNCATE=WITH NO_TRUNCATE & goto restoreall)
echo Invalid Argument. 1>&2 & exit /b 3

:restoreall
SET DATABASE_NAME=%DATABASE_NAME_001%
echo ===== Log Backup Start. Database:%DISP_DATABASE_NAME_001% =====
if exist "%BACKUP_LOG_PATH_001%" (
echo Log backup file already exists.
SET RETURN_CODE=1
) else (
sqlcmd -b -S %SERVER_NAME%\instance1 -Q "BACKUP LOG [%DATABASE_NAME%] TO DISK = '%BACKUP_LOG_PATH_001%' %NO_TRUNCATE%"
)
if not %ERRORLEVEL% == 0 ( SET RETURN_CODE=1 )

if %RETURN_CODE% == 0 (
echo execute_log_backup is successfully completed.
) else (
echo execute_log_backup failed.
)
exit /b %RETURN_CODE%

Information

In the log backup execution script, execute sqlcmd command.

If an error occurs in the execution of the log backup execution script, it may be possible to resolve the error by taking the actions shown below.
For this reason, it is recommended that these actions be performed when the error first occurs.

Execute the following command immediately after executing the log backup execution script to obtain the exit status when the log backup execution script is executed.

echo %ERRORLEVEL%

According to the exit status obtained, take the following actions:

• Error in log backup execution script: (Exit status: 1)
  As the error message of the SQL Server is being output, correspond accordingly by referring to the "SQL Server Manual".

• Error in log backup execution script: (Exit status: 3)
  Confirm be errorless to the parameters in the script.

Configuration Removal Script

Configuration removal script specification is shown below:

Table 10.19 Configuration Removal Script Specification

Script Storage Destination Server	The Database Server on which the Database to be backed up is operating
Storage Directory Path Name	Non-cluster operation: <Program directory when AdvancedCopy Managers agent is installed>\Wizard\bat Cluster operation: <Shared disk for shared data for AdvancedCopy Manager>:\var\opt\swstorage\Wizard\bat
File Name	delete_backup_groupName.bat groupName : Replication group name set by backup wizard. Default: ApplinkForSQLGroup_yyyymmddhhmmss yyyymmddhhmmss is the time and date when creating the script using the wizard.
Execute Form	Non-cluster operation: <Program directory when AdvancedCopy Managers agent is installed>\Wizard\bat\delete_backup_groupName.bat Cluster operation: <Shared disk for shared data for AdvancedCopy Manager>:\var\opt\swstorage\Wizard\bat\delete_backup_groupName.bat
Encoding	MS932
Exit status	0: Exits normally Value except 0: Exits with an error

The output messages of the configuration removal script are listed below:

Output Message	Description
===== Cancel Backup Start : executionCommandName =====	The command that will execute the cancellation of backup configurations within the script will be displayed in the executionCommandName.
===== Delete Backup Start : executionCommandName =====	The command that will execute the deletion of backup configurations within the script will be displayed in the executionCommandName.
delete_backup is successfully completed.	This will be displayed when the script ends with no errors.
delete_backup failed.	This will be displayed when the script ends in an error.

An example of the configuration removal script is listed below:

@echo off

setlocal

setlocal enabledelayedexpansion

set CMDPATH=C:\Win32app\AdvancedCopyManager\bin\
set GROUP_NAME=XXXXX

set SWSTGNODE=
set ORGVOL_001=g1d321p1@E01DBS01WR2DE64
set DSTVOL_001=g1d324p1@E01BKS01WR2DE64
set ORGVOL_002=g1d322p1@E01DBS01WR2DE64
set DSTVOL_002=g1d434p1@E01BKS01WR2DE64
set CHECK_CANCEL="GROUP=%GROUP_NAME% swsrpcancel completed"
set CHECK_DELVOL="swsrpdelvol completed"
set ERRORMSG2605=swsrp2605
set ERRORMSG2637=swsrp2637
set CMD_CANCEL="%CMDPATH%swsrpcancel -Xgroup %GROUP_NAME% 2>&1"
set CMD_DELVOL_001="%CMDPATH%swsrpdelvol %ORGVOL_001% %DSTVOL_001% 2>&1"
set CMD_DELVOL_002="%CMDPATH%swsrpdelvol %ORGVOL_002% %DSTVOL_002% 2>&1"
echo ===== Cancel Backup Start. %CMD_CANCEL% =====
set RET=
for /f "usebackq tokens=*" %%i in (`%CMD_CANCEL%`) do (if "%%i"==%CHECK_CANCEL% (set RET=OK) else (call :CHECK %ERRORMSG2605% "%%i"))
if not "%RET%"=="OK" echo delete_backup failed. & exit /b 1

echo ===== Delete Backup Start. %CMD_DELVOL_001% =====
set RET=
for /f "usebackq tokens=*" %%i in (`%CMD_DELVOL_001%`) do (if "%%i"==%CHECK_DELVOL% (set RET=OK) else (call :CHECK %ERRORMSG2637% "%%i"))
if not "%RET%"=="OK" echo delete_backup failed. & exit /b 2

echo ===== Delete Backup Start. %CMD_DELVOL_002% =====
set RET=
for /f "usebackq tokens=*" %%i in (`%CMD_DELVOL_002%`) do (if "%%i"==%CHECK_DELVOL% (set RET=OK) else (call :CHECK %ERRORMSG2637% "%%i"))
if not "%RET%"=="OK" echo delete_backup failed. & exit /b 2

echo delete_backup is successfully completed.
exit /b 0

:CHECK
set CHECK_STR=%2
for /f "tokens=1" %%i in (%CHECK_STR%) do (if %%i==%1 (set RET=OK) else (echo %CHECK_STR:"=%))
exit /b

Information

The following command will be executed with the configuration removal script.

• swsrpcancel command

• swsrpdelvol command

If an error occurs in the execution of the configuration removal script, it may be possible to resolve the error by taking the actions shown below. For this reason, it is recommended that these actions be performed when the error first occurs.

Execute the following command immediately after executing the configuration removal script to obtain the exit status when the configuration removal script is executed.

echo %ERRORLEVEL%

According to the exit status obtained, take the following actions:

• Error in configuration removal script : (Exit status: 1):
  As the swsrpcancel command will be output, correspond accordingly by referring to the execution command line that is output on the screen and the ETERNUS SF Messages.

• Error in configuration removal script : (Exit status: 2):
  As the swsrpdelvol command will be output, correspond accordingly by referring to the execution command line that is output on the screen and the ETERNUS SF Messages.

10.5.4.2 Execution of Backup

Using the script created in "10.5.4.1 Creating Backup Execution Script" can back up SQL Server databases.

• Full Backup Execution Script
  The script that will execute a full backup of the user database.

• Log Backup Execution Script
  The script that will perform a backup of the transaction log of the user database.

Note

In the case that the Recovery Models is "Full" of "Bulk-Logged", regularly perform a log backup. The disposal of the transaction logs of the SQL Server database will be performed by performing a backup of the logs. With the disposal of the transaction log, it is possible to prevent the region of the log file from being insufficient (refer to documentation of the SQL Server for details on the disposal of the transaction log).

Registering the script to the Scheduler of this product makes it easy to back up SQL Server databases.

For the method to register to Scheduler, refer to "Chapter 11 Scheduler".

Not only registering the script to the Scheduler of this product but also registering it to the Task Scheduler of the operating system or manually executing the script can back up SQL Server databases.

The procedures for automatically executing backup by registering this with the task scheduler of the OS and the procedures for when executing backup manually will be explained below.

Executing Backup Automatically Using Task Scheduler

1. Copy the script file that was generated in "10.5.4.1 Creating Backup Execution Script" to any location.

2. Log into the backup source database server as the AdvancedCopy Manager command execution user.

   See

   Refer to "Creating User Accounts for Executing Command" in the ETERNUS SF Installation and Setup Guide for information on the user which executes the command.

3. Start Task Scheduler.
   The methods to start this are as follows:

   • Windows Server 2008:

     System and Maintenance > Administrative Tools > Task Scheduler

   • Windows Server 2008 R2:

     System and Security > Administrative Tools > Task Scheduler

   • Windows Server 2012:

     Start > Administrative Tools > Task Scheduler

4. In Task Scheduler, configure the following:

   • Task name

   • Date/time task will start

   • Configure the script file which was copied in step 1 in the program which is to be executed

Executing Backup Manually

1. Copy the script file that was generated in "10.5.4.1 Creating Backup Execution Script" to any location.

2. Log into the backup source database server as the AdvancedCopy Manager command execution user.

   See

   Refer to "Creating User Accounts for Executing Command" in the ETERNUS SF Installation and Setup Guide for information on the user which executes the command.

3. Start the command prompt, and execute the script file that was copied in step 1. Refer to "10.5.4.1 Creating Backup Execution Script" for details.

10.5.4.3 Backup to Tape(When Performing Backup to Tape)

When backing up the backup destination volume to a tape device, perform a backup of all files within the data file region and the transaction log region.

Either selection of backup in units of files and RAW backup are possible for the backup method.

10.5.4.4 Backup of the System Database

When the following configurations that apply changes to the system database have been performed, perform a backup of the system database:

• When applying a service pack or correction module

• Immediately after creating an instance

• Making changes to the settings of the server configuration

• When creating / changing / deleting objects of the server level

• When creating / changing / deleting databases

• When adding / deleting a file group or data file

• When creating or deleting a backup device

• When adding / changing / deleting a login

• Dispersed queries of such as link servers or remote login and configurations for remote procedure calls

• When adding / changing / deleting jobs

• When adding / changing / deleting warnings

• When adding / changing / deleting operators

• Operations for saving the SQL Server Integration Services package created with the DB import / export wizard to the SQL Server

• When adding / changing / deleting replication configurations

• When creating or deleting user templates

Refer to the documentation of the SQL Server for details on the backup method.