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
Configuring Environment for Backup Operation Server
Setting Operation Type for Device
Setting Backup Policy
Customizing Pre-processing and Post-processing
Prepare a device map file (only required if the backup destination is specified)
Record the output path of a metadata file
Prepare the automated backup
The following example is used as an illustration in the following sub sections.
Figure 10.8 SQL Server Backup Environment Example
Server Type | Server Name | comment |
|---|---|---|
Management Server | MGR-SVR | |
Managed Server | DB-SVR |
|
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 |
E: | g1d2p1 |
F: | g1d3p1 |
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 on this, refer to "Recovery Models and Transaction Log Management" in the "SQL Server Books Online".
Refer to "3.4.1 Starting Services".
Refer to "3.4.2 Starting Web Console".
Refer to "3.4.3 Registering Managed Server".
Refer to "3.4.6 Setting Operation Type for Device".
Point
When you register transaction volumes, register all the volumes on which database files are placed.
Refer to "3.4.7 Setting Backup Policy".
Point
Set the same backup policies for all the volumes on which database files are placed. The management of backup history information is simplified by making all the backup policies the same. Note that the backup can still be executed even if the backup policies are different.
If you specify the backup volumes which become copy destinations, create a device map file. For details of this, refer to "3.4.9 Preparing Device Map File".
Point
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.
At the time of backup execution, the "metadata", which includes information such as path name, size and file type, etc. of the database files in the database being backed up is saved from SQL Server. AdvancedCopy Manager saves metadata in a metadata file in a backup volume.
A metadata file is required to be restored to a database volume at the time of restoration of database files. Since it becomes indispensable when executing the swstrestore_sqlsvr command, it is necessary to record the absolute path of a metadata file in advance.
The metadata file is saved with the name ""DB name".swst-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", the absolute path of a metadata file becomes "D:\SQLSVR\DB01.swst-dmp".
Even though the database file with file identification number 1 is a primary data file, it is a good idea to check using the following procedure to make sure it is correct:
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 it is necessary to ensure that there is enough capacity for storing a metadata file in a transaction volume, the size of a metadata file is very small, so there is really no necessity for taking this into consideration. The size of a metadata file depends on the number of files of a database. For example, it is about 20KB in a database containing 10 files.
You can automate the backup using the job creation wizard of SQL Server or Systemwalker Operation Manager. Automate the backup using commands provided by AdvancedCopy Manager.
