I was recently tasked with backing up some databases to an Azure Storage Account, using BACKUP TO URL in SQL Server. It is quite simple. Create a Shared Access Token for the Storage Container and then create a CREDENTIAL in SQL Server using “shared access token” as identity and the access token as password.
In an Azure Storage Container, one writes either page blobs or block blobs. Think of a blob as a file in a file system. When SQL Server backups to an URL, it will create a block blob. The size limitation for a block blob in an Azure Storage Container is 200GB. For a large database, the backup file will be larger than 200GB and you will get some funny I/O device errors. The error message will not tell you that you hit the 200GB threshold, it will just tell you there was an I/O failure on the backup device. And that error won’t come at the start of the backup. It will come when SQL Server has written 200GB.
Why 200GB? The limitation is actually 50.000 blocks. A block can be up to 4MB. 50.000x4MB ~ 200GB. Use MAXTRANSFERSIZE for the backup command to set this size. Also use the BLOCKSIZE parameter for the backup command and set it to 64kB (65536 bytes) to maximize the maximum size of a single blob created by the 50 000 blocks in a blob.
BACKUP DATABASE [Underlandet] TO URL='https://saunderlandetdatabasebackups/productionbackups/underlandet.bak WITH BLOCKSIZE=65536, MAXTRANSFERSIZE 4194304, FORMAT, INIT, CHECKSUM, COMPRESSION
Ok, now that’s quite a limitation you might think. Large databases will get large backup files. Even with compression, large enough databases will have backup files larger than 200GB. You could of course backup different file groups to different backup files to overcome this limitation. But sooner or later, as your database and your file groups grow, you will hit the 200GB limit.
The way to overcome this limit is to stripe the backups to multiple files. You stripe a backup to multiple files by simply adding multiple URL=<storage container path> to your backup command. Something like this
BACKUP DATABASE [Underlandet] TO
URL='https://saunderlandetdatabasebackups/productionbackups/underlandet-1-of-2.bak,
URL='https://saunderlandetdatabasebackups/productionbackups/underlandet-2-of-2.bak
WITH BLOCKSIZE=65536, MAXTRANSFERSIZE 4194304, FORMAT, INIT, CHECKSUM, COMPRESSION
You can stripe a backup to up to 64 files. This gives you 200GB x 64 ~ 12,8TB, which at least for all the databases I normally work with is enough. If you still hit the size limit, you will have to split your database into multiple file groups and backup individual file groups instead of the whole database at once. If your database is THAT large, you probably have it split into multiple file groups already anyway (some of the file groups being read-only and not backuped with the same cadence as the read-write file groups).