Sql-server – Managed backup large database to Azure

azure-blob-storagebackupsql serversql-server-2017

Got a bit of an issue here that I need some help to solve.

Hosting a database, that is about 400Gigs. This is giving us a little headache getting it to work with Managed Backup to Azure Storage Container.

This is the error we get

10/21/2018 12:45:18,spid149,Unknown,Write to backup block blob device https://NAME.blob.core.windows.net/CONTAINER/DB_NAME_20181021120709+02.bak failed. Device has reached its limit of allowed blocks.

The setup was done with 

EXEC managed_backup.sp_backup_config_basic

@database_name = 'DB_NAME',
@enable_backup=1,  
@container_url = 'https://NAME.blob.core.windows.net/CONTAINER',  
@retention_days=30;


USE msdb;  
GO  
EXEC managed_backup.sp_backup_config_schedule   
     @database_name =  'DB_NAME'  
    ,@scheduling_option = 'Custom'  
    ,@full_backup_freq_type = 'Daily'  
    ,@backup_begin_time =  '00:30'  
    ,@backup_duration = '02:00'  
    ,@log_backup_freq = '00:05'  
GO

And the log backup is running fine, so we know the credential is ok. But it will not make a backup of the database, since it is too large to fit, so it needs to be split up. But how to do that?
When I run a manual backup like this

Use MSDB  
Go  
EXEC managed_backup.sp_backup_on_demand  
 @database_name = 'DB_NAME'  
,@type = 'Database'

It does split it up into two pieces in the Azure container. So how do I get the automatic job to split up the BAK file.

We are running on SQL Server 2017.

Hope someone has a good idea, since Google doesn't give anything good.

Thanks

Best Answer

Reference:

With your size you should be able to use page blog and specify MAXTRANSFERSIZE = 4194304 and BLOCKSIZE = 65536. If you need to backup databases greater than 1TB use block blob and striped backup. To use block blobs, you need to create a Shared Access Signature (SAS), and create a credential for the SAS. There are examples in the third reference above.

There are bunch of questions answered in the comment section of the first reference that will help in case if you run into any issues.

Related Question