How To Restore Windows Database Records in the AWS Cloud

Restore Windows Database Records in the AWS CloudRecently, a junior engineer at one of our clients’ mistakenly ran a few scripts that deleted some important information from a database. In this case, the client wanted to recover only a few records instead of restoring the entire backed-up database. This is a common and valuable use case to consider, considering data loss or undesired human error changes are inevitable. Data loss can happen for any number of reasons, however, including a virus, malicious breaches or a system failure. In this case, luckily, our client was able to recover the lost data instantaneously with the help of CPM and some minimal manual effort. 

When you are running your application in a cloud environment, database backup and restoration are critical elements in a disaster recovery setup. In the AWS cloud, you can implement disaster recovery for your web and app servers with a combination of Amazon Machine Images, Elastic Load Balancer, and Auto Scaling. However, this can be time-consuming and complex and disaster recovery must be planned carefully to make sure all parties understand the procedures in place in case of an outage or failure.

However, preparing for disaster to strike and restoring databases doesn’t have to be complicated.  Cloud Protection Manager (CPM) provides an operational backup and disaster recovery solution for Amazon EC2 environments. Using CPM, it is possible to restore or recover any workload in a matter of seconds. CPM provides an advantage for backing up a Windows EC2 server because it can take an application-consistent snapshot of your EBS volumes.

Let’s walk through our use case we described earlier to understand exactly how our client successfully restored their data.

Use Case

Let’s say Company A is running a web application and database as an MSSQL database on AWS. Instead of using Amazon’s RDS MSSQL, the company prefers to run its database on a Windows instance. This gives the organization the ability to independently manage the database.

Company A uses CPM to perform scheduled backups of its EBS volumes where the MSSQL data is stored. If any data becomes lost or corrupted, CPM can help restore and recover the information. An immediate restore from the last backup can be performed by creating an EC2 instance using CPM. This way, Company A leverages CPM’s ability both to maintain incremental backups of a database and to restore any information in a matter of seconds. Below are the steps Company A follows to restore the lost MSSQL records.

5-Stage Recovery Process:

  1. Create a Windows instance with an MSSQL database and add data to it.
  2. Configure CPM to perform scheduled backups.
  3. An accidental data loss occurs.
  4. Create a dummy EC2 instance using the most recent backup with CPM.
  5. Compare and manually restore the changes from the dummy EC2 instance to the original EC2 instance database.

1. Create a Windows Instance with MSSQL and Data

  • Start by setting up a Windows EC2 instance with MSSQL.
  • Then, create a database with some data. For this post, we created a database named “CPM” with a table named “MasterUser.” These tables are used to manage web application users.

2. Configure Scheduled Backups on CPM

This step is meant to create a policy in CPM to back up the Windows EC2 instance that we created in Step 1.

  • Before creating the backup policy, it is necessary to define a schedule that will help run the policy at a particular time of day.
  • After the schedule is created, define a backup policy. A backup policy helps you define what to back up, how to back it up, and when to perform a backup.
  • To create a backup policy, log in to the CPM console. Navigate to the “Policies” section and define a new policy according to the parameters in the screenshot below:
How To Restore Windows Database Records in the AWS Cloud
Defining a backup policy using CPM.
  • After the policy is created, attach the same policy to the Windows EC2 instance.

3. Loss of Data

  • Now, let’s say some of your critical data is deleted or lost by accident.

4. Create a Dummy EC2 Instance From a Snapshot

  • To begin the recovery process, create a dummy instance from the CPM console by restoring a snapshot of the most recent backup.
  • First, log in to CPM and select the snapshot to be restored from the “Backup Monitor” tab.
How To Restore Windows Database Records in the AWS Cloud
The CPM “Backup Monitor” tab”
  • Next, on the “Recovery Panel” page, select the “Instance” option.
How To Restore Windows Database Records in the AWS Cloud
The CPM “Recovery Panel” page
  • Finally, on the “Instance Recovery” page, set the “Launch from” option to “snapshot.” Now, launch the dummy EC2 instance.
How To Restore Windows Database Records in the AWS Cloud
The CPM “Instance Recovery” page

Note: To avoid an error from conflicting private IP addresses, remove the private IP by using “Advanced Options.”

5. Restore and Recover the Data Manually

  • Once the dummy instance is ready, log in to the recovered instance and compare the original data with the existing database.

 How To Restore Windows Database Records in the AWS Cloud

In this case, the old data had several “MasterUser” records:

How To Restore Windows Database Records in the AWS Cloud
Records from the “MasterUser” database

It turns out that some records from the “MasterUser” table were missing from the original instance, as shown below:

How To Restore Windows Database Records in the AWS Cloud
The “MasterUser” database with missing records

Note: For this example, we have used HeidiSQL as a Microsoft SQL client. Users can use an SQL Server native client or any other client they have experience with..

The missing “MasterUser” records can be migrated to the original database, either by using an SQL data dump or an Insert/Update query. Since only a few records need to be recovered, an Insert/Update query can be used to get the records back. This is shown in the command lines below:

INSERT INTO MasterUser VALUES (‘6’,’max’,’kell’,’San Jose’, ‘California’);
INSERT INTO MasterUser VALUES (‘7’,’phil’,’stones’,’Chicago’, ‘Illinous’);
INSERT INTO MasterUser VALUES (‘8’,’joe’,’alan’,’New York’, ‘New York’);
INSERT INTO MasterUser VALUES (‘9’,’grep’,’kurtis’,’Denver’, ‘Colorado’);

Once the records are updated, you can terminate the dummy EC2 instance. CPM has recovered the data from the snapshots. The size of the recovered data varies depending on when the last snapshot was taken. CPM can help create multiple backup policies for regularly scheduled backups. This helps decrease backup time.

Note: The amount of data one can recover depends upon the interval of snapshots. To minimize the RPO (Recovery Point Objective), we recommend taking regular snapshots, though it might involve some added AWS costs.

Create a backup policy and Recovery your database in minutes 

Database backup is one of the key features of your application setup. A lost application/web server can be configured again from scratch, but lost data can’t be recovered unless you have already performed a backup. There are several methods to back up data, but relying on automated backups is the best way to minimize the risk of human error.

N2WS’ tool, Cloud Protection Manager, is an ideal solution for automated, flexible backup as well as for recovering EC2 instances, Amazon Redshift clusters, RDS, Aurora and DynamoDB databases. With data storage expanding exponentially, CPM ensures that application owners sleep soundly without worrying about data loss. Try it free today. 

 

 

Share this post →

You might also like: