locked
How can i take backup from SQL Azure to my local SQL Server 2008 R2? RRS feed

  • Question

  • Hi,

    I'm new in Azure but i wnt to take SQL Azure backup on my SQL Server 2008 R2 how can this posible?

    so plz help me...

    thnks

    Chetan

    Wednesday, March 14, 2012 5:19 AM

Answers

All replies

  • Hello Chetan,

    a "real" backup is not possible, but please have a look at Exporting Data from SQL Azure: Import/Export Wizard to export schema + data to your onpremise SQL Server.

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


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Wednesday, March 14, 2012 5:46 AM
  • Hi Olaf,

    I use this facility but this facility is not give full backup i need full backup of Azure SQL Database becauase my local Server HardDisk crass so plz any other utility to take full backup of Azure SQL Database so plz send me I'm online

    thnks

    Chetan

    Wednesday, March 14, 2012 6:16 AM
  • Take a look at SQL Azure Data Sync: http://msdn.microsoft.com/en-us/library/windowsazure/hh456371.aspx.

    Also there is a free tool from Red Gate called SQL Azure Backup: http://www.red-gate.com/products/dba/sql-azure-backup/

    Hope this helps.

    Thanks

    Gaurav Mantri

    Cerebrata Software

    http://www.cerebrata.com

    Wednesday, March 14, 2012 6:34 AM
  • Hello Chetan,

    There is no other way then exporting schema + data with the tools mentioned before.

    Where is the problem? This way will create fully a new database including data on you on-premise SQL Server.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Wednesday, March 14, 2012 6:36 AM
  • Hi  Chetan,

    Actaully... you can take backups using SQL Azure's Import / Export service. It has been in CTP for a while but a little more than a month ago it has hit production. Take  a look at the following blogpost (with videos): http://dacguy.wordpress.com/2012/01/24/sql-azure-importexport-service-has-hit-production/ 

    In short, you'll be able to take a backup to blob storage which allows you to download the backup: 

    Hope this  helps,

    Sandrino


    Sandrino Di Mattia | Twitter: http://twitter.com/sandrinodm | Azure Blog: http://fabriccontroller.net/blog | Blog: http://sandrinodimattia.net/blog

    Wednesday, March 14, 2012 8:46 AM
  • Hi Sandrino, Olaf and Gaurav,

    I need database with data,SP,Function,Trigger,PK,FK and indexs that means every thing from azure.

    I use impot/export functionlty as well as use bcp.exe also and use red-gate software also but that all utility is not work on whole database backup that give me only Record (rows) so plz tell me full DB backup system utility

    thanks,

    chetan

    Wednesday, March 14, 2012 12:44 PM
  • Hello Chetan,

    You don't have file access to or from your SQL Azure instance, so even if you could create a "real" backup, you couldn't transfer it.

    You can SSMS script wizard to script out all objects like tables, view, stored procedures and so on, it can also create INSERT statements for the data. Then you can run this script on you local SQL Server to recreate the database (SSMS 2008R2 required).

    See http://msdn.microsoft.com/en-us/library/ms178078.aspx + http://msdn.microsoft.com/en-us/library/ms181421.aspx


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Wednesday, March 14, 2012 12:49 PM
  • you may want to take a look at Enzo Backup for SQL Azure as well. as far as i know, it does schema+data.

    see: http://www.bluesyntax.net/backup.aspx

    Wednesday, March 14, 2012 12:58 PM
    Answerer
  • Hi Chetan,

    The bacpac file also contains the database schema (quote from the wiki):

    What is in a “.bacpac” export file?

    The export file contains all supported database schema objects and table data in a single package. Schema objects include logins, users, schemas, tables, columns, constraints, indexes, views, stored procedures, functions, triggers, and other objects relevant to the definition of a database. All SQL Azure data types except SQL variant are supported in this CTP.

    Sandrino


    Sandrino Di Mattia | Twitter: http://twitter.com/sandrinodm | Azure Blog: http://fabriccontroller.net/blog | Blog: http://sandrinodimattia.net/blog

    Wednesday, March 14, 2012 2:37 PM
  • Hi Chetan,

    I'll just weigh in here as I wrote SQL Azure Backup from Red Gate :). This should create a full copy of the database on a local SQL Server including all the schema and all the data. Otherwise you could use the .bacpac format to backup and then attempt to restore this on the local SQL Server.

    If you want a transactionally consistent backup you're going to need to create a copy of the database in SQL Azure first though.

    Hope this helps in some way.


    Richard Mitchell Project Manager Red Gate Software Ltd

    Thursday, March 15, 2012 7:24 AM
  • We can also use export / import utility and enter Azure SQL creditianls, this is how i took bakup few months ago.

    contact azure support for your specific cause

    Thursday, March 15, 2012 9:53 AM