How to Restore a SQL Server Database from Multiple Backup (.bak) Files on RDS

To restore a SQL Server database from multiple backup (.bak) files on Amazon RDS for SQL Server, you can use the rds_restore_database stored procedure. While it's true that RDS for SQL Server doesn't support wildcards directly, there is a workaround to restore from multiple backup files.

Here's how you can do it:

1. Ensure all your backup files have the same prefix in the S3 bucket. For example, you could name them like "mydatabase_part1.bak", "mydatabase_part2.bak", etc.

2. Use the rds_restore_database stored procedure with the S3 ARN that includes the common prefix of your backup files, followed by an asterisk (*). This tells RDS to use all files with that prefix for the restore operation.

3. Execute the stored procedure like this:

   exec msdb.dbo.rds_restore_database @restore_db_name='YourDatabaseName', @s3_arn_to_restore_from='arn:aws:s3:::your-bucket-name/your-backup-prefix*';

Replace 'YourDatabaseName' with the name you want to give your restored database, 'your-bucket-name' with your actual S3 bucket name, and 'your-backup-prefix' with the common prefix of your backup files.

Important notes:

• Make sure all the backup files you want to use have the same prefix, and no other files in the bucket use that prefix.
• You need all the backup files for the restore operation to complete successfully.
• This method works for SQL Server version 2014 and later on Amazon RDS.

By using this approach, you can restore your large database from multiple backup files into a single database on Amazon RDS for SQL Server, despite the lack of direct wildcard support.
Sources
Backup and restore strategies for large databases on Amazon RDS for SQL Server | AWS Database Blog
Using native backup and restore - Amazon Relational Database Service