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 the swsrpstartsync 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). (When specifying a replication group name)
C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpstartsync -Xgroup grp1 GROUP=grp1 swsrpstartsync completed C:\> |
Backing Up Database
Backup of an SQL Server database is performed by the swsrpbackup_sql 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 the swsrpstartsync command.
Perform backup of databases DB01, DB02 and DB03. If you use the synchronous type backup, execute the swsrpstat 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 replication group name is grp1.
C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpbackup_sql -Xserver VSERVER -Xgroup grp1 DB01 DB02 DB03 DB01 DB02 DB03 swsrpbackup_sql completed C:\>
Confirm the backup history information with the swsrphistory_sql command.
C:\>C:\Win32App\AdvancedCopyManager\bin\swsrphistory_sql disp -Xserver VSERVER -Xgroup grp1 Instance-Name Server-Name Group-Name DB-Name Meta-Data-File DB-File Original-Volume Replica-Volume Backup-Date MSSQLSERVER VSERVER grp1 DB01 D:\SQLSVR\DB01.swsrp-dmp D:\SQLSVR\DB01.mdf g1d1p1@DB-SVR g1d11p1@BKUP-SVR 2013/09/04 13:51 MSSQLSERVER VSERVER grp1 DB01 D:\SQLSVR\DB01.swsrp-dmp E:\SQLSVR\DB01.ndf g1d2p1@DB-SVR g1d12p1@BKUP-SVR 2013/09/04 13:51 MSSQLSERVER VSERVER grp1 DB01 D:\SQLSVR\DB01.swsrp-dmp F:\SQLSVR\DB01.ldf g1d3p1@DB-SVR g1d13p1@BKUP-SVR 2013/09/04 13:51 MSSQLSERVER VSERVER grp1 DB02 D:\SQLSVR\DB02.swsrp-dmp D:\SQLSVR\DB02.mdf g1d1p1@DB-SVR g1d11p1@BKUP-SVR 2013/09/04 13:51 MSSQLSERVER VSERVER grp1 DB02 D:\SQLSVR\DB02.swsrp-dmp E:\SQLSVR\DB02.ndf g1d2p1@DB-SVR g1d12p1@BKUP-SVR 2013/09/04 13:51 MSSQLSERVER VSERVER grp1 DB02 D:\SQLSVR\DB02.swsrp-dmp F:\SQLSVR\DB02.ldf g1d3p1@DB-SVR g1d13p1@BKUP-SVR 2013/09/04 13:51 MSSQLSERVER VSERVER grp1 DB03 D:\SQLSVR\DB03.swsrp-dmp D:\SQLSVR\DB03.mdf g1d1p1@DB-SVR g1d11p1@BKUP-SVR 2013/09/04 13:51 MSSQLSERVER VSERVER grp1 DB03 D:\SQLSVR\DB03.swsrp-dmp E:\SQLSVR\DB03.ndf g1d2p1@DB-SVR g1d12p1@BKUP-SVR 2013/09/04 13:51 MSSQLSERVER VSERVER grp1 DB03 D:\SQLSVR\DB03.swsrp-dmp F:\SQLSVR\DB03.ldf g1d3p1@DB-SVR g1d13p1@BKUP-SVR 2013/09/04 13:51 C:\>
Backing Up Transaction Log
Back up the transaction log with Management Studio 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 Transaction Log (Perform This If Restore to 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 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 Management Studio, 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.
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 Management Studio. When you apply logs, perform not to check the "Delete backup and restore history information for databases". If you do it, you will no longer be able to apply logs from Management Studio. 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):
When specifying a replication group name, specify the -Xgroup option and the -Xreverse option.
C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpmake -Xgroup grp1 -Xreverse GROUP=grp1 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).
When specifying a replication group name, specify the -Xgroup option and the -Xreverse option.
C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpstartsync -Xgroup grp1 -Xreverse GROUP=grp1 swsrpstartsync completed (Wait until equivalency status is reached) C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpmake -Xgroup grp1 -Xreverse GROUP=grp1 swsrpmake completed C:\> |
Recreating Database
Recreate a database using the swsrprestore_sql 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 Database (Perform This If Restore to Latest Point or Specific Point Is Required)
To restore a database to the latest point or to a specific point, apply logs using Management Studio. For more information, refer to "How to: Restore a Transaction Log Backup (SQL Server Management Studio)" in the "SQL Server Books Online" or "How to: Restore to a Point in Time (SQL Server Management Studio)" .
If you cannot apply logs using Management Studio 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 the swsrpstat 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 Management Studio.
When the copy processing under execution is stopped, use the swsrpcancel command.
Stop backup synchronous processing under execution.
Execute the command from the business server (DB-SVR).
When using a replication group, specify the -Xgroup option.
C:\>C:\Win32App\AdvancedCopyManager\bin\swsrpcancel -Xgroup grp1 GROUP=grp1 swsrpcancel completed C:\> |
AdvancedCopy Manager supports MSCS or WSFC. For more information, refer to "14.1.5 Notes on Cluster Operation".
