Perform the following preparations before the backup of SQL Server is started:
Configure the environment for SQL Server
Starting Services
Starting Web Console
Registering Managed Server
Fetching device information on a Managed Server
Creating Replication Group
Preparing Device Map File (When Not Creating a Replication Group)
Performing Database List File
Record the output path of a metadata file
Prepare the automated operation of backup
The following example is used for explanation in the following subsections.
Figure 10.10 SQL Server Backup Environment Example
Server Type | Server Name | Comment |
|---|---|---|
Management Server | MGR-SVR | |
Managed Server | DB-SVR |
|
Managed Server | BKUP-SVR | Backup server |
Database Name | Database File Name | Database File Type |
|---|---|---|
DB01 | D:\SQLSVR\DB01.mdf | Primary data file |
E:\SQLSVR\DB01.ndf | Secondary data file | |
F:\SQLSVR\DB01.ldf | Transaction log file | |
DB02 | D:\SQLSVR\DB02.mdf | Primary data file |
E:\SQLSVR\DB02.ndf | Secondary data file | |
F:\SQLSVR\DB02.ldf | Transaction log file | |
DB03 | D:\SQLSVR\DB03.mdf | Primary data file |
E:\SQLSVR\DB03.ndf | Secondary data file | |
F:\SQLSVR\DB03.ldf | Transaction log file |
Drive Letter | AdvancedCopy Manager Device Name |
|---|---|
D: | g1d1p1@DB-SVR |
E: | g1d2p1@DB-SVR |
F: | g1d3p1@DB-SVR |
Set up SQL Server according to database backup configuration instructions. If you apply logs at the time of recovering a database, set the "recovery model" of a database to "Full" or "Bulk-Logged". For more information, refer to "Recovery Models and Transaction Log Management" in the "SQL Server Books Online".
Refer to "7.4.1 Starting Service".
Refer to "7.4.2 Starting Web Console".
Refer to "7.4.3 Registering Managed Server".
Set a pair of a transaction volume to which SQL Server database is allocated and its backup destination volume as copy source and destination volumes of the replication management function. At that time, register replication volumes on the groups of replication management function (hereinafter called "Replication group").
Perform this task while being careful of the following.
Register all database volumes and copy destination backup volumes.
Specify a partition (gXdYpZ) for volume name. Do not specify a LUN (gXdY).
Set the database volume as the replication source volume and then set the backup volume as the replication destination volume (do not set the replication destination volume first and then set the replication source volume).
When performing backup server operation, set the server on which to operate the database (the replication source server) as the control server (specify "ORG" for the parameter of the o option with the replication volume information configuration command).
Specify the u option when not using AdvancedCopy for file recovery processing. It is possible to limit the direction of the copying that can be implemented to only be from the replication source volume (database volume) to the replication destination volume (backup volume).
Apart from this, refer to "7.4.7.1 Conditions for Replication Volumes That Make Up Group" for conditions of replication volumes that configure replication groups.
Create a replication group by the swsrpsetvol command.
The information for the created group can be referred to by the swsrpvolinfo command.
In backup to the backup server, specify volumes on other Managed Server in the format of "volume name@Managed Server name" so that inter-server replication can be set. Also, set so that the business server can be an operated server.
Example
Configure replication volume information and a replication group in relation to all targeted database volumes. Execute the command from the business server (DB-SVR).
C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpsetvol -o ORG -Xgroup grp1 g1d1p1 g1d11p1@BKUP-SVR swsrpsetvol completed C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpsetvol -o ORG -Xgroup grp1 g1d2p1 g1d12p1@BKUP-SVR swsrpsetvol completed C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpsetvol -o ORG -Xgroup grp1 g1d3p1 g1d13p1@BKUP-SVR swsrpsetvol completed C:\>
Multiple-generation Backup Operation
When performing backups of databases in multiple generations, create a replication group for each generation.
Example
To create a replication group that performs backup of the transaction volume (g1d1p1, g1d2p1, g1d3p1) that configures the database (DB01, DB02, DB03) to a backup volume of 2 generations (1st generation <g1d11p1, g1d12p1, g1d13p1>, 2nd generation <g1d14p1, g1d15p1, g1d16p1>) when setting up the backup.
C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpsetvol -o ORG -Xgroup grp2 g1d1p1 g1d14p1@BKUP-SVR swsrpsetvol completed C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpsetvol -o ORG -Xgroup grp2 g1d2p1 g1d15p1@BKUP-SVR swsrpsetvol completed C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpsetvol -o ORG -Xgroup grp2 g1d3p1 g1d16p1@BKUP-SVR swsrpsetvol completed C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpvolinfo -L Server Original-Volume Size Replica-Volume Size Copy Op-Server Group DB-SVR g1d1p1@DB-SVR 7.5 Gbyte g1d11p1@BKUP-SVR 7.5 Gbyte bi-direction original grp1 DB-SVR g1d2p1@DB-SVR 7.5 Gbyte g1d12p1@BKUP-SVR 7.5 Gbyte bi-direction original grp1 DB-SVR g1d3p1@DB-SVR 7.5 Gbyte g1d13p1@BKUP-SVR 7.5 Gbyte bi-direction original grp1 DB-SVR g1d1p1@DB-SVR 7.5 Gbyte g1d14p1@BKUP-SVR 7.5 Gbyte bi-direction original grp2 DB-SVR g1d2p1@DB-SVR 7.5 Gbyte g1d15p1@BKUP-SVR 7.5 Gbyte bi-direction original grp2 DB-SVR g1d3p1@DB-SVR 7.5 Gbyte g1d16p1@BKUP-SVR 7.5 Gbyte bi-direction original grp2
To perform a backup of the database (DB01, DB02, DB03) of the first generation, specify the database (DB01, DB02, DB03) and the replication group (grp1) when executing the backup.
To perform a backup of the database (DB01, DB02, DB03) of the second generation, specify the database (DB01, DB02, DB03) and the replication group (grp2) when executing the backup.
When performing a backup while specifying a replication group, it is not necessary to perform this task.
Point
Use of the device map file in cases where the device map file specified backup operation continuing from the previous version is carried out.
For other cases, carry out replication group specified backup operation. Furthermore, the backup history information can be confirmed with the swsrphistory_sql command at the replication group specified backup operation.
Generate a device map file by referring to "3.4.9 Preparing Device Map File" and the items to take note of below.
Note
Specify the backup volume of a backup server in the form of "volume name@server name". Only the form "volume name" can be used for the database volume. It should also be noted that the form "volume name@database server name" cannot be used for the transaction database name.
Specify a partition (gXdYpZ) for volume name. Do not specify a LUN (gXdY).
Where the database consists of multiple volumes, it is necessary to state all the definitions pertaining to the database volumes in one device map file.
The database list file indicated by the swsrpbackup_sql command is generated.
Without using the database list file, where backup of the database indicated at the operand of swsrpbackup_sql command is carried out, this procedure is not necessary.
The format of the database list file is as follows:
List the database name in each row of the text file.
List only 1 database name in each row.
List the database name from the beginning of the rows.
Blank lines will be ignored.
It is possible to specify a maximum of 128 databases.
The names shall be listed using CR+LF for line breaks.
Example:
DB01 DB02 DB03
At the time of backup execution, the "metadata" which includes information, such as path name, size and file type, etc. of the database files of the database for backup is saved from SQL Server. AdvancedCopy Manager saves this in a metadata file in the backup volume.
A metadata file is restored to the database volume at the time of restore of the database files.
Since this file is essential when executing the SQL Server restore execution command (swsrprestore_sql), it is necessary to record the absolute path of the metadata file in advance.
The metadata file is saved with the name of "dbName.swsrp-dmp" in the folder where the database file with file identification number (FILE_ID) 1 exists. For example, if the absolute path of the database file with file identification number 1 of a database DB01 is "D:\SQLSVR\DB01_data.mdf", then the absolute path of a metadata file will be "D:\SQLSVR\DB01.swsrp-dmp". Even though the database file with file identification number 1 is a primary data file, you should confirm it with the following procedure:
Perform the Transact-SQL command "USE database name EXEC SP_HELPFILE".
Look for the record with which the column "file id" is 1 as a result of this execution. The content of the column "filename" of the record is the absolute path of the database file with file identification number 1.
Point
Although you need to ensure that there is enough space to store a metadata file in the transaction volume, the size of a metadata file is very small, so there is really no need to check this. The size of a metadata file depends on the number of files of a database. For example, it is about 20KB in a database with 10 files.
You can automate the backup by using the job creation wizard of SQL Server or Systemwalker Operation Manager. In either case, this is achieved with commands provided by AdvancedCopy Manager.
