Restoring data from AWS RDS SQL Server snapshot

Making a mistake

Recently, I’ve noticed that some fields of a table contained a lot of trailing white spaces (probably due to not doing a proper trim when executing inserts). So I’ve decided to fix the issue with something like this:

update table1
set field1 = rtrim(field1)

Trailing white spaces also existed in another field of the same table, so I did another update like so:

update table1 set field1 = rtrim(field2)

update table1
set field1 = rtrim(field2)

Oops! I forgot to change set field1 to set field2. Now the data in field1 got overwritten by the data from field2. Yes, I should’ve used transactions and yes, I could have used a single update statement instead of copy/paste, but beating myself up wasn’t gonna help. So I had to do a restore from a backup.

Restoring from a RDS snapshot

Good thing is that the database is backed up every day automatically by AWS RDS. So what you need to do is log in to AWS console, go to Snapshots of RDS section, choose the snapshot you want to restore, and under Actions choose Restore Snapshot.

Choose the instance class that’s right for the database an give it an identifier that is different from the original DB instance. Set the Network configuration the same as your original DB. You’d probably want to set the Auto Minor Version Upgrade to No.

Once you click Restore DB Instance, a new DB instance will be created and it will go through several phases: Creating, Backing Up, Modifying. Once it’s available, you have to change it’s security group, because restored snapshots are set to default security group. So select the new instance and select Modify from Instance actions list.

Remove the default security group and select the one you want from the list. While you are at it, set the Backup retention period to 0 days. This will remove the backup snapshot created during restore (we don’t need a backup of a backup). The instance will go through another Modifying phase once you click Continue.

Link the new instance

In SQL server you can create a link to another SQL server instance and interact with it. In order to link to the newly created instance, you need to figure out the internal ip address. Use nslookup on the endpoint of the new instance:

nslookup dbbackup.cj2eiomy9xj8.us-west-1.rds.amazonaws.com

nslookup dbbackup.cj2eiomy9xj8.us-west-1.rds.amazonaws.com

This will give you the address like:

Non-authoritative answer: Name: dbbackup.cj2eiomy9xj8.us-west-1.rds.amazonaws.com Address: 10.0.10.11

Non-authoritative answer:
Name:    dbbackup.cj2eiomy9xj8.us-west-1.rds.amazonaws.com
Address:  10.0.10.11

Note that nslookup has be executed from the EC2 instance that is within the same VPC network.

Use that ip address to create the linked server. From SQL Server Management Studio, execute these queries:

EXEC master.dbo.sp_addlinkedserver @server = N'dbbackup', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'10.0.10.11';

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'dbbackup', @useself=N'False',@locallogin=NULL,@rmtuser=N'username',@rmtpassword='password';

Set datasrc, username, and password to match your configuration.

Once the linked server has been created, you can access it with queries like this:

select * from dbbackup.mydb.dbo.table1

Copy data from restored DB

In my case, since I’ve overwritten the data of one field with the data of another field, my update query will look something like this:

update a
set a.field1 = rtrim(b.field1)
from table1 a
join dbbackup.mydb.dbo.table1 b
on a.id = b.id

Make sure data is right with a select and you’re done.

Clean up

We don’t want the new instance costing us any more than necessary, so after data integrity has been confirmed, simply delete the instance from RDS.

The whole process can take anywhere from 30 mins to more than an hour depending on the size of your database and the instance class chosen.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.