This article explores the differential backups in AWS RDS SQL Server using recovery scenarios.
Introduction
Amazon RDS offers a platform as a service (PaaS) solution for highly available SQL instances. We do not maintain the infrastructure in RDS and pay only for the resource usage. It is an important aspect to know the way of data recovery in case of any unfortunate scenario. You can learn more about AWS RDS by checking the articles in AWS RDS category.
In the previous article, we explored Recover Data in AWS RDS SQL Server using native full database backup and restore. Previously AWS RDS SQL Server did not support differential and log backups restore. Recently AWS published a blog announcement stating support of differential and log restoration.
In this article, we will explore the differential database backups in detail with examples.
Prerequisite
- You should have an existing RDS SQL Server instance. You can follow the article AWS RDS SQL Server – Launching a new database instance for creating a new Amazon RDS SQL instance
- You should have an AWS S3 bucket in the same region. You can follow the AWS RDS SQL Server migration using native backups article for configuring an S3 bucket
In this article, my SQL instance name is sqlshackdemo, as shown in the following image:
This instance public accessibility property should be true so that you can connect it using SSMS. Connect RDS instance using the endpoint:
Create a new database and table with sample data using the following query:
|
1 2 3 4 5 6 7 8 |
CREATE DATABASE Employees; Create table EmpData ( EmpID int, EmpName varchar(30) ) Insert into EmpData values (1, 'Rajendra') Insert into EmpData values (2, 'Kusum') |
Perform a full database backup of AWS RDS SQL Server in AWS S3 bucket
As we know, a base for a differential backup is a full backup in SQL Server. It takes database backup of extents changed from the last full backup.
We need the following parameters in the command msdb.dbo.rds_backup_database:
- @Source_db_name: It is the database for which we want to take backup
- @S3_arn_to_backup_to: Specify the Amazon S3 ARN, followed by a backup file name
- @overwrite_s3_backup_file: If a backup file with a similar name exists, you can specify this parameter to overwrite an existing file. We can use a unique name if you do not want to overwrite the file
|
1 2 3 4 |
EXEC msdb.dbo.rds_backup_database @source_db_name = 'Employees', @s3_arn_to_backup_to = 'arn:aws:s3:::rdstestsql/Employees_Full.bak', @overwrite_S3_backup_file = 1; |
For a new RDS instance, you might get following error message once you execute the command to take native DB backup:
By default, the RDS SQL instance does not allow database backup and restore. Navigate to Options groups on the Amazon RDS homepage and select the option group that has SQLSERVER_BACKUP_RESTORE. If you do not have any options group, you can create an option group and add this parameter SQLSERVER_BACKUP_RESTORE:
Once we created an options group, modify the RDS instance properties. We can change the option group from default to the previously created options group (rdstestgroup in my case). We also need to apply changes immediately else it will modify RDS instance in the next scheduled maintenance window. You can see the scheduled maintenance window in the following screenshot:
It might take some time to apply changes. You can monitor the status of AWS RDS SQL Instance. Its status should be available. In the following screenshot, it shows the Modifying status that shows changes is in progress:
Once the instance is available again, execute the command specified above for taking a full database backup:
We can see that full database backup is in progress. Task id for the backup is 1. Initially, it shows the status CREATED:
We can check the full backup status using the following RDS procedure msdb.dbo.rds_task_status supplying the task id in the parameter:
|
1 2 |
EXEC msdb..rds_task_status @task_id = 1; |
We can also browse the Amazon S3 bucket and verify the backup file, as shown below:
Perform a database differential backup in AWS S3 bucket
Once a full backup is completed, let’s do some insert, update operations in the database. It will generate some activity in the differential database backup:
|
1 2 3 |
INSERT INTO EmpData VALUES(3, 'Akshita'); INSERT INTO EmpData VALUES(4, 'Manoj'); Update EmpData set EmpName='John' where EmpID=4 |
We use the same stored procedure rds_backup_database for differential database backup. We can specify @type=’DIFFERENTIAL’ in the query for taking a differential backup:
|
1 2 3 4 5 |
EXEC msdb.dbo.rds_backup_database @source_db_name = 'Employees', @s3_arn_to_backup_to = 'arn:aws:s3:::rdstestsql/Employees_diff.bak', @overwrite_S3_backup_file = 1, @type = 'DIFFERENTIAL'; |
In the backup status command, we can see task_type BACKUP_DB_DIFFERENTIAL that shows it is executing a differential database backup for the AWS RDS SQL database:
Once the differential backup is completed (lifecycle=SUCCESS), let’s use the following query and intentionally perform some changes in the table:
It updates an existing record in the EmpData table and removes a row for EmpID 4:
|
1 2 |
Update EmpData set EmpName='XYZ' where EmpID=3 Delete from EmpData where EmpID=4 |
Perform an AWS RDS SQL Server database restoration from a full backup stored in AWS S3 bucket
We want to recover data in this EmpData table, and it will require the restoration of a full and differential database backup on a separate database. Later, we can export the data from a restored database to the original database.
Note: We will create a new database from the full and differential database backup for demo in this article.
We use the msdb.dbo.rds_restore_database command for database restoration. It requires the following parameters:
- @Restore_db_name: It is the restored database name
- @s3_arn_to_restore_from: It is the full path of the database backup. You can get the ARN from the Amazon S3 bucket
- @with_norecovery: We want to restore further differential database backup after the full backup restoration. It requires full backup restoration in the NORECOVERY mode. In AWS RDS SQL Server, we need to use parameter @with_norecovery=1
- @Type: We specified the backup file type in the @s3_arn_to_restore_from parameter. For a full backup restoration, use the @type=Full parameter
We cannot overwrite or replace a database in the AWS RDS SQL Server instance. If you try to restore an existing database, it gives the following error message:
Execute the query for database backup restoration and check the task status. It shows the task_type value as RESTORE_DB_NORECOVERY for the database restoration in NORECOVERY mode:
|
1 2 |
EXEC msdb..rds_task_status @task_id = 6 |
Once the database restoration is completed, we can refresh the database in Object Explorer of SSMS. It shows the database in the restoring mode. We cannot access the database in the restoring mode, but SQL Server allows you to restore subsequence backups:
Perform an AWS RDS SQL Server database restoration from a differential backup stored in AWS S3 bucket
Now, let’s restore the differential backup on the Employee_restore database. We need to use the following parameters in this command:
- @Restore_db_name: It should be the same database restored earlier from the full backup in NORECOVERY mode
- @s3_arn_to_restore_from: It is the full path of the differential database backup. @with_norecovery: We want to restore a differential database backup after the full backup restoration and recover the database to access it. Use the parameter @with_norecovery=0 for database recovery
- @Type: We specified the backup file type in the @s3_arn_to_restore_from parameter. For a differential backup restoration, use the @type=DIFFERENTIAL parameter
|
1 2 3 4 5 |
EXEC msdb.dbo.rds_restore_database @restore_db_name = 'Employees_restore', @s3_arn_to_restore_from = 'arn:aws:s3:::rdstestsql/Employees_diff.bak', @with_norecovery = 0, @type = 'DIFFERENTIAL'; |
It restores the differential database backup and recovers the database:
Refresh the databases in Object Explorer, and it is online:
Let’s compare the records in the original database and the restored database. We can see that records exist in the restored database. We have restored the database to a state that exists at differential backup completion:
Note: As per official AWS RDS SQL Server, it supports for transaction log backup with point-in-time recovery as well. We restore transaction log backup using the msdb.dbo.rds_restore_log stored procedure. However, it does not show the procedures for taking a transaction log backup for the RDS SQL database. I will be in touch with the Amazon support center on this and cover point-in-time recovery in subsequence article. You can go through official documentation Importing and Exporting SQL Server Databases.
Conclusion
In this article, we explored database recovery in the AWS RDS SQL Server database using full and differential database backups. You should explore these databases and explore it in your environment.
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
- 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