Recover Data in AWS RDS SQL Server

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:

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:

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

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.

Create a new database and objects

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

1	CREATE DATABASE TestDB;

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

USE testdb;

sp_helpfile;

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

Create table Customers

( CustID int,

CustName varchar(30)

)

Insert into Customers values (1, 'Rajendra')

Insert into Customers values (2, 'Kusum')

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.

exec msdb.dbo.rds_backup_database

@source_db_name='TestDB', @s3_arn_to_backup_to='arn:aws:s3:::rdstestsql/TestDB_Full.bak',

@overwrite_S3_backup_file=1;

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:

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

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

1	exec msdb..rds_task_status @task_id= 15

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

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

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

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

Delete data from Customers table

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

1	Delete from Customers where CustID=2

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.

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:

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:

exec msdb.dbo.rds_restore_database

@restore_db_name='TestDB_Recover',

@s3_arn_to_restore_from='arn:aws:s3:::rdstestsql/TestDB_Full.bak';

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

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.

1	exec msdb..rds_task_status @task_id= 16

Here is the result:

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

1	Select * from TestDB_Recover.dbo.customers

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

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

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:

Use TestDB

SELECT name,physical_name, file_guid FROM sys.database_files;

Use TestDB_Recover

SELECT name,physical_name, file_guid FROM sys.database_files;

In the following screenshot, note the following:

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.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023