| ETERNUS SF AdvancedCopy Manager Operator's Guide 13.0 -Microsoft(R) Windows(R) 2000- -Microsoft(R) Windows Server(TM) 2003- |
|
Contents
Index
|
This chapter explains how to perform backup and restore of the SQL Server 2000/2005 databases by employing the backup management function or the replication management function of AdvancedCopy Manager.
SQL Server 2000/2005 is a registered trademark of Microsoft Corporation in the U.S.A. and elsewhere.
SQL Server 2000/2005 is an enterprise database software that serves as the core of Microsoft(R).NET Enterprise Servers family.
AdvancedCopy Manager performs high-speed backup and restore of SQL Server 2000/2005 databases constructed on ETERNUS storage system by cooperating with SQL Server 2000/2005.
AdvancedCopy Manager provides the functions (commands) to perform high-speed backup and restore of SQL Server 2000/2005 databases.
SQL Server backup execution command
SQL Server restore execution command
As for the SQL Server backup execution command and the SQL Server restore execution command, two kinds are provided for the backup management function and for the replication management function. Swstbackup_sqlsvr and swstrestore_sqlsvr are used when operating by the backup management function, and swsrpbackup_sql and swsrprestore_sql are used when operating by the replication management function.
For backup management function
For replication management function
SQL Server backup execution command
swstbackup_sqlsvr
swsrpbackup_sql
SQL Server restore execution command
swstrestore_sqlsvr
swsrprestore_sql
An SQL Server 2000/2005 database consists of the following physical files (database files):
Primary data file (One file is required per database.)
Secondary data files (There may be no file or more than one file.)
Transaction log files (At least one file is required per database.)
Backing up all database files is called the "database backup" or "full backup". Backing up only logs is called the "log backup".
The SQL Server backup execution command performs the database backup at high speed:
Copies the database files (primary data file, secondary data files, and transaction log files) onto the backup volumes.
Executes the data copy instantly by the advanced copy function, without utilizing server CPUs.
Realizes an online backup by cooperating with the VDI(Virtual Device Interface) mechanism of SQL Server 2000/2005.
In the backup operation of SQL Server 2000/2005, both the "database backup" and the "log backup" are generally used in combination.
The following figure shows an example of backup operation that combines the database backup using AdvancedCopy Manager and the log backup using SQL Server. In this example assuming the operation cycle as one week, the database backup is performed on Sunday and the log backup is performed on weekdays:
AdvancedCopy Manager provides the "database backup" function. Therefore, backup and restore (recovery) of a transaction log should be performed by SQL Server's Enterprise Manager and Transact-SQL, etc.
The databases which can be backed up with AdvancedCopy Manager are only user databases, and so system databases (master, msdb, model and distribution) cannot be backed up with AdvancedCopy Manager. The backup operation of the system databases should be performed with SQL Server 2000/2005.
|
SQL Server 2000/2005 |
|
||
|---|---|---|---|
|
System Databases (master, msdb, model, distribution) |
O |
X |
|
|
User Databases |
Database backup |
O |
O |
|
Log backup |
O |
X |
|
O:Provided, X:Not provided
In this chapter, the "database restore" refers to changing back a database to the status at the time of database backup. In contrast, the "database recovery" refers to applying logs to a database on which the database restore has been performed to restore the database to a specific point or the latest point after the database backup.
The SQL Server restore execution command recreates a database using the database files restored from a database backup. A series of processing of (1)restoring database files, (2)recreating a database and (3)applying transaction logs can be completed in a short time by using OPC(One Point Copy) of the advanced copy function.
In the example of operation of Figure 10.2, the following figure shows how to perform restore up to the latest point when a failure occurs before the log backup on Friday:
Work out the following backup operation designs before you perform the backup operation of SQL Server 2000/2005.
Design the backup operation of a database. For more information, see "Designing a Backup and Restore Strategy" in the "SQL Server Books Online".
Additionally, observe the following limitations on placing database files.
AdvancedCopy Manager does not support a database constructed in raw partitions. Construct a database on file systems.
AdvancedCopy Manager performs copy in units of volume (i.e., in units of partitions). Thus, do not place any file other than target database files on the volumes on which the database files are placed.
If a file irrelevant to the database files to be backed up is placed on a volume to be backed up, not only the data of the file but also the entire file system might be corrupted.
In particular, do not place any database file on the system drive or on the volume that stores the executable files and management files of SQL Server 2000/2005 and AdvancedCopy Manager.
Both mixing files of N(>2) databases on one volume and mixing files of N(>2) databases on M(>2) volumes are possible, however, these N databases must be databases under the control of the same instance.
If you perform the backup for the database of such a configuration, specify all the databases which exist on the volumes. After SQL Server 2000/2005 secures the data consistency by freezing all the specified databases, AdvancedCopy Manager executes the copy processing to all the volumes where the files are placed. If the backup is performed without specifying all the databases, the database backup cannot be normally obtained (the file systems of the backup volumes might be damaged).
The above-mentioned database configuration is seen in the system with insufficient drive letters or the system where many small-scale databases exist, and has the following problems in respect of backup operation:
When two or more databases are backed up at the same time, all the specified databases are frozen by SQL Server 2000/2005 until processing is completed. If the frozen time of the data base becomes long, there is a possibility that the backup cannot be normally completed. Design databases so that the number of the databases to be backed up may decrease as much as possible.
If you will restore a specific database, you cannot use the advanced copy for file restore processing. In this case, only the database files to be restored must be restored by the means of the COPY command and the FTP, etc. The advanced copy can be used only for file restore in the case where all the databases are restored.
As shown in Figure 10.7, you can place database files distributed over multiple volumes. AdvancedCopy Manager performs processing on all the volumes on which database files are placed.
Determine the servers to perform backup operation.
The following servers are used to perform backup:
Multiple storage servers are unified and operated centrally. The manager function of AdvancedCopy Manager should be installed. A storage management server can also be used as a storage server.
SQL Server 2000/2005 is operated on this server. The agent function of AdvancedCopy Manager should be installed. Backup and restore of databases are performed on this server.
This server serves as a backup server. The agent function of AdvancedCopy Manager should be installed. The volumes (i.e., backup volumes) which become backup destination of database volumes are connected to this server. The operation using a backup server can be executed only if the replication management function is employed and so cannot be executed if the backup management function is employed.
Determine the databases to be backed up and the volumes (database volumes) where their database files are placed.
All the database volumes must be placed on disk array unit ETERNUS storage system.
Determine the backup method.
The following two can be selected as a backup method.
It is a backup method using OPC/ROPC function of disk array unit ETERNUS storage system. The backup is obtained by starting OPC/ROPC.
The differential snapshot high-speed backup can be done for the disk array corresponding to the QuickOPC function.
For information on the differential snapshot high-speed backup, see "Backup Operation by the QuickOPC Function."
It is a backup method using EC/REC function of disk array unit ETERNUS storage system. The backup is obtained by stopping (in case of employing backup management function) or suspending (in case of employing replication management function) EC/REC in the equivalency holding state.
When the backup management function is used, a backup is created by stopping or suspending EC/REC under the equivalency maintain status. When the SQL Server backup execution command (swstbackup_sqlsvr) is executed with -suspend specified, the Suspend/Resume function suspends the equivalency maintain status and executes the backup operation. For information on the Suspend/Resume function, see "Backup Operation That Uses the Suspend/Resume Function."
Prepare backup volumes.
The volumes which become backup destination of database volumes are called backup volumes.
All the backup volumes must be placed on disk array unit ETERNUS storage system.
Carry out the following preparations before the backup operation of SQL Server 2000/2005 is started.
Setting the environment for SQL Server
Carrying out a general preparation
Creating the transaction volume locking specification file for backups
Preparing a device map file (Only if the backup destination is specified)
Recording the output path of a metadata file
Preparing the automated operation of backup
The following example is used for explanation in the following sub sections.
[Server configuration]
|
Server type |
Server name |
comment |
|---|---|---|
|
Storage management server |
MGR-SVR |
|
|
Storage server |
DB-SVR |
|
<Database configuration>
|
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 |
<Database volume configuration>
|
Drive letter |
AdvancedCopy Manager device name |
|---|---|
|
D: |
g1d1p1 |
|
E: |
g1d2p1 |
|
F: |
g1d3p1 |
Set up SQL Server based on the result of database backup operation design. If, in particular, 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, see "Designing a Backup and Restore Strategy" in the "SQL Server Books Online".
Do the work described in "Preparation", noting the following points.
When you register transaction volumes, register all the volumes on which database files are placed.
Also, set the same backup policies for all the volumes on which database files are placed. The management of backup history information becomes easy by making the backup policies the same though the backup can be executed even if the backup policies are not made the same.
<Execution example>
Register all the target database volumes in AdvancedCopy Manager 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 in AdvancedCopy Manager.
|
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 preprocessing and postprocessing, 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 2000/2005 database is being backed up, the transaction volume cannot be locked in backup preprocessing because SQL Server 2000/2005 is using database files.
Therefore, set the file so that a transaction volume locking specification file for backups is created for the SQL Server database volume, the transaction volume where database files are located is not locked, and the file system buffer is only flushed. No problem results from the volume being unlocked because SQL Server 2000/2005 guarantees the matching of database component files.
For details on the transaction volume locking specification file for backups, refer to "Transaction volume locking specification file for backups".
Be careful with the changes made. If a change is made incorrectly, then the backup command ends in an error.
Since it 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 referring to "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 of the database for backup is outputted from SQL Server 2000/2005. AdvancedCopy Manager saves a metadata file in a backup volume.
A metadata file is restored to a database volume at the time of restore of database files. Since it becomes indispensable when executing SQL Server restore execution command (swstrestore_sqlsvr), it needs to record the absolute path of a metadata file in advance.
The metadata file is saved by the name of ""DB name".swst-dmp" at the folder where the database file with file identification number (FILE_ID) 1 exists. For example, supposing that 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 will serve as "D:\SQLSVR\DB01.swst-dmp." Usually, although the database file with file identification number 1 is a primary data file, check it in the following procedure by way of precaution.
Perform Transact-SQL "USE database name EXEC SP_HELPFILE".
Look for the record with which the column "file id" is 1 from an execution result. The content of the column "filename" of the record is the absolute path of the database file with file identification number 1.
Although it is strictly required to secure the capacity for saving a metadata file in operating volume, since the size of a metadata file is small, there is almost no necessity for consideration. (The size of a metadata file depends on the number of files of a database. For example, it is about 20KB in the database of 10 files composition).
You can automate the backup operation of AdvancedCopy Manager using the job creation wizard of SQL Server or SystemWalker/OperationMGR. Automate the backup operation using commands provided by AdvancedCopy Manager.
It explains how to perform backup and restore of the SQL Server database.
The backup of the SQL Server database is composed of the following procedures.
Starting synchronous processing (Only if synchronous type backup is employed)
Backing up a database
Backing up a transaction log
In the case of synchronous type backup, synchronous processing (EC/REC) should start beforehand. Synchronous processing starts by backup synchronous processing starting command (swststartsync). If the database is composed of two or more volumes, synchronous processing for all the database volumes should be executed.
<Execution example>
Start synchronous processing for all the database volumes.
|
C:\>set SWSTGNODE=nodeAGT C:\>C:\Win32App\AdvancedCopyManager\bin\swststartsync g1d1p1 g1d1p1 swststartsync completed C:\>C:\Win32App\AdvancedCopyManager\bin\swststartsync g1d2p1 g1d2p1 swststartsync completed C:\>C:\Win32App\AdvancedCopyManager\bin\swststartsync g1d3p1 g1d3p1 swststartsync completed C:\> |
Backup of an SQL Server database is performed by the SQL Server backup execution command (swstbackup_sqlsvr).
The transaction log is not truncated by snapshot backup. Therefore, execute the log backup for the truncation of the transaction log regularly even when you compose the backup operation only of the database backup. For details, refer to the clause of "Truncating the Transaction Log" of "SQL Server Books Online."
If an error occurs while SQL Server backup execution command (swstbackup_sqlsvr) is processing the database of two or more volumes composition with the backup execution command (swstbackup), processing is interrupted at that time and the backup history information on AdvancedCopy Manager may become inconsistent. In order to cancel such an inconsistent state, delete unnecessary history information using a history information deletion command (swsthistdel). Also, for the volumes for which the replication processing has already been completed, after you deal with the error cause, restart synchronous processing by using the backup synchronous processing start command (swststartsync).
<Execution example>
Perform backup of databases DB01, DB02 and DB03. If you employ synchronous type backup, by the backup synchronous processing progress display command (swstsyncstat), confirm synchronous processing for all database volumes is in equivalency holding state (namely, the Execute column is "100%"). Execute the command from the business server (DB-SVR), where it is assumed that the device map file name is G:\SQLADM\devmap.txt.
|
C:\>set SWSTGNODE=nodeAGT C:\>C:\Win32App\AdvancedCopyManager\bin\swstbackup_sqlsvr -Xserver VSERVER -Xdevmap G:\SQLADM\devmap.txt DB01 DB02 DB03 DB01 DB02 DB03 swstbackup_sqlsvr completed C:\> |
When the SQL Server backup execution command (swstbackup_sqlsvr) is executed with -suspend specified, the Suspend/Resume function suspends the synchronous processing and executes the backup operation. For information on the Suspend/Resume function, see "Backup Operation That Uses the Suspend/Resume Function."
Backup the transaction log with Enterprise Manager or Transact-SQL of SQL Server 2000/2005.
The restore of SQL Server database is composed of the following procedures.
Backing up a transaction log (Only if the restore to the latest point should performed)
Restoring a database
Recovering a database (Only if the restore to the latest point or a specific point should be performed)
Backup the transaction log if the restore to the latest point should be performed.(Execute the BACKUP LOG statement using the NO_TRUNCATE clause to back up the currently active transaction log.) You cannot perform the restore to the latest point if transaction log has not been backed up or a failure has occurred disabling the log backup. In such cases, use the latest log backup before a failure occurs to perform database recovery.
Restore a database by performing the following two procedures:
Restoring files
Recreating a database
If a user's (login) default database is set as the target database of restoration, then it must be changed to the master database before the above operation. In such cases, the steps for restoration are as follows.
A default database is changed into master from the database for restoration.
"Restoring file" work is done.
"Recreating a database" work is done.
A default database is changed into the database for restoration from master.
A default database can be changed by Enterprise Manager. :
Expand a server group, and then expand a server.
Expand Security, and then click Logins.
In the details pane, right-click the login to modify, and then click Properties.
In the Database list, on the General tab, click the new default database to which the login is connected after logging into an instance of SQL Server(The above-mentioned modifying method is indicated by "To change the default database of a login" of "Administering SQL Server"->"Managing Security"->"Managing Security Accounts"->"Modifying Logins" in SQL Server Books Online.
Restore all the database files (data and log files).
You cannot restore only log files.
Restore all the database files as follows:
|
Step |
Step name |
Description |
|---|---|---|
|
1 |
Deleting a database |
Delete a database using Enterprise Manager. When you apply logs, be careful not to delete the "history of database backup and restore". If you do, you will no longer be able to apply logs from Enterprise Manager. In this case, apply logs by Transact-SQL. The user who re-creates a database as described in a subsequent section is the owner of the database after restoration is completed. If this user is different from the database owner before the restoration, then the database owner must be changed after the database is re-created. Therefore, check and make a note of the present database owner before deleting the database. |
|
2 |
Executing the restore execution command |
Execute the Restore execution command (swstrestore) for transaction volumes on which all the database files are placed. |
A meta data file as well as database files will be restored (to the same folder as the data file with file identification number 1).
If you use the operation of further saving the data on a backup volume of AdvancedCopy Manager to a tape unit, you can also directly restore files from the tape unit to the database volumes without using the restore execution command.
<Execution example>
Restore files by using the restore execution command (swstrestore) after deleting the databases.
|
C:\>set SWSTGNODE=nodeAGT C:\>C:\Win32App\AdvancedCopyManager\bin\swstrestore g1d1p1 g1d1p1 swstrestore completed C:\>C:\Win32App\AdvancedCopyManager\bin\swstrestore g1d2p1 g1d2p1 swstrestore completed C:\>C:\Win32App\AdvancedCopyManager\bin\swstrestore g1d3p1 g1d3p1 swstrestore completed C:\> |
Recreate a database using the SQL Server restore execution command (swstrestore_sqlsvr). You can select one of the following restore methods:
Restore to the latest point or a specific point
Restore to the backup point
The database owner after restoration is completed is the user who re-created the database. If this user is different from the database owner before the restoration, then change the owner by using the following stored procedure: sp_changedbowner.
<Execution example>
Recreate the databases after performing file restore. Because the meta data files are restored to D:\SQLSVR\DB01.swst-dmp etc., execute the commands specifying these files.
|
C:\>C:\Win32App\AdvancedCopyManager\bin\swstrestore_sqlsvr -Xserver VSERVER -Xmeta D:\SQLSVR\DB01.swst-dmp DB01 DB01 swstrestore_sqlsvr completed C:\>C:\Win32App\AdvancedCopyManager\bin\swstrestore_sqlsvr -Xserver VSERVER -Xmeta D:\SQLSVR\DB02.swst-dmp DB02 DB02 swstrestore_sqlsvr completed C:\>C:\Win32App\AdvancedCopyManager\bin\swstrestore_sqlsvr -Xserver VSERVER -Xmeta D:\SQLSVR\DB03.swst-dmp DB03 DB03 swstrestore_sqlsvr completed C:\> |
To restore a database to the latest point or a specific point, apply logs using Enterprise Manager. For information on the operation method, see "How to apply a transaction log backup (Enterprise Manager)" of the "SQL Server Books Online".
If you cannot apply logs using Enterprise Manager because, for example, the backup history of msdb is lost, apply logs using Transact-SQL. For information on the operation method, see "How to apply a transaction log backup (Transact-SQL)" of the "SQL Server Books Online".
Confirm the copy status by the backup synchronous processing progress display command (swstsyncstat), the backup execution status display command (swstbackstat), and the restore execution status display command (swstreststat). The database backup history information is stored in msdb of SQL Server. You can refer to the latest backup time by selecting "Property" after right-clicking in the target database on the tree screen of Enterprise Manager.
When the copy processing under execution is stopped, use the backup synchronous processing cancel command (swstcancelsync), the history information deletion command (swsthistdel), and restore cancellation command (swstcancelrest).
<Execution example>
Stop backup synchronous processing under execution.
|
C:\>set SWSTGNODE=nodeAGT C:\>C:\Win32App\AdvancedCopyManager\bin\swstcancelsync g1d1p1 g1d1p1 swstcancelsync completed C:\>C:\Win32App\AdvancedCopyManager\bin\swstcancelsync g1d2p1 g1d2p1 swsrpcancel completed C:\>C:\Win32App\AdvancedCopyManager\bin\swstcancelsync g1d3p1 g1d3p1 swstcancelsync completed C:\> |
AdvancedCopy Manager supports Microsoft Clustering Service (MSCS). For more information, see the "Notes on cluster operation".
Carry out the following preparations before the backup operation of SQL Server 2000/2005 is started.
Setting the environment for SQL Server
Carrying out a general preparation
Preparing a device map file
Recording the output path of a metadata file
Preparing the automated operation of backup
The following example is used for explanation in the following sub sections.
<Server configuration>
|
Server type |
Server name |
Comment |
|---|---|---|
|
Storage management server |
MGR-SVR |
|
|
Storage server |
DB-SVR |
|
|
Storage server |
BKUP-SVR |
Backup server |
<Database configuration>
|
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 |
<Database volume configuration>
|
Drive letter |
AdvancedCopy Manager device name |
|---|---|
|
D: |
g1d1p1@DB-SVR |
|
E: |
g1d2p1@DB-SVR |
|
F: |
g1d3p1@DB-SVR |
Set up SQL Server based on the result of database backup operation design. If, in particular, 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, see "Designing a Backup and Restore Strategy" in the "SQL Server Books Online".
Do the work described in Capter 7.4 "Preparation", noting the following points.
Register all the database volumes and register the backup volumes which become copy destinations.
Set a database volume and a backup volume to be an original volume and a replica volume, respectively (Do not reverse an original volume and a replica volume).
Set the server (original server) which operates the database to be an operation server when you employ the backup server. (Specify "ORG" for the argument of o option in the replication volume information setting command.)
Specify u option if you do not use the advanced copy to perform the file restore. The direction of the copy which can be executed can be limited to one direction from the original volume (database volume) to the replica volume (backup volume).
<Execution example>
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:\> |
Create a file (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 on an arbitrary place of the server which operates the database (original server). The backup destination of the database volume is decided by specifying this file when backup is performed.
The following shows a description example of a device map file.
Description example of a device map file
The following lists 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 "half-size blank or tab characters". One or more "half-size blank or tab characters" may be included between the beginning of a line and a database volume name or between the end of a backup volume name and the end of a line (newline character).
Specify the backup volume of a backup server in the form of "volume name@server name". Only the form of "volume name" can be used about the database volume. Also, it should be noted that the form of "volume name@database server name" cannot be used.
A blank line ("half-size blank or tab characters") may be included in a file.
The portion from the symbol "#" to the end of a line is assumed as a comment.
You cannot specify more than one target backup volumes for one database volume in one device map file. In such a case, the information in the line first found will be valid. While a device map file is read, the system will not detect such a redundant line. Prepare two or more device map files if you perform the plural generation backup.
A device map file may include descriptions of database volumes other than the processing target database. (See the description example provided in the beginning of this chapter.)
Describe all the database volumes for the database of the multivolume composition in one device map file. (The description shall not be extending over plural 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 outputted from SQL Server 2000/2005. AdvancedCopy Manager saves a metadata file in a backup volume.
A metadata file is restored to a database volume at the time of restore of database files. Since it becomes indispensable when executing SQL Server restore execution command (swsrprestore_sql), it needs to record the absolute path of a metadata file in advance.
The metadata file is saved by the name of ""DB name".swsrp-dmp" at the folder where the database file with file identification number (FILE_ID) 1 exists. For example, supposing that 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 will serve as "D:\SQLSVR\DB01.swsrp-dmp." Usually, although the database file with file identification number 1 is a primary data file, check it in the following procedure by way of precaution.
Perform Transact-SQL "USE database name EXEC SP_HELPFILE".
Look for the record with which the column "file id" is 1 from an execution result. The content of the column "filename" of the record is the absolute path of the database file with file identification number 1.
Although it is strictly required to secure the capacity for saving a metadata file in operating volume, since the size of a metadata file is small, there is almost no necessity for consideration. (The size of a metadata file depends on the number of files of a database. For example, it is about 20KB in the database of 10 files composition.)
You can automate the backup operation of AdvancedCopy Manager using the job creation wizard of SQL Server or SystemWalker/OperationMGR. Automate the backup operation using commands provided by AdvancedCopy Manager.
It explains how to perform backup and restore of the SQL Server database.
The backup of the SQL Server database is composed of the following procedures.
Starting or resuming synchronous processing (Only if synchronous type backup is employed)
Backing up a database
Backing up transaction log
In the case of synchronous type backup, synchronous processing (EC/REC) should start or resume beforehand. Synchronous processing starts by backup synchronous processing starting command (swsrpstartsync). If the database is composed of two or more volumes, synchronous processing for all the database volumes should be executed.
<Execution example>
Start or resume synchronous processing for all the database volumes. Execute the command from the business server (DB-SVR).
|
C:\>set SWSTGNODE=nodeAGT C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpstartsync g1d1p1 g1d11p1@BKUP-SVR FROM=g1d1p1@DB-SVR,TO=g1d11p1@BKUP-SVR swsrpstartsync completed C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpstartsync g1d2p1 g1d12p1@BKUP-SVR FROM=g1d2p1@DB-SVR,TO=g1d12p1@BKUP-SVR swsrpstartsync completed C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpstartsync g1d3p1 g1d13p1@BKUP-SVR FROM=g1d3p1@DB-SVR,TO=g1d13p1@BKUP-SVR swsrpstartsync completed C:\> |
Backup of an SQL Server database is performed by the SQL Server backup execution command (swsrpbackup_sql).
The transaction log is not truncated by snapshot backup. Therefore, execute the log backup for the truncation of the transaction log regularly even when you compose the backup operation only of the data base backup. For details, refer to the clause of "Truncating the Transaction Log" of "SQL Server Books Online."
If an error occurs while SQL Server backup execution command (swsrpbackup_sql) is processing the database of two or more volumes composition with the replication creation command (swsrpmake), processing is interrupted at that time. For the volumes for which the replication processing has already been completed, after you deal with the error cause, restart synchronous processing by using the synchronous processing start command (swsrpstartsync).
<Execution example>
Perform backup of databases DB01, DB02 and DB03. If you employ synchronous type backup, by the operation status display command (swsrpstat), confirm synchronous processing for all database volumes is in equivalency holding state (namely, the Execute column is "100%"). Execute the command from the business server (DB-SVR), where it is assumed that the device map file name is G:\SQLADM\devmap.txt.
|
C:\>set SWSTGNODE=nodeAGT C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpbackup_sql -Xserver VSERVER -Xdevmap G:\SQLADM\devmap.txt DB01 DB02 DB03 DB01 DB02 DB03 swsrpbackup_sql completed C:\> |
Backup the transaction log with Enterprise Manager or Transact-SQL of SQL Server 2000/2005.
The restore of the SQL Server database is composed of the following procedures.
Backing up transaction log (Only if the restore to the latest point should performed)
Restoring a database
Recovering a database (Only if the restore to the latest point or a specific point should be performed)
Backup the transaction log if the restore to the latest point should be performed.(Execute the BACKUP LOG statement using the NO_TRUNCATE clause to back up the currently active transaction log.) You cannot perform the restore to the latest point if transaction log has not been backed up or a failure has occurred disabling the log backup. In such cases, use the latest log backup before a failure occurs to perform database recovery.
Restore a database by performing the following two procedures:
Restoring files
Recreating a database
If a user's (login) default database is set as the target database of restoration, then it must be changed to the master database before the above operation. In such cases, the steps for restoration are as follows.
A default database is changed into master from the database for restoration.
"Restoring file" work is done.
"Recreating a database" work is done.
A default database is changed into the database for restoration from master.
A default database can be changed by Enterprise Manager. :
Expand a server group, and then expand a server.
Expand Security, and then click Logins.
In the details pane, right-click the login to modify, and then click Properties.
In the Database list, on the General tab, click the new default database to which the login is connected after logging into an instance of SQL Server(The above-mentioned modifying method is indicated by "To change the default database of a login" of "Administering SQL Server"->"Managing Security"->"Managing Security Accounts"->"Modifying Logins" in SQL Server Books Online.
Restore all the database files (data and log files).
You cannot restore only log files.
Restore all the database files as follows:
|
Step |
Step name |
Description |
|---|---|---|
|
1 |
Deleting a database |
Delete a database using Enterprise Manager. When you apply logs, be careful not to delete the "history of database backup and restore". If you do, you will no longer be able to apply logs from Enterprise Manager. In this case, apply logs by Transact-SQL. The user who re-creates a database as described in a subsequent section is the owner of the database after restoration is completed. If this user is different from the database owner before the restoration, then the database owner must be changed after the database is re-created. Therefore, check and make a note of the present database owner before deleting the database. |
|
2 |
Executing the replication creation command |
Executing the replication creation command (swsrpmake), copy all the database files from the backup volumes to the database volumes. |
A meta data file as well as database files will be restored (to the same folder as the data file with file identification number 1).
If you use the operation of further saving the data on a backup volume of AdvancedCopy Manager to a tape unit, you can also directly restore files from the tape unit to the database volumes without using the replication creation command.
<Execution example>
Restore files by using the snapshot type replication (OPC) after deleting the databases. Execute the command from the business server (DB-SVR).
|
C:\>set SWSTGNODE=nodeAGT C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpmake g1d11p1@BKUP-SVR g1d1p1 FROM=g1d11p1@BKUP-SVR,TO=g1d1p1@DB-SVR swsrpmake completed C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpmake g1d12p1@BKUP-SVR g1d2p1 FROM=g1d12p1@BKUP-SVR,TO=g1d2p1@DB-SVR swsrpmake completed C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpmake g1d13p1@BKUP-SVR g1d3p1 FROM=g1d13p1@BKUP-SVR,TO=g1d3p1@DB-SVR swsrpmake completed C:\> |
Recreate a database using the SQL Server restore execution command (swsrprestore_sql). You can select one of the following restore methods:
Restore to the latest point or a specific point
Restore to the backup point
The database owner after restoration is completed is the user who re-created the database. If this user is different from the database owner before the restoration, then change the owner by using the following stored procedure: sp_changedbowner.
<Execution example>
Recreate the databases after performing file restore. Because the meta data files are restored to D:\SQLSVR\DB01.swsrp-dmp etc., execute the commands specifying these files from the business server (DB-SVR).
|
C:\>C:\Win32App\AdvancedCopyManager\bin\swsrprestore_sql -Xserver VSERVER -Xmeta D:\SQLSVR\DB01.swsrp-dmp DB01 DB01 swsrprestore_sql completed C:\>C:\Win32App\AdvancedCopyManager\bin\swsrprestore_sql -Xserver VSERVER -Xmeta D:\SQLSVR\DB02.swsrp-dmp DB02 DB02 swsrprestore_sql completed C:\>C:\Win32App\AdvancedCopyManager\bin\swsrprestore_sql -Xserver VSERVER -Xmeta D:\SQLSVR\DB03.swsrp-dmp DB03 DB03 swsrprestore_sql completed C:\> |
To restore a database to the latest point or a specific point, apply logs using Enterprise Manager. For information on the operation method, see "How to apply a transaction log backup (Enterprise Manager)" of the "SQL Server Books Online".
If you cannot apply logs using Enterprise Manager because, for example, the backup history of msdb is lost, apply logs using Transact-SQL. For information on the operation method, see "How to apply a transaction log backup (Transact-SQL)" of the "SQL Server Books Online".
Confirm the copy status by the operation status display command (swsrpstat). The database backup history information is stored in msdb of SQL Server. You can refer to the latest backup time by selecting "Property" after right-clicking in the target database on the tree screen of Enterprise Manager.
When the copy processing under execution is stopped, use the replication cancellation command (swsrpcancel).
<Execution example>
Stop backup synchronous processing under execution. Execute the command from the business server(DB-SVR).
|
C:\>set SWSTGNODE=nodeAGT C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpcancel g1d1p1 g1d11p1@BKUP-SVR FROM=g1d1p1@DB-SVR,TO=g1d11p1@BKUP-SVR swsrpcancel completed C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpcancel g1d2p1 g1d12p1@BKUP-SVR FROM=g1d2p1@DB-SVR,TO=g1d12p1@BKUP-SVR swsrpcancel completed C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpcancel g1d3p1 g1d13p1@BKUP-SVR FROM=g1d3p1@DB-SVR,TO=g1d13p1@BK-SVR swsrpcancel completed C:\> |
AdvancedCopy Manager supports Microsoft Clustering Service (MSCS). For more information, see the " Notes on cluster operation ".
|
Contents
Index
|