Sql-server – SQL backup to azure storage failed

azure-sql-databasesql server

I have a SQL Server 2014 up and running and I am trying to backup a specific database to Azure storage. I created a credential using the storage account name as identity and the primary key as password. Then I tried to backup the database to URL but I get the following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Backup failed for Server '*****'.  (Microsoft.SqlServer.SmoExtended)

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: A nonrecoverable I/O error occurred on file "https://******.blob.core.windows.net/backupcontainer/demo_backup_2016_06_22_110957.bak:" Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (400) Bad Request.. (Microsoft.SqlServer.Smo)

Best Answer

I figured out what the problem is. When I created the storage account I choose the kind to be "Blob Storage". It must be "General Purpose" to work. The backup steps was correct, the problem was with the kind of storage account.

Related Solutions

Sql-server – Restore failed for server (Microsoft.SqlServer.SmoExtended)

Try running RESTORE VERIFYONLY and see if you get more information about the failure.

You could also try running RESTORE with CONTINUE_AFTER_ERROR and then run DBCC CHECKDB:

RESTORE DATABASE database_name 
FROM backup_device WITH CONTINUE_AFTER_ERROR

(i.e. running as TSQL rather than through SMO)

DBCC CHECKDB(N'databasename') WITH EXTENDED_LOGICAL_CHECKS;

Specifying WITH CONTINUE_AFTER_ERROR in a RESTORE statement attempts to restore the database. However, there are many kinds of corruption that prevent recovering a database. We strongly recommend that you reserve using the CONTINUE_AFTER_ERROR option until you have exhausted all alternatives.

All backup strategies should include regular DBCC CHECKDB runs.

Also NOTE: You do not have a backup unless you periodically successfully test restores.

Responding to SQL Server Restore Errors Caused by Damaged Backups

Sql-server – On Prem SQL Server: Log shipping via Azure Blob Storage: Possible

Yes, but only manually - Azure Blob Storage isn't integrated into the log shipping wizards. You would need to do your own backups to Azure Blob Storage, and then have the secondary server(s) restore from ABS using the same certificates.

When other shops have done similar projects with AWS's S3 storage (writing their SQL Server backups there using 3rd party tools), I've seen them either log backups to a text file that the other SQL Servers check for backup files, or log entries into a centrally accessible table (like SQL Azure or Amazon RDS) and fetch the backup file names from there.

Best Answer

Related Solutions

Sql-server – Restore failed for server (Microsoft.SqlServer.SmoExtended)

Sql-server – On Prem SQL Server: Log shipping via Azure Blob Storage: Possible

Related Question