Rajendra Gupta
database in restoring mode

An overview of native differential backups in AWS RDS SQL Server

December 9, 2019 by

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.

AWS blog announcement

In this article, we will explore the differential database backups in detail with examples.

Prerequisite

In this article, my SQL instance name is sqlshackdemo, as shown in the following image:

SQL instance name

This instance public accessibility property should be true so that you can connect it using SSMS. Connect RDS instance using the endpoint:

RDS instance connection in SSMS

Create a new database and table with sample data using the following query:

Sample data

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

For a new RDS instance, you might get following error message once you execute the command to take native DB backup:

error while taking 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:

Options group

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:

Modify RDS instance for database backup

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:

Modifying status

Once the instance is available again, execute the command specified above for taking a full database backup:

Available status of AWS RDS SQL Server

We can see that full database backup is in progress. Task id for the backup is 1. Initially, it shows the status CREATED:

full database backup is in progress

We can check the full backup status using the following RDS procedure msdb.dbo.rds_task_status supplying the task id in the parameter:

Sucessful full database backup

We can also browse the Amazon S3 bucket and verify the backup file, as shown below:

backup file in Amazon S3 bucket

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:

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:

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:

Perform a database differential backup in AWS S3 bucket

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:

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:

Error while overwrite a database

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:

RESTORE_DB_NORECOVERY status

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:

database in restoring mode

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

It restores the differential database backup and recovers the database:

database recovery after a differential backup restoration

Refresh the databases in Object Explorer, and it is online:

database status in SSMS

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:

Data validation

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.

Rajendra Gupta
1,659 Views