December 7, 2021 at 7:41 pm
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.
December 7, 2021 at 10:24 pm
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
December 8, 2021 at 3:08 pm
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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy