Sql backup into Azure Blob fails: 1117(The request could not be performed because of an I/0 device error.)

Question

I am trying to run a backup from our sql server to a blob azure container. I went ahead and did the setup exactly as suggested by microsoft: https://learn.microsoft.com/de-de/sql/relational-databases/tutorial-sql-server-backup-and-restore-to-azure-blob-storage-service?view=sql-server-ver16&tabs=SSMS But at 90-100% I encounter an error:
Microsoft.Data.SalClient.SqlError: Write on https:/xxx.blob.core. windows.net/dbbackup/backup_2024_02_18_200221.bak" failed: 1117(The request could not be performed because of an I/0 device error.) (Microsoft.SqlServer.Smo)

The database file is about 50 GB big.

Any idea why this might happen?

Answer 1

Hi @Andreas Niedermann

The error message reported an I/O device error but not that the Azure Storage Account space is full.

Please use stripped backup by providing several URL locations where parts of the backup will be placed:

BACKUP DATABASE tpcc2501
 TO URL = 'https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-1.bak',
 URL = 'https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-2.bak',
 URL = 'https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-3.bak',
 URL = 'https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-4.bak'
 WITH COPY_ONLY

Refer to this article: Lesson Learned #311:Error-1117 the request could not be performed because of an I/O device error.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Andreas Niedermann , You are getting I/O device error. You may reach this error depending on the size of the blob storage, in this situation, please, run the backup in stripping backup format "Azure Blob Storage block blobs have 200GB size limitation.

https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-ver16#limitations Thank you!

Answer 3

Hi Andreas Niedermann

You should split backup to multiple files so this way the files will be smaller

BACKUP DATABASE [mydatabase] TO URL='.............../mydatabase_01.BAK',
URL='.............../mydatabase_02.BAK',
URL='.............../mydatabase_03.BAK',
URL='.............../mydatabase_04.BAK',

You can try with the number of files you want Regards Javier

If the Answer is helpful, please click "Accept Answer" and upvote it. Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Not sure if your query has been answered, but yea, in your case, the 50,000 block limit is reached when 90-100% of your backup is completed. It is recommended to increase the MAXTRANSFERSIZE to 4MB (4194304) to allow a maximum backup file size of 195GB. (default MAXTRANSFERSIZE size is 1MB)