Sql-server – Database Backup to URL fails with Device I/O Error

azure-sql-databaseola-hallengrensql-server-2016

I'm using Ola's Maintenance Solution to backup my databases to a URL. Most of my databases backup just fine but I have one 2 TB database that continues to fail with a device I/O error. Has anyone seen this behavior before using his scripts? I don't have a clue what I should do with this error.

Best Answer

I have seen a similar issue previously with large backups to Azure Blob Storage and the issue was hitting block blob limits. You might be hitting size limitations for the block blob size in Azure (link) depending on your version.

Try the following:

  • Enable compression to ensure your backup is as small as possible.
  • Stripe your backup over multiple files. To try and stay under the 195GB per blob limitation, you will need to use 12-15 striped files.
  • Set the BLOCKSIZE and MAXTRANSFERSIZE parameters in your backup command to 64 K and 4 MB respectively. This will help ensure maximum density of your blob blocks.

Alternatively, since SQL Server 2016 uses the older API, you can try backing up to a local disk then using the latest version of AzCopy to move the backup file up to Azure Storage. This will take longer but may help alleviate the device I/O error you're seeing.