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