Automating SQL Azure backup to On premise Disks using a SQL Azure management cmdlet

Standard

[Update: When i wrote this post, the SQL Azure import and Export CTP was not available. But now it is – and it would let you export a SQL Azure database on local disks too. And since it is command driven, you’ll be able to automate the process on a local server]

In this article, I am going to discuss automating SQL Azure backup to an on premise location using a SQL Azure management cmdlet. Alternatively, you can also schedule a backup that backs up your SQL Azure data to Azure storage. But before we discuss how to go about it – couple of disclosures 1. Powershell cmdlet is to be executed from on premise machine as SQL Azure does not natively support Powershell but you can always choose SQL Azure as your target for your Cmdlet running locally 2. I am going to use a third party Azure management cmdlet from Cerebrata. You can download it from https://www.cerebrata.com/Products/AzureManagementCmdlets/Download.aspx

1

A complete setup guide is available at https://www.cerebrata.com/Docs/AzureManagementCmdlets/readme.pdf

Now, once you are done with installation and post installation steps – you will be able to see the list of cmdlet’s by running

Get-Command –PSSnapin AzureManagementCmdletsSnapIn

4

For automating the SQL Azure backup we are interested in the cmdlet named “Backup-Database” that let’s you back up SQL Azure to a local disk using bcp.exe utility. It also allows you to back up the database to blob storage. But remember the SQL Azure data transfer rates apply

Now, to backup a SQL Azure database to local disks run:

Backup-Database -Name “database1” -DownloadLocation “D:temp” -Server “mydatabaseserver” -UserName “username” -Password “password” –Verbose

here,

Backup-Database s the name of the cmdlet

-Name is the name of the database to be backed up

-DownloadLocation is the local location where the data will be backed up

-Server is the name of the server in which the database to be backed up is located

-Username is the username to login to the specified SQL Azure server

-Password is the password corresponding to the specified username used to login to the specified SQL Azure server

-Verbose is the parameter that will display detailed information about the operation performed by the cmdlet

After the back up is done, you will receive following message:

5

Now, if you wish to backup only specific table that you could do that by run the command:

Backup-Database -Name “database1” -DownloadLocation “D:temp” -Server “mydatabaseserver” -UserName “username” -Password “password”TablesToInclude “<name1>,<name..n>” –Verbose

Now, one can schedule a PowerShell command – do that to automate your back up process!

If you wish to backup the database to blobs instead of local files. please read: http://parasdoshi.com/2011/07/03/sql-azure-backup-to-windows-azure-blobs-using-azure-management-cmdlet-powershell/

Just note that:

For now, the restore database through the Management CmdLet is not available – But basically the bigger picture of this post is to point out that – using Powershell CmdLet’s we will be able to schedule SQL Azure tasks! and that is definitely a good news for an IT pro.

 

Related Posts:

programmatically verifying that the underlying database is SQL Azure

Standard

There are two ways to programmatically verify that the underlying database is SQL Azure

1. By running the TSQL command: select @@version
if the underlying database is SQL Azure, the query will return something like:

“Microsoft SQL Azure (RTM) – 10.25.9640.0 Apr 2 2011 18:10:33 Copyright (c) Microsoft Corporation”

The returned value clearly states that the database is SQL Azure

2. By running the TSQL command: SELECT SERVERPROPERTY('EngineEdition')
if the underlying database is SQL Azure, the query will return value 5

Output of SELECT SERVERPROPERTY('EngineEdition') as value

To know more about serverproperty. go here

Extending SQL Azure with Azure worker roles << Guest Post on Pinal sir’s blog

Standard

My guest post on “Extending SQL Azure with Azure worker role” got published on Pinal sir’s blog. In the article, I discuss three lightweight solutions that augment the contemporary capability of SQL Azure. They are:

1. Automating SQL Azure database backup process

2. Lightweight SQL server agent for SQL Azure

3. Synchronization of databases using SYNC Framework.

To read the article, please visit: http://blog.sqlauthority.com/2011/06/13/sql-server-extending-sql-azure-with-azure-worker-role-guest-post-by-paras-doshi/

Thank you Pinal sir for this wonderful opportunity.

sys.dm_db_partition_stats : A SQL Azure Dynamic management view (DMV) to calculate database size

Standard

I have written a blog post on beyondrelational site about a SQL Azure DMV – sys.dm_db_partition_stats that can be used to extract information about database size and size of each individual database object. To read the article please go to: http://beyondrelational.com/blogs/parasdoshi/archive/2011/05/30/sys-dm-db-partition-stats-a-sql-azure-dynamic-management-view-dmv-to-calculate-database-size.aspx

TSQL code to know SQL Azure DB size

Explore the features being added with each release of SQL Azure on “What’s New in” Site for SQL Azure

Standard

Go to http://beyondrelational.com/whatisnew/sqlserver/azure/default.aspx to explore the features being added with each release of SQL Azure. You will be interested to know that unlike other products, the release cycles of SQL Azure is rapid and in a year you can expect 4-6 service updates (releases). On  http://beyondrelational.com/whatisnew/sqlserver/azure/default.aspx , you will find the information about past releases organized in a list format and also information about latest releases of SQL Azure.

I would like to thank Jacob sir for his encouragement and support! Thanks sir!

A step by step guide to Install Adventure works LT ( sample database ) on SQL Azure

Standard

I have written a step by step guide on how to install Adventure works LT DB, a sample database available on codeplex, at my beyondrelational site. here is the link to the article: http://beyondrelational.com/blogs/parasdoshi/archive/2011/05/27/let-s-install-an-adventure-works-lt-database-on-sql-azure.aspx

How to configure SQL Azure firewall from Azure management portal

Standard

I have written a blogpsot on my beyondrelational site about how to configure SQL Azure firewall from Azure management portal. Have a look at the article – click here