Azure SQL Managed Backup for on-prem databases

Question

Azure SQL Managed Backup for on-prem databases

database diva

Old Hand

Points: 300
More actions
December 7, 2021 at 7:41 pm

#3961427

I have a SQL 2017 on-prem database server that the client wants to back up to Azure storage directly; instead of using local storage. The client wants to save money. The largest database backup is 4-5TB. I read the MS documentation and it states this 'SQL Server Managed Backup to Microsoft Azure uses the Backup to Block Blob feature. The maximum size of a block blob is 200 GB. But by utilizing striping, the maximum size of an individual backup can be up to 12 TB. '
What does it mean by striping?? Do I need to get Premium storage for striping??
Thanks everyone.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

A backup stripe set is a database backup taken to multiple backup files. The above 200GB block blob max size is your guide - use enough files to keep each file under 200GB.

For example, when backing up the database that produces a 5TB backup, then back up to at least 25 files (5TB ÷ 25 files = 200GB/file). To back up to multiple files, just add the additional target files to the backup command, separated by commas.

BACKUP DATABASE [your_database_name] TO
  URL='https://url_path_to_your_Azure_backup_storage/MyDB_Full_20211207_File01.BAK',
  URL='https://url_path_to_your_Azure_backup_storage/MyDB_Full_20211207_File02.BAK',
  URL='https://url_path_to_your_Azure_backup_storage/MyDB_Full_20211207_File03.BAK',
<...and so on...> 
  URL='https://url_path_to_your_Azure_backup_storage/MyDB_Full_20211207_File25.BAK'
WITH FORMAT;

Because the size of each blob file is constrained not by its total size, but instead by the number of blob blocks in the file (50,000), it is possible for a blob to get 'full' before reaching 200GB. (Backup may not 'fill' each block before moving on to the next one). With that info, I would likely use at least 30 files, if not 40 to handle growth.

You will need all of the files in a stripe set in order to restore any databases from it.

Striped backup sets are covered in the online docs for the BACKUP command:

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15

Eddie Wuerch
MCM: SQL

database diva Old Hand Points: 300 More actions · Answer 2

Thank you for the information.

However, I am using Azure SQL Managed BACKUP, not SQL Managed Instance. I am not using BACKUP TO URL.

How would I enable striping with Azure SQL Managed Backup?? Below, is the SQL to enable SQL Managed Backup & create a custom backup schedule; but, I don't see any section to add multiple files for striping like you mentioned. Can you advise?? Can it not be done??

Use msdb;

GO

EXEC msdb.managed_backup.sp_backup_config_basic

@enable_backup = 1,

@database_name = 'my5TBdb',

@container_url = 'https://mybackups.blob.core.windows.net/myssqlbackup',

@retention_days = 30

GO

USE msdb;

GO

EXEC managed_backup.sp_backup_config_schedule

@database_name = 'my5TBdb'

,@scheduling_option = 'Custom'

,@full_backup_freq_type = 'daily'

--,@days_of_week = 'Tuesday'

,@backup_begin_time = '17:30'

,@backup_duration = '02:00'

,@log_backup_freq = '00:05'

GO