I need to automate SQL Azure database backup in SQL Script files. How can i do so?

Question

Presently i am generating SQL scripts everyday to take the backup of SQL Azure database. I need to automate this process using some freeware. How can i achieve this?

Note:- I need to take the backup in form of SQL Script to some local machine.

Answer 1

Option 1: Run some code/script to backup the database

You could run the SQL using the SQLCMD utility.

http://msdn.microsoft.com/en-us/library/ms162773.aspx

To schedule this, just use the Windows Task Scheduler.

http://msdn.microsoft.com/en-us/library/bb756979.aspx

Option 2: Use Azure Data Sync

Keep a local database in sync with your SQL Azure database, like a constant backup.

http://msdn.microsoft.com/en-us/library/windowsazure/hh456371.aspx

Option 3: Use BacPac

There is an API available in Azure to backup your database to a BACPAC format.

http://msdn.microsoft.com/en-us/library/windowsazure/hh335292.aspx

A RedGate tool then allows you to restore to a local SQL Server.

http://www.red-gate.com/products/dba/sql-azure-backup/

Edit

I have since created a command line tool which will create a copy of your database, and back it up to BacPac using the Azure Management API:

https://github.com/richorama/SQLDatabaseBackup