none
How to import/export sql azure database from one dabase server to another programmetically?

    Question

  • Hi,

    I am trying to export and import sql azure database. First export it to a temporary blob storage and from there I will import it into another database server. There is a tool in azure portal(import/erport) through which I can do that. But I want to do it programmetically(c#) and trying to create a worker role so that it will execute with a particular schedule. I tried to get some help from different website, but in most of the pages they did not mentioned a particular step by step process. They are just giving some portion of total code. But for executing this what are the additional setup are not mentioned there. most of them are saying to use DAC. But I don't have any idea how to use it. Can anynoe please help me with proper c# code.

    Thanks,

    Saikat

    Friday, October 12, 2012 11:57 AM

Answers

  • Saikat,

    Here are some of the options available to programmatically import & export.  We have several customers who use these methods currently including option 3 below.

    1) Use the Data-Tier Application Framework (DACFx) client tool - sqlpackage.exe

    Example for import
    "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /a:Import /tdn:"your database to create" /tp:"your password here" /tsn:"yourserver.database.windows.net" /tu:"yourdbuser" /sf:"bacpac on local disk"

    Example for export
    "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /a:Export /ssn:yourserver.database.windows.net /sdn:"your database to export" /su:"yourdbuser" /sp:"your password" /tf:"bacpac file to create on local disk"

    You can download the latest version of the Data-Tier Application Framework here and find sqlpackage documentation here.

    2) Use the Data-Tier Application Framework (DACFx) public API

    Add a reference to Microsoft.SqlServer.Dac

    Create a DacServices instance, call appropriate method to import (ImportBacpac) or export (ExportBacpac).

    You can download the latest version of the Data-Tier Application Framework here and find the DACFx public API documentation here.

    3) Use the Import/Export REST Endpoints

    Create a service reference to one of the following service endpoints (depending on your datacenter).

    Use the appropriate methods to import, export, selective export, and check status.

    DataCenter

    Endpoint

    North   Central US

    https://ch1prod-dacsvc.azure.com/DACWebService.svc/

    South   Central US

    https://sn1prod-dacsvc.azure.com/DACWebService.svc/

    North   Europe

    https://db3prod-dacsvc.azure.com/DACWebService.svc/

    West   Europe

    https://am1prod-dacsvc.azure.com/DACWebService.svc/

    East   Asia

    https://hkgprod-dacsvc.azure.com/DACWebService.svc/

    Southeast   Asia

    https://sg1prod-dacsvc.azure.com/DACWebService.svc/

    East   US

    https://bl2prod-dacsvc.azure.com/DACWebService.svc/

    West   US

    https://by1prod-dacsvc.azure.com/DACWebService.svc/

    -Shamitha Reddy

    Program Manager, Windows Azure SQL Database Import/Export Service


    Shamitha Reddy (Microsoft)



    Tuesday, October 16, 2012 6:31 PM

All replies

  • Hi Saikat,

    Thank you for your question.  

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.  

    Thank you for your understanding and support


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, October 15, 2012 8:36 AM
    Moderator
  • Hi Saikat

    I guess you are looking for something like this.. http://azureautobackup.codeplex.com/

    Regards

    Ashish


    I hope it helps!! If you found this post useful, Please "Mark as answer" or "Vote as Helpful". Thanks much!

    • Proposed as answer by Ashish.Agrawal Monday, October 15, 2012 9:19 AM
    Monday, October 15, 2012 9:19 AM
  • You could use SQL Server 2012 Data Tier Application Framework API to do that. You could download it from http://www.microsoft.com/en-us/download/details.aspx?id=34964. In that framework, you could use DacServices::ExportBacPac to export SQL Azure database and import using DacServices::ImportBacPac. Besides, there is no API which could export the SQL Azure database directly to Azure BLOB storage, there need to be a local temporary output. You could refer to the following sample,

    DacServices dacService = new DacServices("server=<username>;uid=<user name>;password=<password>");
                dacService.ExportBacpac(<file path>, <database name>);

                BacPackage bacpacFile = BacPackage.Load(<file path>, DacSchemaModelStorageType.File);
                dacService.ImportBacpac(bacpacFile, <database name>);

    Tuesday, October 16, 2012 9:28 AM
  • Saikat,

    Here are some of the options available to programmatically import & export.  We have several customers who use these methods currently including option 3 below.

    1) Use the Data-Tier Application Framework (DACFx) client tool - sqlpackage.exe

    Example for import
    "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /a:Import /tdn:"your database to create" /tp:"your password here" /tsn:"yourserver.database.windows.net" /tu:"yourdbuser" /sf:"bacpac on local disk"

    Example for export
    "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /a:Export /ssn:yourserver.database.windows.net /sdn:"your database to export" /su:"yourdbuser" /sp:"your password" /tf:"bacpac file to create on local disk"

    You can download the latest version of the Data-Tier Application Framework here and find sqlpackage documentation here.

    2) Use the Data-Tier Application Framework (DACFx) public API

    Add a reference to Microsoft.SqlServer.Dac

    Create a DacServices instance, call appropriate method to import (ImportBacpac) or export (ExportBacpac).

    You can download the latest version of the Data-Tier Application Framework here and find the DACFx public API documentation here.

    3) Use the Import/Export REST Endpoints

    Create a service reference to one of the following service endpoints (depending on your datacenter).

    Use the appropriate methods to import, export, selective export, and check status.

    DataCenter

    Endpoint

    North   Central US

    https://ch1prod-dacsvc.azure.com/DACWebService.svc/

    South   Central US

    https://sn1prod-dacsvc.azure.com/DACWebService.svc/

    North   Europe

    https://db3prod-dacsvc.azure.com/DACWebService.svc/

    West   Europe

    https://am1prod-dacsvc.azure.com/DACWebService.svc/

    East   Asia

    https://hkgprod-dacsvc.azure.com/DACWebService.svc/

    Southeast   Asia

    https://sg1prod-dacsvc.azure.com/DACWebService.svc/

    East   US

    https://bl2prod-dacsvc.azure.com/DACWebService.svc/

    West   US

    https://by1prod-dacsvc.azure.com/DACWebService.svc/

    -Shamitha Reddy

    Program Manager, Windows Azure SQL Database Import/Export Service


    Shamitha Reddy (Microsoft)



    Tuesday, October 16, 2012 6:31 PM