search

microsoft / SQLServerPSModule

This repo is the home of SQL Server PowerShell Module development.
MIT License
45 stars 1 forks source link

Backup-SqlDatabase File Splitting #64

Closed van-thieu closed 1 day ago

van-thieu commented 7 months ago

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

Example: Backup-SqlDatabase -MaxFileSize 150GB

Matteo-T commented 4 days ago

@erinstellato-ms - what do you think?

erinstellato-ms commented 1 day ago

@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 commented 1 day ago

@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 commented 1 day ago

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 commented 1 day ago

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 commented 1 day ago

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.