Blanche
Engineer
Engineer
  • UID619
  • Fans2
  • Follows2
  • Posts59
Reads:2131Replies:0

Using Backup Files on the RDS to Recover a SQL Server Database

Created#
More Posted time:Jul 14, 2016 14:12 PM
Application Scenario: This article is applicable when you have backup files on an Alibaba Cloud SQL Server RDS instance and want to use the backup files to restore a database to a previous time point on another server.
Preparation: Prepare a server for restoration and install SQL Server 2008 or 2012 on it.
Restoration Scenario: The restoration time point is between an incremental backup and a full backup.
 
The above figure shows the backup information on the RDS console. Our goal is to restore the database to its status at 7:00 P.M. on February 12, 2014, that is between the incremental backup at 6:31 A.M. on February 13 and the full backup at 6:29 A.M. on February 14, 2014.
Procedures taken to complete the recovery:
1. Download the full backup files created at 7:16 A.M. on February 10, 2014 and decompress the backup files for the recovery.
2. Use the NORECOVERY method to restore the full backup created at 7:16 A.M. on February 10, 2014. The SQL statements are shown below:
  

Reference
USE [master]
RESTORE DATABASE [Database Name]  
FROM  DISK = N'Path of the full backup file'  
WITH  FILE = 1,  
NORECOVERY
GO
 
After the restoration was successful, the database turned into Restoring status.
3. Use the NORECOVERY method to restore the incremental backup created at 6:51 A.M. on February 12, 2014. The SQL statements are shown below:

Reference
USE [master]
RESTORE DATABASE [Database Name]  
FROM  DISK = N'Path of the incremental backup file'  
WITH  FILE = 1,  
NORECOVERY
GO
 
After the restoration was successful, the database turned into Restoring status.
3. Contact Alibaba Cloud customer service to get the log backup files time stamped before 6:51 A.M. on February 12, 2014 and before 6:31 A.M. on February 13, 2014 from the DBA (currently, users cannot download log backup files from the RDS console).
4. Use the RECOVERY method (the default method) to restore the logs.
 
Reference
RESTORE LOG [Name of the Database to Restore]  
FROM  DISK = N'Path of the log backup file obtained in the previous step'  
WITH  FILE = 1,  
STOPAT = N'Recovery time point'
GO
After the log was restored, the database changed into 'Normal' status, which indicated that the database had been restored to the desired time point.
Guest