SQL Server Combine Partioned Backups Files to One?

Question

Scenario:

Every day you receive a SQL Server 2016 database backup set that is composed of multiple partitioned .bak files of size 1GB each:

backup1.bak (1GB)
backup2.bak (1GB)
backup3.bak (1GB)
...
backup20.bak (1GB)

This database is consistently growing, but the backups will always be partitioned by 1GB size. 20GB database contains 20 * 1GB partitions, 21GB database contains 21 * 1GB partitions and so on. You DO NOT have control of the backup producer. You need to restore this SQL Server instance on Amazon RDS for SQL Server.

Problem:

As noted in Amazon RDS documentation:

You can't back up to or restore from more than 10 backup files at the same time.

Considerations:

Also noted in that same doc:

RDS supports native restores of databases up to 16 TB

Question:

It seems like combining these partitioned backup files into one .bak file is the best approach for use on RDS. As the consumer, HOW do you combine these .bak files into one when you have no control of how they are produced?

The first solution that comes to mind is to spin up an EC2/SQLServer instance, restore the database from the partitions, perform a backup as one file, load one file to S3, restore RDS from one file on S3. But is this really the best option? Is there a more elegant way?

Answer 1

Every day you receive a SQL Server 2016 database backup set that is composed of multiple partitioned .bak files of size 1GB each

You mean a striped backup? If that's so, the lengths they'd have to go to just to get 1 GB files is... special. These are special people indeed. If you don't mean this, please explain what a partitioned backup is, as I'm unfamiliar with the terminology in SQL Server.

You DO NOT have control of the backup producer. You need to restore this SQL Server instance on Amazon RDS for SQL Server.

I'm not sure if this belongs on dba.se or writing.se because this is quite the introduction to a horror novella.

You can't back up to or restore from more than 10 backup files at the same time.

Definitely a horror novella. Truly though, since in RDS you must use Amazon's procedures I don't see a way around this. There are crafty ways for sure, but I don't think one which will probably work with Amazon RDS.

It seems like combining these partitioned backup files into one .bak file is the best approach for use on RDS. As the consumer, HOW do you combine these .bak files into one when you have no control of how they are produced?

You don't, if you're forced to use what is provided. I do not know of any utilities that will read the SQL Server special usage of MTF and combine it into a single file and re-write the header information in a way that SQL will understand.

There are some super sketchy ways around migrating all the backups into a single file, but I doubt the S3 system will support alternate streams. Even if it did, you'd have to list each stream so you'll still hit the 10 limit.

The first solution that comes to mind is to spin up an EC2/SQLServer instance, restore the database from the partitions, perform a backup as one file, load one file to S3, restore RDS from one file on S3. But is this really the best option? Is there a more elegant way?

Sounds like you're getting clouded super hard here.. and yeah probably the only way to go if you're still wanting the cloud. You could also move away from RDS, the cloud, or double win and do both. It might even be easier to ask whomever produces the backups to do something else, anything else. Again, this all assumes this is a striped backup. If it's a full with differential and logs, that'd be a different story... but it at least smells like a striped backup.

Answer 2

I will assume that the backup is created using the ola.hallengren script, there is a MaxFileSize parameter that allows you to set the maximum file size.

As a rule, this is used to speed up the creation of backups, as it allows the use of multi-threaded writing to disk.

The first solution that comes to mind is to spin up an EC2/SQLServer instance, restore the database from the partitions, perform a backup as one file, load one file to S3, restore RDS from one file on S3. But is this really the best option? Is there a more elegant way?

For the situation you described, this seems to be the only possible solution. It is not possible to merge a SQL Server database backup split into several files without restoring it.

---

A little advice.

In SQL Server 2022 you can backup and restore directly from S3. You can save a little time and free disk space on the intermediate server if you backup the intermediately restored database directly to S3 in one file

BACKUP DATABASE <db-name> TO URL = ' s3://s3.<region-name>.amazonaws.com:443/<backet-name>/<db-name>.bak' WITH COMPRESSION

Instead of making a backup locally and transferring it separately.