In this article, we will explore how to split native backup and restore for AWS RDS SQL Server from the AWS S3 bucket.

Introduction

When deploying SQL Server in Amazon Web Services (AWS) cloud infrastructure, there are two ways to do it: AWS Managed RDS SQL Server and Customer Managed SQL Server. In this article, we will focus on AWS Managed RDS SQL Server.

In traditional SQL Server environments, whether on-premises or on an EC2 instance, we define the database backup policies. When taking a full backup for a large database, it is often necessary to split it into multiple files. This allows us to take advantage of multiple IOPS while writing the backups to the media and also helps in managing space constraints. Additionally, storing backup files on the cloud makes it easier to download smaller files, as large backup files may take longer depending on network bandwidth.

But what about AWS RDS SQL Server? Can we take split native database backups for it? Let’s find out.

Split Backups for AWS RDS SQL Server Databases

RDS automatically takes full and incremental snapshots for your SQL instances, which can be used for recovery purposes. However, if you want to take split backups for your RDS SQL Server databases, you can follow these steps:

1. Define the IAM policy and provide permissions for the AWS S3 bucket.
2. Create a Role for AWS RDS SQL Server that uses the IAM policy created in the previous step.
3. Create an option group for SQL Server and add the SQLSERVER_BACKUP_RESTORE feature.
4. Modify RDS instance properties to use the new option group instead of the default group.

Once these steps are completed, you can proceed with taking split backups for your RDS SQL Server databases.

Taking Split Native Backups for AWS RDS SQL Server

To take a split native backup for an RDS SQL Server database, you can use the stored procedure msdb.dbo.rds_backup_database. By default, AWS native backup creates a single file irrespective of the database size. However, by specifying the @number_of_files parameter, you can split the backup into multiple files.

Here is an example of taking a split native backup for the AdventureWorks2017 database:

EXEC msdb.dbo.rds_backup_database
  @source_db_name = 'AdventureWorks2017',
  @s3_arn_to_backup_to = 'arn:aws:s3:::sqlshackbackup/AdventureWorks2017_Split*.bak',
  @number_of_files = 5,
  @overwrite_S3_backup_file = 1;

Once the backup task is completed, you will have multiple split backup files in your S3 bucket.

Restoring Split Native Backups in AWS RDS SQL Server

To restore split backups stored in the AWS S3 bucket to an RDS SQL Server instance, you can use the stored procedure msdb.dbo.rds_restore_database. It is important to note that you cannot specify a particular backup file for restoration, as SQL Server requires all the split backup files for the restoration process.

Here is an example of restoring split native backups for the AdventureWorks2017 database:

EXEC msdb.dbo.rds_restore_database
  @restore_db_name = 'AdventureWorks2017_New',
  @s3_arn_to_restore_from = 'arn:aws:s3:::sqlshackbackup/AdventureWorks2017_Split*';

Once the restoration process is completed, the database will be restored with the specified name.

Conclusion

In this article, we have learned how to take split native backups and restore them in AWS RDS SQL Server. By splitting backups into multiple files, we can take advantage of various benefits such as improved IOPS and easier management of backup files. This can be particularly useful when dealing with large databases in the AWS cloud environment.