none
SQL 2014 backup to Azure StorageV2 account failing

    Întrebare

  • Hello, we are following this tutorial: https://msdn.microsoft.com/en-us/library/jj720557(v=sql.120).aspx

    We setup a Storage V2 account and when we run the backup, we receive the following error: 

    Msg 3271, Level 16, State 1, Line 1
    A nonrecoverable I/O error occurred on file "https://drstoragepremium.blob.core.windows.net/drcontainer/DRsqlDB.bak:" Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (400) Bad Request..
    Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally.

    The odd thing is if we try the same with a Storage V1 account, it works correctly.  Is there something additional that needs to be done with V2 accounts?

    Thank you!

    luni, 7 mai 2018 22:54

Răspunsuri

  • Try upgrade to SQL Server 2016 or 2017 then, maybe SQL 2014 have issues with Azure Storage(General v.2).

    I made my test in SQL Server 2017.

    Today, I follow your steps using SQL Server 2014 and it works. I think that the differences between your scenario and mine are the database (actually the size... ), network configuration or SSMS Version...

    SQL Server version : 12.0.2000.8

    SSMS version : 14.0.17254.0 (Latest v17.7)


    vineri, 18 mai 2018 07:28

Toate mesajele

  • Symptoms: You try to back up a database on your Azure-based SQL Server virtual machine (IaaS) by using the BACKUP DATABASE to URL command. However, the attempt fails
    Cause: This issue occurs if the storage account that you're trying to back up to was created with the Account Kind setting set to Blob. The Account Kind setting should be General Purpose.
    Resolution: To resolve this issue, create a new storage account, and specify General Purpose for the Account Kind setting. Also, designate a container in this storage account for backup to URL.

    ------------------------------------------------------------------------------------------
    If this answer was helpful, click “Mark as Answer” or “Up-Vote”. To provide additional feedback on your forum experience, click 
    here.

    marți, 8 mai 2018 03:26
    Moderator
  • Thank you however the storage account is setup as Storage V2 (general purpose), not a blob.
    marți, 8 mai 2018 15:52
  • If you are using premium storage you will not be able to backup as SQL backup in premium storage is not supported.

    refer to this link for more details

    miercuri, 9 mai 2018 11:27
    Moderator
  • SQL backup in premium storage works fine. I tested without any problem. 

    

    Tim Krupp please check if your SQL Server instance is fully updated and if not try to download and install, all the latest SP's and CU's.

    joi, 10 mai 2018 07:15
  • Thank you George.  We are currently running SQL 2014 SP2 but do not have any of the cumulative updates applied.  We will apply those this weekend and follow up if that resolves the issue.
    joi, 10 mai 2018 20:30
  • Checking in to see if the above answer helped solve your problem. Let me know if there are still any additional issues I can help with.

    miercuri, 16 mai 2018 10:04
    Moderator
  • Unfortunately upgrading to the latest (SQL 2014 SP2 CU11) did not correct the issue.  Even with creating a new storage account (General V2, Premium), we receive the same error:

    Msg 3271, Level 16, State 1, Line 7
    A nonrecoverable I/O error occurred on file "https://testv2plexis.blob.core.windows.net/drcontainer/DRsqlDB.bak:" Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (400) Bad Request..
    Msg 3013, Level 16, State 1, Line 7
    BACKUP DATABASE is terminating abnormally.

    Any other ideas?

    joi, 17 mai 2018 16:36
  • Can you send details from your backup  maintenance plan configuration?
    joi, 17 mai 2018 19:55
  • The details are pretty much the same as the tutorial (https://msdn.microsoft.com/library/jj720558%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396).

    1. Create the Azure Storage Account (General V2, Premium)

    2. Create the container in that storage account

    3. Create the credential with the storage account and access key

    4. Run this script to create the backup

    BACKUP DATABASE[TR_Medore_QC]  

    TO URL = 'https://drstoragepremium.blob.core.windows.net/drcontainer/DRsqlDB.bak'  

    /* URL includes the endpoint for the BLOB service, followed by the container name, and the name of the backup file*/  

    WITH CREDENTIAL = 'mycredential'; 

    /* name of the credential you created in the previous step */  

    GO 

    I am running the scripts in a query window instead of built into a maintenance plan.

    joi, 17 mai 2018 20:05
  • Can you try this one, there are no many differences, but this is the one that it works for me.

    BACKUP DATABASE[TR_Medore_QC]  TO URL = N'https://drstoragepremium.blob.core.windows.net/drcontainer/TR_Medore_QC.bak'  
    
    /* URL includes the endpoint for the BLOB service, followed by the container name, and the name of the backup file*/  
    
    WITH CREDENTIAL = N'mycredential',NOFORMAT, NOINIT,  NAME = N'TR_Medore_QC.bak', SKIP, REWIND, NOUNLOAD,STATS=10
    
    /* name of the credential you created in the previous step */  
    
    GO 

    joi, 17 mai 2018 21:00
  • Thank you George, unfortunately we still get the same result.
    joi, 17 mai 2018 21:11
  • Try upgrade to SQL Server 2016 or 2017 then, maybe SQL 2014 have issues with Azure Storage(General v.2).

    I made my test in SQL Server 2017.

    joi, 17 mai 2018 21:16
  • Try upgrade to SQL Server 2016 or 2017 then, maybe SQL 2014 have issues with Azure Storage(General v.2).

    I made my test in SQL Server 2017.

    Today, I follow your steps using SQL Server 2014 and it works. I think that the differences between your scenario and mine are the database (actually the size... ), network configuration or SSMS Version...

    SQL Server version : 12.0.2000.8

    SSMS version : 14.0.17254.0 (Latest v17.7)


    vineri, 18 mai 2018 07:28
  • We still are unable to make it work.  Seems like it must be something with the configuration.  Just going to use the other storage account for now and call it good.
    vineri, 18 mai 2018 20:06