locked
I need to automate the data backup for SQL Azure database. How can i do so? RRS feed

  • Question

  • I have a SQL Azure Database I need to automate only data backup from this database to my local system. Can anyone suggest me that how to do so using any freeware or some build in feature of SQL Azure?
    Tuesday, January 3, 2012 3:51 AM

All replies

  • Balraj,

    You have a couple of options.

    SSIS
    You can use SQL Server Integration Services on a local SQL Server to import data from SQL Azure. In the Windows Azure Training Kit, there's a demo that walks through moving data with SSIS.

    SQL Azure Data Sync
    You could also sync to a local SQL Server using SQL Azure Data Sync. In the Windows Azure Training Kit, there's a hands-on-lab for that too.

    Third-Party tools
    Red Gate has a SQL Azure backup and restore tool that will backup to local SQL Server, SQL Azure and blob storage. http://www.red-gate.com/products/dba/sql-azure-backup/

     


    Eric D. Boyd - Director, Chicago + Cloud at Centare 
    Blog | twitter
    • Proposed as answer by Paras Doshi Tuesday, January 3, 2012 5:25 AM
    Tuesday, January 3, 2012 4:13 AM
  • Thanks for the reply but how can i automate this. I mean that data backup should happen automatically every morning at 08:00 AM. How to do that?
    Tuesday, January 3, 2012 4:48 AM
  • You can schedule SSIS using SQL Agent.

    SQL Azure Data Sync will run every X minutes and will stay in sync on that schedule.

    The Red Gate tool also advertises a command line utility for scheduling automated backups.


    Eric D. Boyd - Director, Chicago + Cloud at Centare 
    Blog | twitter
    Tuesday, January 3, 2012 5:18 AM
  • SSIS packages can be scheduled in couple of ways. one of the way is to schedule it using SQL server agent (running locally)

    And other third party tool (not a freeware) that you can try is Enzo backup: http://www.bluesyntax.net/backup.aspx - it'll let you schedule backups.


    Paras Doshi (Blog: ParasDoshi.com | Twitter: @Paras_Doshi )

    • Edited by Paras Doshi Tuesday, January 3, 2012 5:32 AM
    Tuesday, January 3, 2012 5:19 AM
  • I am trying to use Red Gate tool to schedule a backup through command prompt but unable to do it. Do you have any documentation on how to use it or any link to create a schedule backup.
    Tuesday, January 3, 2012 6:05 AM
  • I don't have any specific documentation or advice on Red Gate's tool. That would be better answered by Red Gate. They have a Forum and a contact person listed on their support site.  http://www.red-gate.com/products/dba/sql-azure-backup/support
    Eric D. Boyd - Director, Chicago + Cloud at Centare 
    Blog | twitter
    Tuesday, January 3, 2012 6:45 AM
  • The way most people seem to use the tool to backup on a schedule is to use a local windows server and simply setup a local "scheduled task" or even use a SQL Agent on a local sql server to run the command line. There are a couple of links from Grant's blog post you can find here ...  http://www.simple-talk.com/community/blogs/scary/archive/2011/09/26/103434.aspx

    I'm looking at making a much better solution to this which I hope will go out as an open alpha very early this year.


    Richard Mitchell Project Manager Red Gate Software Ltd
    Tuesday, January 3, 2012 7:12 AM
  • Hi,

    You can use a Azure Worker Role / Windows Scheduler / Windows Service to take backup files on a scheduled period and store it in a local storage or Blob storage. Verify the below url.

    http://azureautobackup.codeplex.com/

    Thursday, January 5, 2012 6:02 AM
  • Hi,

     

    Kindly check if you can use StoreGrid's professional edition for backing up SQL Server databases in a periodically scheduled manner.

    http://storegrid.vembu.com/online-backup/network-backup.php

     

    You can choose to do remote server backups or free local backups.

     

    Regards.


    • Edited by VembuTech1 Friday, January 6, 2012 10:50 AM Link added
    Friday, January 6, 2012 10:49 AM
  • Maybe you can use the Data-tier Application, which is called Import/Export now, to export your data in DACPAC file and save into BLOB. You can do it in your own .NET code which means you can schedule it by using a thread, a worker role.

    You can check my blog post http://blogs.shaunxu.me/archive/2011/06/07/backup-your-sql-azure-by-data-tier-application-ctp2.aspx

     

    Saturday, January 7, 2012 6:59 AM
  • Create a Worker Role
    Log in to the Worker Role (RDP)
    Create folder in C: drive (C:\DBBackupPrograms)
    Copy DacIESvcCli.exe & DacIESvcCli.exe.config
    Created a batch file ((C:\DBBackupPrograms\DacIESvcCli.exe -s <Azure Server Name> -d <Database Name> -u <User Name> -p <Password>  -x -BLOBACCESSKEY <BLOBKEY> -BLOBURL <URL Path>/GOLD_%date:~-4,4%%date:~-7,2%%date:~-10,2%_%time:~0,2%%time:~3,2%.bacpac -accesskeytype "STORAGE"))
    Enable Schedule Task services
    Create a Scheduled Task  and attach batch file

    The above approach has one issue, if the instance is moved to a different node , all files will be wiped out. So you have to manually do all the below activities everyone when you figure out instance has changed a different node.
    Its better to put these steps in an application and deploy it in azure -- http://azureautobackup.codeplex.com/

    Regards,Ranjith

    Thursday, February 16, 2012 9:23 AM
  • Microsoft has launched export import as a hosted service so it doesn't need any installation to export the database and take backup, its a simple post call to the service.

    You might want to schedule that service call from onpremise or any other scheduling service. Please find details here http://sqldacexamples.codeplex.com/wikipage?title=Import%20Export%20Service%20Client&referringTitle=Documentation

    Sunday, February 19, 2012 3:43 AM