Perform the following preparations before the backup of SQL Server is started:
Configure the environment for SQL Server
General preparation
Prepare a device map 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 "Designing a Backup and Restore Strategy" in the "SQL Server Books Online".
Perform the processing described in "7.4 Preparations", noting the following points:
Register all the database volumes and register the backup volumes which will become copy destinations.
Configure a database volume and a backup volume to be a source volume and a replica volume, respectively (do not reverse a source volume and a replica volume).
Configure the original server which operates the database to be an operation server when you use the backup server (specify "ORG" for the argument of the -o option in the replication volume information setting command).
Specify the -u option if you do not use the Advanced Copy to perform the file restore. The direction of the copy can be limited to one direction from the source volume (i.e., the database volume) to the replica volume (i.e., the backup volume).
Set the replication volume information for all the target database volumes in AdvancedCopy Manager. Execute the command from the business server (DB-SVR).
C:\>set SWSTGNODE=nodeAGT C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpsetvol -o ORG g1d1p1 g1d11p1@BKUP-SVR swsrpsetvol completed C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpsetvol -o ORG g1d2p1 g1d12p1@BKUP-SVR swsrpsetvol completed C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpsetvol -o ORG g1d3p1 g1d13p1@BKUP-SVR swsrpsetvol completed C:\> |
It is necessary to create a file (called a device map file) which describes the relation between database volumes and backup volumes, which is used by the SQL Server backup execution command. The SQL Server backup execution command copies data onto the backup volumes specified for a device map file.
Place a device map file in an arbitrary place on the server which operates the database (i.e., the original server). The backup destination of the database volume is declared by specifying this file when backup is performed.
Describing a device map file
The following shows a descriptive example of a device map file.
# Device map file for AdvancedCopy Manager SQL Server Support Function # database volume names backup volume names # (original volume names) (replica volume names) # Database DB01,DB02,DB03 g1d1p1 g1d11p1@BKUP-SVR # D: primary data file g1d2p1 g1d12p1@BKUP-SVR # E: secondary data file g1d3p1 g1d13p1@BKUP-SVR # F: log file # Database DB04,DB05,DB06 g1d4p1 g1d14p1@BKUP-SVR # G: primary data file g1d5p1 g1d15p1@BKUP-SVR # H: secondary data file : |
The following describes the rules for creating a device map file:
Describe a database volume and a corresponding target backup volume in one line. Separate a database volume and a backup volume with one or more blank or tab characters. One or more blank or tab characters may be included between the beginning of a line and a database volume name or between t0he end of a backup volume name and the end of a line (which is denoted by a newline character).
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.
A blank line may be included in a file.
The portion from the symbol "#" to the end of a line is interpreted as a comment.
You cannot specify more than one target backup volume for each database volume in one device map file. If you do, only the information in the first line will be valid. While the whole device map file will be read, the system will ignore the redundant lines after the first line. You must prepare multiple device map files if you perform a multiple generation backup.
A device map file may include descriptions of database volumes other than the processing target database. (Refer to the description example provided in the beginning of this chapter for details of this.)
Describe all the database volumes of a multivolume database in one device map file. (The description cannot be extended over multiple files.)
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 ""DB name".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.
