This section explains how to perform backup and restore of an SQL Server database.
The backup of the SQL Server database consists of the following procedures:
Starting synchronous processing (only if synchronous- type backup is used)
Backing up a database
Backing up a transaction log
Starting synchronous processing (only if synchronous type backup is used)
In the case of synchronous type backup, synchronous processing (EC/REC) should be started beforehand. Synchronous processing starts by executing swststartsync (Backup synchronous processing start command). If the database consists of two or more volumes, synchronous processing should be executed for all the database volumes.
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:\> |
Backing up a database
Backup of an SQL Server database is performed by swstbackup_sqlsvr (SQL Server backup execution command).
Note
The transaction log is not truncated by a snapshot backup. Therefore, execute the log backup for the truncation of the transaction log regularly, even when the backup consists only of the database backup. For details, refer to the clause "Truncating the Transaction Log" in "SQL Server Books Online".
If an error occurs while the SQL Server backup execution command (swstbackup_sqlsvr) is processing a database that consists of two or more volumes and the backup execution command (swstbackup) is running, processing is interrupted at that time and the backup history information on AdvancedCopy Manager may become inconsistent. In order to prevent such inconsistency, delete unnecessary history information using swsthistdel (History information deletion command). Additionally, for the volumes for which the replication processing has already been completed, after you fix the cause of the error, restart synchronous processing by using swststartsync (Backup synchronous processing start command).
Figure 10.9 Recovery from a backup history information inconsistency
Perform backup of databases DB01, DB02 and DB03. If you use a synchronous type backup, then by executing swstsyncstat (Backup synchronous processing progress display command), you can confirm synchronous processing for all database volumes is in the equivalency maintenance state (namely, when 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:\> |
Point
When the SQL Server backup execution command (swstbackup_sqlsvr) is executed with -suspend option specified, the Suspend/Resume function suspends the synchronous processing and executes the backup. For information on the Suspend/Resume function, refer to "Chapter 5 Backup Operation using Suspend/Resume Function".
Backing up a transaction log
Back up the transaction log with Enterprise Manager or Transact-SQL of SQL Server.
The restore of an 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 latest point or specific point is required)
Backing up a transaction log (perform this if restore to the latest point is required)
Back up the transaction log if the restoration to the latest point will need to be performed. (you should execute the BACKUP LOG statement using the NO_TRUNCATE clause to back up the currently active transaction log.)
You cannot perform a restore to the latest point if the transaction log has not been backed up or if a failure has occurred, causing the log backup to be disabled. In such cases, use the latest log backup before the failure occurs to perform database recovery.
Restoring a database
Restore a database by performing the following two procedures:
Restoring files
Recreating a database
Note
If the user's default database (at the time of login) is set as the target database for restoration, then it must be changed to the master database before the above operation. In such cases, the steps for restoration are as follows:
The default database is changed to the master for restoration.
"Restoring file" work is done.
"Recreating a database" work is done.
The default database is changed back 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 Login.
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 abovementioned modification method is detailed in the section titled "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 (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 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 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, identify the present database owner before deleting the database. |
2 | Executing the restore execution command | Execute swstrestore (Restore execution command) for the transaction volumes on which all the database files are placed. |
Point
A metadata file as well as the database files will be restored to the same folder as the data file with file identification number 1.
If you save 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.
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:\> |
Recreating a database
Recreate a database using swstrestore_sqlsvr (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 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.swst-dmp etc., execute the commands specifying these files.
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\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:\> |
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 a specific point is required)
To restore a database to the latest point or a specific point, apply logs using Enterprise Manager. For information on the operation method, 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 information on the operation method, refer to "How to apply a transaction log backup (Transact-SQL)" in the "SQL Server Books Online".
Confirm the copy status by executing swstsyncstat (Backup synchronous processing progress display command), swstbackstat (Backup execution status display command) and swstreststat (Restore execution status display command). The database backup history information is stored in msdb of the SQL Server. You can determine the latest backup time by selecting "Property" after right-clicking in the target database on the tree screen of Enterprise Manager.
To stop the currently running copy processing, use swstcancelsync (Backup synchronous processing cancel command), swsthistdel (History information deletion command) and swstcancelrest (Restore cancellation command).
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 MSCS or WSFC. For more information on this, refer to "13.1.5 Notes on cluster operation".
