How To Restore Windows Database Records in the AWS Cloud

Recently, 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 N2WS 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.

AWS Backup Checklist

Fill in the gaps in your backup and DR strategy

Fortify your data backup strategy across every critical dimension—from security to disaster recovery to cost savings.

N2WS Backup & Recovery provides an operational backup and disaster recovery solution for Amazon EC2 environments. Using N2WS, it is possible to restore or recover any workload in a matter of seconds. N2WS 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 N2WS to perform scheduled backups of its EBS volumes where the MSSQL data is stored.

If any data becomes lost or corrupted, N2WS can help restore and recover the information. An immediate restore from the last backup can be performed by creating an EC2 instance using N2WS. This way, Company A leverages N2WS’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:

Create a Windows instance with an MSSQL database and add data to it.
Configure N2WS to perform scheduled backups.
An accidental data loss occurs.
Create a dummy EC2 instance using the most recent backup with N2WS.
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 N2WS

This step is meant to create a policy in N2WS 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 N2WS console. Navigate to the “Policies” section and define a new policy according to the parameters in the screenshot below:

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 N2WS console by restoring a snapshot of the most recent backup.
First, log in to N2WS and select the snapshot to be restored from the “Backup Monitor” tab.

Next, on the “Recovery Panel” page, select the “Instance” option.

Finally, on the “Instance Recovery” page, set the “Launch from” option to “snapshot.” Now, launch the dummy EC2 instance.

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.

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

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

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. N2WS has recovered the data from the snapshots. The size of the recovered data varies depending on when the last snapshot was taken. N2WS 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 Backup & Recovery (CPM) 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, N2WS ensures that application owners sleep soundly without worrying about data loss.