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.

• Eddie Wuerch

  SSChampion

  Points: 12540

  More actions

  December 7, 2021 at 10:24 pm

  #3961480

  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

  December 8, 2021 at 3:08 pm

  #3961697

  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