locked
Sql files Restore error while accessing backup files from azure blob to sql server vm 2016 RRS feed

  • Question

  • Hi all,

    I was facing the below error while restoring the backup files from azure blob storage .

    Msg 3271, Level 16, State 1, Line 23
    A nonrecoverable I/O error occurred on file "https://containername.blob.core.windows.net/test/sample.BAK:" Backup to URL received an exception from the remote endpoint. Exception Message: Error occurred while referencing Blob in remote storage.
    Msg 3013, Level 16, State 1, Line 23
    RESTORE DATABASE is terminating abnormally.

    Followed below steps:

    Step-1  Create credential's using Tsql in sqlserver to access my storage account 

    CREATE CREDENTIAL [Credential_BLOB9]
    WITH IDENTITY= 'https://containername.blob.core.windows.net/test',
    SECRET = 'ds9AKSdl90Yoc0qK9lRI4gkjFXl7lJk6PWcRI31e9S79TnzCimEmwfxwOzptb9IwUw==';

    step-2  

      Create shared access credential's using Tsql in sqlserver to access

    CREATE CREDENTIAL [Credential_Name_SAS3]
    WITH IDENTITY = 'https://containername.blob.core.windows.net/test/sampledb.BAK',
    SECRET = 'sp=racwdl&st=2019-10-17T12:32:17Z&se=2020-01-01T12:32:00Z&sv=2019-02-02&sr=b&sig=OJgmHzSqKPizbPMuiKsaSY%3D';

    step -3

    Run restore script to restore database to azure sqlvm 2016

    RESTORE DATABASE Database_Name FROM URL = 'https://containername.blob.core.windows.net/test/sampledb.BAK'

    WITH CREDENTIAL = 'Credential_BLOB9',
    MOVE 'sampledb1' to 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\sampledb1.mdf',
    MOVE 'sampledb_log1' to 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\sampledb_log1.ldf',
    BLOCKSIZE = 512;

    Please let us know if any solution is available to fix .Thanks in  advance .

    Thursday, October 17, 2019 1:18 PM

All replies

  • If am correct you are using SQL Server 2016, backup to URL with SaSKey will backup to block blob. It works well with both Blob Storage Account (either hot or cold) and General Purpose Storage Account.

    • Backup to premium storage is not supported.
    Tuesday, October 22, 2019 5:34 AM
  • Hi Reddy,

    Wanted to substantiate Sumanth's response with additional information although the error seems to be a networking related and not a specific issue with your configuration. Please see the following:

    SQL Server Backup and Restore with Microsoft Azure Blob Storage Service

    SQL Server Backup to URL Best Practices and Troubleshooting

    In the troubleshooting document, you will see the following with regard to Proxy Errors:

    If you are using Proxy Servers to access the internet, you may see the following issues:

    Connection throttling by Proxy Servers:

    Proxy Servers can have settings that limit the number of connections per minute. The Backup to URL process is a multi-threaded process and hence can go over this limit. If this happens, the proxy server kills the connection. To resolve this issue, change the proxy settings so SQL Server is not using the proxy. Following are some examples of the types or error messages you may see in the error log:

    A nonrecoverable I/O error occurred on file "https://storageaccount.blob.core.windows.net/container/BackupAzurefile.bak:" Error could not be gathered from Remote Endpoint. 
     
    Msg 3013, Level 16, State 1, Line 2 
     
    BACKUP DATABASE is terminating abnormally.

    There are additional steps you can take to use specific proxy settings to get around this error.

    Please let us know if you have any additional questions.

    Regards,

    Mike

    Wednesday, October 23, 2019 10:00 PM