AWS RDS SQL Server recovery models, backups and restores

This article will review about the recovery models backups and restore options in available AWS RDS SQL Server.

Recovery Models

With Automatic Backups:

By default, when a new database is created it is created in simple recovery model. If the automatic backups are enabled on the database instance, with in five minutes the newly created database which is in simple recovery model will be changed to full recovery model.

If the database is put into simple recovery model from full recovery model and the automatic backups are enabled on the database instance, after 5 minutes the database is put back into full recovery model and a snapshot of the AWS RDS SQL Server database instance is taken.

Without Automatic Backups:

When the database instance is launched without automatic backups, the new database created will be in simple recovery model. If we change the recovery model from simple to full, after 5 minutes the database is put back into simple recovery model.

With Multi-AZ deployment:

When the database is deployed in multiple AZ. The database is always in full recovery model. If you try to change the database to simple recovery model, AWS RDS immediately changes the recovery model back to full and rebuilds the mirror automatically. During the rebuild the secondary is degraded until it is ready for failover.

So, do not change the recovery model on your databases even though AWS RDS SQL Server allows you to change.

Backups

AWS RDS takes backup of your database instances automatically and save these backups as per retention period you selected while creating the instances.

A full snapshot of your database instance is taken every day during the specified backup window and incremental snapshots are taken every 5 minutes. These backups include entire database instance not the individual databases. The automated backups can be taken when the database instance is in ACTIVE state only.

Enable Automatic Backups:

To enable automatic backups, we must select a positive value greater than zero in backup retention period while creating the database instance

We can also modify the retention period after creating the database instance by selecting the database instance and click on Modify.

To get the latest backup information Navigate to Automated backups. You can the list of active backups by instance wise.

Disable Automatic Backups:

To disable automatic backups, select the retention period as zero while creating the database instance or modify the database instance if it is already created.

Manual Backups:

Click on Databases and select the database instance you want to backup. Click on the Actions drop down and select Take Snapshot

In Take DB Snapshot window, Input the snapshot name and click on Take Snapshot.

Now navigate to Snapshots and you can see the list of available snapshots of your database instances.

Restore

Restore from snapshots:

You can always restore the backup/snapshot to a new database instance by providing the name of the new database instance. You cannot restore the snapshot to existing database instance.

Navigate to Snapshots, select the snapshot you want to restore to a new instance. Click on Actions drop down and select Restore Snapshot

In Restore DB instance page, Provide the new database instance name, other configurations and click on Restore DB instance at bottom of the page. Navigate to the Databases and you can see the new database which you restored.

The default security group is associated with the restored instance. Once the restore is completed you must select the database instance and modify it to associate with your custom security group.

Restoring from Automated Backups:

The database instance can be restored to point in time based on the retention period you have set. Restoring the database instance to point in time recovery will create a new database instance. The new instance will be created with default security group and you must modify the database instance to have custom security group.

Navigate to the Automated backups. You can see the details of backup like earliest restorable time and latest restorable time. You can restore the database instance to any point between the earliest restorable time and latest restorable time. These timing depend on the retention period you set for the database instance.

Navigate to the Databases. Select the database you want to restore the point in time. Click on the Actions drop down and select Restore to point in time.

In Launch DB Instance page, select the latest restorable time if you want to restore the database instance to latest possible time. You can choose Custom and specify the date and time you want to restore the database instance to specific time.

Input the new database instance name and other options and click on Launch DB Instance at bottom of the page. A new database instance is created with new instance provided.

Native Backups

AWS RDS SQL Server provides an option to backup the individual databases of the instance to S3. We cannot issue a backup command on the database take backup the databases to physical drives as you can access the drives and Backup Database permission is denied on the database.

Below are the steps to take database backups to S3.

Configure the Option Groups
Modify the database instance to use the new option group
Backup and Restore using procedures

Configure Option Groups:

Navigate to Option Groups and click on Create Group. Give the name, description, SQL Server engine and the version and click Create.

Navigate to Options Group, select the group you created and click on Add option.

In option details page, select the option SQLSERVER_BACKUP_RESTORE option. Select the IAM role if you already have one or create new IAM role. Select the S3 backut where backup is stored.

Select Yes for Apply immediately and click on Add Option.

Modify the database instance:

Once the option is added to Option group, Navigate to Database and select the database instance. Click on Modify. In database options section, select the new option group you created and click Continue at the bottom of the page. Select Apply Immediately in scheduling of the modification and click on Modify DB Instance.

Backup database:

Now login to AWS RDS SQL Server instance from SQL Server management studio using the endpoint of the database instance.

In msdb database, execute rds_backup_database to backup your database to S3. Replace the S3 arn and database name with yours.

Navigate to Amazon S3, Select the bucket to get the arn of the bucket Click on Copy Buck ARN to copy the S3 arn and use it in the procedure.

Below is the T-SQL script to back up the database to S3.

EXEC rds_backup_database @source_db_name = 'SampleDatabase'

,@S3_arn_to_backup_to = 'arn:aws:s3:::nativebackups3/SampleDatabase.bak'

,@overwrite_S3_backup_file = 1

Once you execute this procedure a task is created, and the status of the backup is updated in lifecycle column.

You can check the status of the task by executing the rds_task_status procedure with database or task id parameter.

Once the status is success, the backup file is stored in S3 bucket you specified. Navigate to the S3 bucket and check for the backup file.

Restore database:

Similarly, AWS RDS SQL Server provides rds_restore_database to restore the DATABASE from backup stored in S3

Login to database instance from SQL Server management studio and in msdb execute the stored procedure by replacing the database name and s3 bucket arn.

Below is the T-SQL script to restore database from backup in S3 bucket.

1 2	EXEC rds_restore_database @restore_db_name = 'NewSampleDB' ,@s3_arn_to_restore_from = 'arn:aws:s3:::nativebackups3/SampleDatabase.bak'

Once you execute this procedure, a restore task is created and you can check the status of task using rds_task_status procedure.

Author
Recent Posts

Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies