Perform the following preparations before the backup of SQL Server is started:
Configure the environment for SQL Server
General preparation
Create the transaction volume locking specification file for backups
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 8.8 SQL Server backup environment example
Server type | Server name | comment |
|---|---|---|
Storage Management Server | MGR-SVR | |
Storage 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 "Designing a Backup and Restore Strategy" in the "SQL Server Books Online".
Perform the steps described in "4.4 Preparing to Start the Backup", noting the following points:
When you register transaction volumes, register all the volumes on which database files are placed.
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.
Register all the target database volumes as transaction volumes, and set the backup policies.
C:\>set SWSTGNODE=nodeAGT C:\>C:\Win32App\AdvancedCopyManager\bin\swstdevinfoset -t g1d1p1 swstdevinfoset completed C:\>C:\Win32App\AdvancedCopyManager\bin\swstdevinfoset -t g1d2p1 swstdevinfoset completed C:\>C:\Win32App\AdvancedCopyManager\bin\swstdevinfoset -t g1d3p1 swstdevinfoset completed C:\>C:\Win32App\AdvancedCopyManager\bin\swstbkpolset -s 1 -i 7 g1d1p1 g1d1p1 swstbkpolset completed C:\>C:\Win32App\AdvancedCopyManager\bin\swstbkpolset -s 1 -i 7 g1d2p1 g1d2p1 swstbkpolset completed C:\>C:\Win32App\AdvancedCopyManager\bin\swstbkpolset -s 1 -i 7 g1d3p1 g1d3p1 swstbkpolset completed C:\> |
Register the backup volumes.
C:\>set SWSTGNODE=nodeAGT C:\>C:\Win32App\AdvancedCopyManager\bin\swstdevinfoset -b g1d11p1 swstdevinfoset completed C:\>C:\Win32App\AdvancedCopyManager\bin\swstdevinfoset -b g1d12p1 swstdevinfoset completed C:\>C:\Win32App\AdvancedCopyManager\bin\swstdevinfoset -b g1d13p1 swstdevinfoset completed |
In standard backup pre-processing and post-processing, the transaction volume is locked and unlocked. This processing enables copies to be created with the file system in the matched state.
However, when the SQL Server database is being backed up, the transaction volume cannot be locked in backup pre-processing because SQL Server is using the database files.
Set the file so that a transaction volume locking specification file for backups is created for the SQL Server database volume. In addition, ensure that the transaction volume where database files are located is not locked, and that the file system buffer only is flushed.
No problem results from the volume being unlocked because SQL Server guarantees the matching of database component files.
For details about this, refer to "A.2.2.2 Transaction volume locking specification file for backups".
Figure 8.9 Example of a description in the transaction volume locking specification file for backups
Note
Take care with the changes that are made. If a change is made incorrectly, then the backup command will result in an error.
Since the transaction volume is not locked, do not place files other than database files on the transaction volume where database files are located.
If you specify the backup volumes which become copy destinations, create a device map file. For details of this, refer to "4.4.10 Preparing a 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 swstrestore_sqlsvr (SQL Server restore execution 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/OperationMGR. Automate the backup using commands provided by AdvancedCopy Manager.
