Azure SQL Managed Backup for on-prem databases

  • 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.

  • 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
    <...and so on...>

    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:


    Eddie Wuerch
    MCM: SQL

  • 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;


    EXEC msdb.managed_backup.sp_backup_config_basic

    @enable_backup = 1,

    @database_name = 'my5TBdb',

    @container_url = '',

    @retention_days = 30


    USE msdb;


    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'


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

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