Restore SQL Backup from Azure Blob Storage

Question

I've been tasked with automating the restore of a database from a backup made overnight. The backups are saved to a Azure Blob storage container.

I'd like to perform the restore directly from Blob Storgae, has anyone else achieved this?

I've tried to create a powershell script to download the back up file on to the server where the SQL Server is installed and then restore form there but i'm having a lot of problems.

What kind of backup you try to restore? .bak or .bacpac? What kind of problems you have? — Martin Brandl
I cant get a powershell script to restore the back up, and i would like to know how to do it directly from the blob container — Derek
There a scripts available to achieve this on msdn: technet.microsoft.com/en-us/library/jj919148(v=sql.110).aspx — Martin Brandl

illandous illandous · Accepted Answer · 2018-04-10T20:31:18

Yes, this is possible. These instructions work on SQL Server 2012.

I was able to upload the backup using the /BlobType:page switch with the AZCopy command.

I was then able to restore directly from the location using stored credentials that I stored on the database.

Here is some sample code:

Copy the file -

Azcopy /Source:<locationOfYourBackup> /Dest:https://<yourContainerName>.blob.core.windows.net/sqlbackup/<yourBackupFile> /DestKey:<YourContainerKey> /BlobType:page

On the database server -

Create credential 'sqlbackup'
With identity ='<yourContainerName>', secret = '<YourContainerKey>'

RESTORE DATABASE [nameOfYourDatabase] FROM  URL = https://<yourContainerName>.blob.core.windows.net/sqlbackup/<yourBackupFile>  
WITH credential = 'sqlbackup' , FILE = 1, NOUNLOAD,  REPLACE,  BLOCKSIZE=4096

It's important to match the blocksize of your current DB so that the restore will complete successfully.

Restore SQL Backup from Azure Blob Storage

1 Answers