Databases, DevOps and Software development: Tools

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

-- To update the currently configured value for advanced options

RECONFIGURE

-- To enable the feature

EXEC sp_configure 'xp_cmdshell', 1

-- To update the currently configured value for this feature

RECONFIGURE

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

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

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!

PROBLEM:

You are using TOAD for Oracle / SQL Server and searching for SSMS (SQL Server Management Studio) extensions to format SQL, generate insert statements, etc. Like in Toad

SOLUTION:

SSMS Tools Pack is the ultimate add-in for SSMS with the following great functionality:

CRUD (Create, Read, Update, Delete) stored procedure generation