5

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

1

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.

  • Hi, Sadly this doesn't really help that much. We want to run this as managed backup job and can't find anything about how to split into several files with managed backup. I have been reading the links you posted and they only explain it as a normal backup to URL. Right now I have to get it to run with a SQL job that starts the manual job, but this timesout after 120seconds and then says failure, but the backup completes. But again this is not the way we want it to run. – dkw Oct 25 '18 at 8:02
  • First url shows you an example of how to split into several files. – SqlWorldWide Oct 25 '18 at 12:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.