This section explains how to perform backup and restore of an SQL Server database.
The backup of an SQL Server database consists of the following procedures:
Starting or resuming synchronous processing (only if synchronous type backup is used)
Backing up the database
Backing up the transaction log
Starting or resuming synchronous processing (only if synchronous type backup is used)
In the case of synchronous type backup, synchronous processing (EC/REC) should be started or resumed beforehand. Synchronous processing starts by executing swsrpstartsync (Synchronous processing start command). If the database is composed of two or more volumes, synchronous processing for all the database volumes should be executed.
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:\> |
Backing up a database
Backup of an SQL Server database is performed by swsrpbackup_sql (SQL Server backup execution command).
Note
The transaction log is not truncated by performing a snapshot backup. Therefore, execute the log backup for the truncation of the transaction log regularly even when the backup only consists of the database backup. For details, refer to the section "Truncating the Transaction Log" in "SQL Server Books Online."
If an error occurs while the SQL Server backup execution command (swsrpbackup_sql) is processing a database that consists of two or more volumes with the replication creation command (swsrpmake), processing is interrupted at that time. In the case of volumes for which the replication processing has already been completed, after you fix cause of the error, restart synchronous processing by using swsrpstartsync (Synchronous processing start command).
Perform backup of databases DB01, DB02 and DB03. If you use the synchronous type backup, run swsrpstat (Operation status display command) to confirm that synchronous processing for all database volumes is in the equivalency maintenance state (where 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:\> |
Backing up transaction log
Back up the transaction log with Enterprise Manager or Transact-SQL in SQL Server.
The restore of the SQL Server database consists of the following procedures:
Backing up a transaction log (perform this if restore to the latest point is required)
Restoring a database
Recovering a database (perform this if restore to the latest point or to a specific point is required)
Backing up a transaction log (perform this if restore to the latest point is required)
Backup the transaction log if a restoration to the latest point is required (execute the BACKUP LOG statement using the NO_TRUNCATE option to back up the currently active transaction log).
You cannot perform the restore to the latest point if the transaction log has not been backed up, or if a failure has occurred when disabling the log backup. In such cases, use the latest log backup before the failure occurred to perform database recovery.
Restoring a database
Restore a database by performing the following two procedures:
Restoring files
Recreating a database
Note
If a user's default database (the one used after logging on) is set as the target database of restoration, then it must be changed to be the master database before the above operation is performed. In such cases, the steps for restoration are as follows:
The default database is changed into the master from the database for restoration.
"Restoring file" processing is performed.
"Recreating a database" processing is performed.
The default database is changed to the database for restoration from the master.
The default database can be changed by Enterprise Manager, as follows:
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 user is connected after logging in to an instance of SQL Server. (The abovementioned modification method is shown 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").
Restoring files
Restore all the database files (ie, data and log files).
Note
You cannot restore log files alone.
Restore all the database files as follows:
Step | Step name | Description |
|---|---|---|
1 | Deleting a database | Delete the 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 using Transact-SQL. The user who recreates 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 recreated. Therefore, determine the identity of the present database owner before deleting the database. |
2 | Executing the replication creation command |
|
Point
A metadata file as well as database files will be restored to the same folder as the data file with file identification number 1.
If you also save the data on a backup volume to a tape unit, you can then directly restore files from the tape unit to the database volumes without using the replication creation command.
Restore files by using the snapshot type replication (OPC) after deleting the databases.
Execute the following commands 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:\> |
Delete the databases, and then use synchronous replication (REC) to restore the files.
Execute these commands from the transaction server (DB-SVR).
C:\>set SWSTGNODE=nodeAGT C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpstartsync g1d11p1@BKUP-SVR g1d1p1 FROM=g1d11p1@BKUP-SVR,TO=g1d1p1@DB-SVR swsrpstartsync completed C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpstartsync g1d12p1@BKUP-SVR g1d2p1 FROM=g1d12p1@BKUP-SVR,TO=g1d2p1@DB-SVR swsrpstartsync completed C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpstartsync g1d13p1@BKUP-SVR g1d3p1 FROM=g1d13p1@BKUP-SVR,TO=g1d3p1@DB-SVR swsrpstartsync completed (Wait until equivalency status is reached) 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:\> |
Recreating a database
Recreate a database using swsrprestore_sql (SQL Server restore execution command). You can select one of the following restore methods:
Restore to the latest point or a specific point
Restore to the backup point
Note
The database owner after restoration is completed is the user who recreated 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.
Recreate the databases after performing file restore. Because the metadata files are restored to D:\SQLSVR\DB01.swsrp-dmp etc., execute the commands specifying these files from the business server (DB-SVR).
In the example below, the -Xnolog option is not specified after database recreation. This is because it is assumed that the database will be restored to the latest point or to a specific point in time, as a result of the application of transaction logs.
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:\> |
Point
Once the SQL server restore execution command completes normally, the metadata file will be deleted automatically.
Recovering a database (perform this if restore to the latest point or to a specific point is required)
To restore a database to the latest point or to a specific point, apply logs using Enterprise Manager. For more information, refer to "How to apply a transaction log backup (Enterprise Manager)" in 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 more information, refer to "How to apply a transaction log backup (Transact-SQL)" in the "SQL Server Books Online".
Confirm the copy status by swsrpstat (Operation status display command). 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 swsrpcancel (Replication cancellation command).
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 MSCS or WSFC. For more information, refer to "13.1.5 Notes on cluster operation".
