Back up SQL Server database to Azure BLOB Storage

To back up your database to Azure BLOB storage, first you need to set up the BLOB storage in your Azure account. You will also need to create a container first.
azure blob storage and container
You can back up the database to Azure by using either PowerShell or T-SQL.

Using PowerShell

Here is an example PowerShell script that does the backup:

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#you will need to fill this in with your own account settings
$instanceName = "InstanceName"
$databaseName = "DatabaseName"
$AzureStorageAccount = "sqlbelle"
$blobContainer = "BLOBContainer"
$backupUrlContainer = "https://$AzureStorageAccount.blob.core.windows.net/$blobContainer/"
$primaryAccessKey = "replaceWithYourStorageKey"

$storageAccount = "sqlbelle"
$secureString = ConvertTo-SecureString $primaryAccessKey  -AsPlainText -Force
$credentialName = "AzureCredential"

Backup-SqlDatabase -ServerInstance $instanceName -Database $databaseName -BackupContainer $backupUrlContainer -SqlCredential $credentialName -Compression On

If you want to get just the T-SQL script that will do the backup, you can add the -Script switch at the end of the Backup-SqlDatabase command:

Backup-SqlDatabase -ServerInstance $instanceName -Database $databaseName -BackupContainer $backupUrlContainer -SqlCredential $credentialName -Compression On -Script

Using T-SQL

Here is an example of T-SQL:

BACKUP DATABASE [SampleDB] 
TO  URL = N'https://azureblobstorageURL/container/automaticbackupfilename.bak' 
WITH  CREDENTIAL = N'AzureBackup' , 
NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10

Here’s the MSDN page that walks you through the same task:
https://msdn.microsoft.com/en-us/library/dn223322.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s