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:
CREATE DATABASE TestDB;
Once a database is created, we can use the sp_helpfile command to see data and log file information:
We can then create a Customers table and insert two records in it.
Create table Customers
( CustID int,
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.
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:
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:
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:
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.
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:
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:
SELECT name,physical_name, file_guid FROM sys.database_files;
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.
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.
While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at email@example.com
View all posts by Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
- Import data from multiple Excel files in SQL Server tables using an SSIS package - December 13, 2019
- An overview of native differential backups in AWS RDS SQL Server - December 9, 2019
- An overview of SQL Server data types - December 9, 2019