Azure Database Backup

PROBLEM:

You want to backup your Azure databases locally and also in Azure

SOLUTION:

The following steps are needed to copy Azure database to your local SQL Server 2008R2. Then you can backup it locally using a database job or manually.

First you need to allow your SQL Server to use and run xp_cmdshell

-- To allow advanced options to be changed

EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options

RECONFIGURE

GO

-- To enable the feature

EXEC sp_configure 'xp_cmdshell', 1

GO

-- To update the currently configured value for this feature

RECONFIGURE

GO

Download and install SQL Azure Backup from here: http://www.red-gate.com/products/dba/sql-azure-backup

You are going to use this tool command line capabilities to automate and schedule your Azure backups with a simple stand-alone .exe

After installing Red Gate tool, use SSMS query window to run the following command. This will check if you configure xp_cmdshell and install Red Gate tool correctly

EXEC sys.xp_cmdshell 'C:\SQLAzureBackup\redgate.sqlazurebackupcommandline.exe'

This will bring the following

SQL Azure Backup v2.6.0.86

===============================================================================

Copyright (c) Red Gate Software Ltd 2011

Error: No argument for AzureServer and no default value

Back up a database from SQL Azure to SQL Server

Show Usage:

                  RedGate.SQLAzureBackupCommandLine.exe [/Help]

Copy to local sql server:

                  RedGate.SQLAzureBackupCommandLine.exe

                           /AzureServer:server /AzureDatabase:database

                           /AzureUserName:user /AzurePassword:password

                           [/CreateCopy] [/Skip] /LocalServer:server

                           /LocalDatabase:database [/LocalUserName:user]

                           [/LocalPassword:password] [/DropLocal] [/Verbose]

                           [/Help] [/Backup]

Create BACPAC file with Import/Export service CTP (Access Key):

                  RedGate.SQLAzureBackupCommandLine.exe

                           /AzureServer:server /AzureDatabase:database

                           /AzureUserName:user /AzurePassword:password

                           [/CreateCopy] /StorageAccount:name /AccessKey:key

                           /Container:container [/Filename:filename] [/Backup]

Create BACPAC file with Import/Export service CTP (Shared Access Signature):

                  RedGate.SQLAzureBackupCommandLine.exe

                           /AzureServer:server /AzureDatabase:database

                           /AzureUserName:user /AzurePassword:password

                           [/CreateCopy] /SignatureUrl:url [/Filename:filename]

                           [/Backup]

Copy to SQL Azure:

                  RedGate.SQLAzureBackupCommandLine.exe

                           /AzureServer:server /AzureDatabase:database

                           /AzureUserName:user /AzurePassword:password

                           [/CreateCopy] /LocalServer:server

                           /LocalDatabase:database [/LocalUserName:user]

                           [/LocalPassword:password] [/DropAzure] [/Restore]

  /sk:key,       /AccessKey:key          Primary or secondary access key

  /ad:database,  /AzureDatabase:database Database name to backup

  /ap:password,  /AzurePassword:password Azure password

  /as:server,    /AzureServer:server     Azure server

  /au:user,      /AzureUserName:user     Azure username

  /ba,           /Backup                 Do a backup

  /sc:container, /Container:container    Container where file will be placed

  /cc,           /CreateCopy             Ensure db transactional consistentency

  /da,           /DropAzure              Drop SQL Azure database if it exists

  /dl,           /DropLocal              Drop local database if it exists

  /sf:filename,  /Filename:filename      Filename to be created

  /?,            /Help                   Show usage

  /ld:database,  /LocalDatabase:database Database to be created

  /lp:password,  /LocalPassword:password Local password

  /ls:server,    /LocalServer:server     Local server

  /lu:user,      /LocalUserName:user     Local username for sql authentication

  /re,           /Restore                Do a restore

  /su:url,       /SignatureUrl:url       Shared access signature url

  /s,            /Skip                   Skip objects that fail to be copied

  /sa:name,      /StorageAccount:name    Backup to azure blob storage account

  /v,            /Verbose                Show verbose output

In SSMS query window or inside database backup job, use the following command to make local copy of SQL Azure database

EXEC sys.xp_cmdshell 'C:\SQLAzureBackup\redgate.sqlazurebackupcommandline.exe /AzureServer:MyAzureServerName.database.windows.net /AzureDatabase:MyDatabaseName /AzureUserName:MyUser /AzurePassword:MyPass /LocalServer:MyLocalServerName /LocalDatabase:MyDatabaseName /DropLocal'

Use the following command to make database backup in Azure (BACPAC file)

EXEC sys.xp_cmdshell 'C:\SQLAzureBackup\redgate.sqlazurebackupcommandline.exe /AzureServer:MyAzureServerName.database.windows.net /AzureDatabase:MyDatabaseName /AzureUserName:MyUser /AzurePassword:MyPass /Container:db-backup /Filename:MyDatabaseName /Backup /StorageAccount:MyStorageAccount /AccessKey:MyAccessKey'

To see you Azure database backups, download and install Azure Storage Explorer from here http://azurestorageexplorer.codeplex.com


If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!