Recently I ran into an error when I tried to backup a database to Azure Blob storage. The error was this:
> 1117(The request could not be performed because of an I/O device error.)
The database .mdf file was about 80gb.
Upon reading a few postings, it turns out Azure Blob storage does have some type of limitation. If I understand correctly, there are only 50,000 blocks available and each block can hold roughly 4Mb.
I played around with the following BACKUP DATABASE TO URL with varying parameters:
1.
BACKUP DATABASE [mydb] TO URL WITH INIT
(failed)
2. BACKUP DATABASE [mydb] TO URL WITH INIT, COMPRESSION
(succeeded)
3. BACKUP DATABASE [mydb] TO URL WITH INIT, COMPRESSION, MAXTRANSFERSIZE = 4194304
(failed)
4. BACKUP DATABASE [mydb] TO URL WITH INIT, COMPRESSION, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536
(succeeded)
When the operation was successful, the backup file produced was about 7.8Gb. Again, the .mdf file was about 80Gb.
I'm a bit confused as to how the parameter BLOCKSIZE
is used as part of the BACKUP T-SQL command. Can someone explain why the operation would fail when this parameter is omitted?
Asked by sydney
(167 rep)
Aug 23, 2021, 01:54 PM
Last activity: Aug 25, 2021, 05:28 PM
Last activity: Aug 25, 2021, 05:28 PM