Rajendra Gupta
S3 bucket ARN

Recover Data in AWS RDS SQL Server

September 27, 2019 by

This article explores the process to recover data in AWS RDS SQL Server and its recent enhancements.

Introduction to AWS RDS

Amazon Web Services (AWS) is a leading cloud solutions provider. It is a highly available solution and provides better infrastructure management with reduced administrative costs – prompting many organizations to move their infrastructure to the cloud. With its pay-as-you-go model, AWS frees organizations from maintaining equipment, resulting in time and cost savings.

AWS offers following support for SQL Server databases:

  • SQL Server on Amazon EC2 instance
  • Amazon Relational Database Service (RDS)

Amazon RDS is a platform as a service (PaaS) solution that provides highly available SQL Server instances while removing the need to maintain underlying infrastructure. It also reduces administrative efforts for backups, database maintenance, minor upgrades, and monitoring. You can learn more about Amazon RDS using this link.

Scenario to recover data in on-premises SQL Server

Let’s explore a scenario involving an on-premises SQL Server instance. Suppose you have a database, TestDB that is backed up regularly. A developer accidentally deletes rows from a table in the database that need to be recovered. You can think of the following solutions:

  • Restore the latest database backup to the SQL instance. You can restore the backup on the same instance with different name, such as TestDB_recover
  • You can extract the data from a cloned database and import it into the original database. You can either use data scripts or use the SSMS import-export wizard

Unfortunately, there are some limitations in the RDS database, and you cannot recover your data from a database backup since it does not allow you to restore the database with a new name from the same backup file. We will cover this scenario and solution to recover data in this article.

Steps to recover data in AWS RDS SQL Server

Create an RDS SQL Server instance

Follow the instructions in the article AWS RDS SQL Server – Launching a new database instance and create a new Amazon RDS SQL instance.

In the following screenshot, we can see the Amazon RDS SQL Server Express edition and DB identifier is myrdsinstance:

AWS RDS SQL Server instance

If you click on myrdsinstance under the DB identifier column, it launches DB instance properties. Under the Connectivity and security tab, you can see the endpoint. We require this endpoint to connect to RDS instance using SSMS.

You should have Public accessibility set to Yes in order to connect it outside the Amazon cloud:

RDS database endpoint and public accessibility

In SSMS, specify the endpoint in the server name and provide the user name and password specified during Amazon RDS instance creation:

Connect to RDS instance using SSMS

By default, you can see the rdsadmin database in AWS RDS SQL Server. The rdsadmin database stores objects that contain procedures to manage databases. We should not make any changes to rdsadmin database and treat this as a system database for Amazon RDS SQL Server instance.

RDSadmin database

Create a new database and objects

Execute the following CREATE DATABASE statement for creating testdb that stores our tables:

Once a database is created, we can use the sp_helpfile command to see data and log file information:

DB file properties

We can then create a Customers table and insert two records in it.

Perform database full backup in AWS S3 bucket

Let’s perform a full database backup of this database and store it in an S3 bucket. We cannot take the database backup directly using the native database backup command in AWS RDS SQL Server. Rather, we need to use stored procedure msdb.dbo.rds_backup_database and specify the S3 bucket name along with the backup file name. You can follow steps in article AWS RDS SQL Server migration using native backups to configure the S3 bucket and configure an Amazon RDS instance with it.

You can copy S3 bucket ARN from S3 bucket properties. Select the S3 bucket and click on Copy Bucket ARN as shown in the following image:

S3 bucket ARN

Execute the backup command mentioned above, and it starts the database backup.

Task ID for the backup

We need to use another stored procedure to track the status of the database backup. Specify the task id from the above screenshot:

You can execute this procedure multiple times to see the status changes.

As shown below, the status changes: Created -> In_PROGRESS -> SUCCESS:

track the backup status

If there is an error while doing a database backup, you can see the error message in the task status:

error message

Once the task status is completed successfully, you can see the backup file in the S3 bucket:

Verify backup file in S3 bucket

Delete data from Customers table

We have database backup now. Suppose someone accidentally deleted records from the database:

Old approach to recover data in AWS RDS SQL Server

We have a database backup before the accidental removal of data. Previously Amazon RDS did not allow restoring a database backup on the same instance if the source database was already present on the RDS instance. You can reference the backup error message in Amazon blogs.

For example, in our case, we cannot restore a database backup from the S3 bucket into an existing RDS instance with a different name such as testdatabase_recoverdata. We will cover the reason for this later.

Amazon blog for RDS

You can restore the backup on a different instance and script out data there. This means you need to manufacture a separate RDS instance for restoration purpose only.

Previously AWS RDS SQL Server allowed two solutions for it.

  • Restore database backup in different RDS instance
  • Use SSMS import-export wizard to recover the data

New approach to recover data in AWS RDS SQL Server

On October 24th, 2018, Amazon announced an exciting feature for database backup and restore. Below is the screenshot from the RDS feature announcement.

Amazon RDS now supports restoring a differently-named database from the backup to an existing RDS instance where the source database exits:

RDS enhacements announcements

Restore database with a new name on existing AWS RDS SQL Server instance

Let’s restore the database from the backup in the S3 bucket. We need to use the same stored procedure, only with different parameters:

It starts a task to restore the database and you can note the task id:

database restore for RDS SQL

Check the status for the database restore using the query below. In the following screenshot, we can see status Success along with duration, source backup file, and database name.

Here is the result:

track the restoration status

Let’s verify the record in the new database by running the query below:

We can see both the records in the restored database. We can script this out and recover data in the source database:

Verify data

We cannot use the import and export wizard in Amazon RDS to export data from a restored database to a source database:

SSMS import and export wizard

Earlier, we mentioned that Amazon RDS does not allow database restoration from backup on the source database RDS instance. Let’s see the reason for it.

Verify database file properties for source and restored database

Execute the following commands on the source database TestDB and new (restored) database TestDB_Recover. It gives us file name, physical file name, and file_guid:

In the following screenshot, note the following:

File_guid for DB files in AWS RDS for SQL Server

  • The file name for data and log files are similar for both source and restored database
  • We have different physical file names for data and log files in both the source and restored database
  • The file_guid is similar for both source and restored database. Amazon RDS initially did not allow restoration due to similar file_guid for both databases. It returns the following error message:

    Database TestDB_clone cannot be restored because there is already an existing database with the same file_guid on the instance.

Conclusion

In this article, we explored data recovery in AWS RDS SQL Server database from the database backups. You can restore a different database from the S3 bucket backup on an existing database as part of this new enhancement. You should be aware of this enhancement for database backup and restoration in RDS SQL Server.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views