Backup-SqlDatabase File Splitting

[van-thieu]

Possible to include a maximum .bak file size and have it automatically split backup into multiple files?

Example:
Backup-SqlDatabase -MaxFileSize 150GB

[Matteo-T]

@erinstellato-ms - what do you think?

[erinstellato-ms]

@van-thieu Can you provide the scenario where you want to include the maximum .bak file size? I'm interested to understand how and why you would use that, as there's no option to specify a max file size in the T-SQL for BACKUP. Thanks!

[van-thieu]

@van-thieu Can you provide the scenario where you want to include the maximum .bak file size? I'm interested to understand how and why you would use that, as there's no option to specify a max file size in the T-SQL for BACKUP. Thanks!

When backing up to a storage account, will need to split a large bak file of a few terabytes.

[erinstellato-ms]

When backing up to a storage account, will need to split a large bak file of a few terabytes.

@van-thieu Ok, but there's no equivalent T-SQL syntax for setting a max file size for a backup, and I would argue there shouldn't be - what if the size you specify is too small? Will the backup fail?

You can backup to multiple files using two or more files like this:

Backup-SqlDatabase -ServerInstance "computer\instance" -Database "DBName" -BackupFile "C:\backups\DB1.bak", "C:\backups\DB2.bak"

Let us know if that doesn't help address what you're looking to do.

[van-thieu]

When backing up to a storage account, will need to split a large bak file of a few terabytes.

@van-thieu Ok, but there's no equivalent T-SQL syntax for setting a max file size for a backup, and I would argue there shouldn't be - what if the size you specify is too small? Will the backup fail?

You can backup to multiple files using two or more files like this:

Backup-SqlDatabase -ServerInstance "computer\instance" -Database "DBName" -BackupFile "C:\backups\DB1.bak", "C:\backups\DB2.bak"

Let us know if that doesn't help address what you're looking to do.

It wouldn't fail per se, rather it should automatically partition the number of bak files based upon the specified limit per file, ie, 150GB per file.

This can be done by getting the total db size and divide by 150gb.

[erinstellato-ms]

It wouldn't fail per se, rather it should automatically partition the number of bak files based upon the specified limit per file, ie, 150GB per file.

This can be done by getting the total db size and divide by 150gb.

@van-thieu This assumes that the engine would know the size of the database in advance. While SQL runs a compression pre-allocation algorithm to calculate how much space it will need when a compressed backup starts, it can be incorrect. This is not something we will implement, thanks.