通过

Copying SQL Server Backups to Windows Azure Storage using AzCopy

  • 项目

There are at least three easy ways to get your SQL Server backups to Azure storage, which, being offsite, bottomless, and reasonably-priced is a good thing.

First you can use SQL Server 2012's native backup to URL function.

Also you can use the Microsoft® SQL Server® Backup to Microsoft Windows® Azure®Tool, available here: https://www.microsoft.com/en-us/download/details.aspx?id=40740

But both those methods put your backups directly in Azure, which means that if you need your backups locally (and you do), you have to download them.  Another downside of this approach is that your backup time depends on your network bandwidth to Azure, so it's not really viable for backing up really large or a really large number of databases.

A better approach is to take your backups to some local disk and copy them to azure. 

A StorSimple https://www.storsimple.com/ is a really good option, as it dedupes and encrypts the data before backing it up to Azure, and handles the full lifecycle of the data.  Of course with StorSimple you need to install small storage appliance in your datacenter.

If you just want to copy from a local folder to Azure, that's really easy too using AzCopy, available here https://aka.ms/WaCopy .

So use a MaintenancePlan or scheduled job to backup your databases to a local disk or network file share, and then use AzCopy to copy them to Azure.  Your backups will be available locally until your Maintence Plan deletes them, and will be available in Azure forever.  Plus your backup time will not increase, even if it takes 18 hours to get all your nightly backups sent up to Azure.  Just run azcopy after your backups are done.  Like this

azcopy.exe backupfolder azure_container_url /destkey:yourkey /S /XO

so, something like

"c:\Program Files (x86)\Microsoft SDKs\Windows Azure\AzCopy\azcopy.exe" "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup" "https://yourstorageaccount.blob.core.windows.net/yourcontainer" /destkey:AKSxxxxxxxxxxxxxxxxxxxxxxxxxxx /S /XO

You can also create a SQL Agent job to run this command.  If you do, remember to grant the SQL Agent Service rights to the backup folder.

You will also want to delete some of these backups from Azure at some point according to a long term retention plan.  For instance retain daily backups for a week, weekly backups for a month, monthly backups for a year, annual backups for 5 years, where the first backup of the week is the "weeky" backup, the first backup of the month is the "monthly" backtup etc.  But that's a subject for another day. . .

David

Comments

  • Anonymous
    October 28, 2014
    Great article, now you can find everything about azcopy at http://aka.ms/azcopy