How do I copy SQL Azure database to my local development server

Question

Does anyone know how I can copy a SQL Azure database to my development machine  I d like to stop paying to have a development database in the cloud  but it s the best way to get production data  I copy my production database to a new development database but I d like to have that same database local    Any suggestions

User · Answer

Using msdeploy exe  Caveat  msdeploy exe fails to create the destination database on its own  so you need to create it manually first    Copy the connection string on the database properties page  Adjust it so that it contains a correct password   Get the connection string for the destination DB  Run msdeploy exe like this    c  Program Files IIS Microsoft Web Deploy V3 msdeploy exe  -verb sync -dest dbDacFx  destination DB connection string  dropDestinationDatabase true -source dbDacFx  azure DB connection string  includeData true -verbose    Using SqlPackage exe   Export the azure DB to a bacpac package    c  Program Files  x86  Microsoft SQL Server 110 DAC bin SqlPackage exe   a Export  ssn  azure db server   sdn  azure db name   su  user name   sp  password   tf  file bacpac   Import the package to a local DB    c  Program Files  x86  Microsoft SQL Server 110 DAC bin SqlPackage exe   a Import  SourceFile  file bacpac   TargetServerName    SQLEXPRESS   TargetDatabaseName CopyOfAzureDb

User · Answer

Download Optillect SQL Azure Backup - it has 15-day trial  so it will be enough to move your database

User · Answer

I always use Import Export Feature which seems to be the easiest one among all   Step 1   Get the backup from the azure instance as follows   Select the database   Right click   Tasks    Export Data Tier Application   Step 2  Give a specific name for the backup file and save it in your desired location  Step 3  That s it you have taken a backup of the database from sql instance to your local  Lets restore it to the local  Copy the backed up database to your C drive  Now open the PowerShell with administrator rights and navigate to C drive  Step 4  Lets download the powershell script to remove the master keyRemoveMasterKey ps1 have the script on the same drive in this case its C   Step 5   Run the script as follows     RemoveMasterKey ps1 -bacpacPath  C  identity bacpac   That s it  now you can restore it on MSSQL 2017 in your local environment   Step 6  Connect to your local server  and click Databases   Import-Data-Tier-Application  Step 7   Give a name for your database to restore    Now you will see everything in green   Read my blog with diagrams

User · Answer

The trick for me was to start replicating PKs FKs constraints on empty DB  then temporarily disable constraints while importing data  see https   stackoverflow com a 161410    More precisely    Create empty target DB manually  Right-click source DB   Tasks   Generate Scripts  Run script file on empty target DB  now DB has correct PKs FKs constraints  but no data   Disable all constraints  Import data  Right-click target DB   Tasks   Import Data   Re-enable constraints    Hope this helps

User · Answer

Hi I m using the SQLAzureMW tool for SQLAzure DB migration and management  Very useful one  It was downloaded from codeplex  but currently it s not available the codeplex will be going to shutdown  the same application tool is now available in GttHub  This below link is explain how to use this tool and also available the Application for download   https   github com twright-msft azure-content blob master articles sql-database sql-database-migration-wizard md

User · Answer

Regarding the   I couldn t get the SSIS import   export to work as I got the error  Failure inserting into the read-only column  id     This can be gotten around by specifying in the mapping screen that you do want to allow Identity elements to be inserted   After that  everything worked fine using SQL Import Export wizard to copy from Azure to local database   I only had SQL Import Export Wizard that comes with SQL Server 2008 R2  worked fine   and Visual Studio 2012 Express to create local database

User · Answer

There are multiple ways to do this    Using SSIS  SQL Server Integration Services   It only imports data in your table  Column properties  constraints  keys  indices  stored procedures  triggers  security settings  users  logons  etc  are not transferred  However it is very simple process and can be done simply by going through wizard in SQL Server Management Studio   Using combination of SSIS and DB creation scripts  This will get you data and all missing metadata that is not transferred by SSIS  This is also very simple  First transfer data using SSIS  see instructions below   then create DB Create script from SQL Azure database  and re-play it on your local database   Finally  you can use Import Export service in SQL Azure  This transfers data  with a schema objects  to Azure Blob Storage as a BACPAC  You will need an Azure Storage account and do this in Azure web portal  It is as simple as pressing an  Export  button in the Azure web portal when you select the database you want to export  The downside is that it is only manual procedure  I don t know a way to automate this through tools or scripts -- at least the first part that requires a click on the web page    Manual procedure for method  1  using SSIS  is the following    In Sql Server Management Studio  SSMS  create new empty database on your local SQL instance  Choose Import Data from context menu  right click the database -  Tasks -  Import data     Type in connection parameters for the source  SQL Azure   Select   Net Framework Data Provider for SqlServer  as a provider  Choose existing empty local database as destination  Follow the wizard -- you will be able to select tables data you want to copy  You can choose to skip any of the tables you don t need  E g  if you keep application logs in database  you probably don t need it in your backup     You can automate it by creating SSIS package and re-executing it any time you like to re-import the data  Note that you can only import using SSIS to a clean DB  you cannot do incremental updates to your local database once you already done it once    Method  2  SSID data plus schema objects  is very simple  First go though a steps described above  then create DB Creation script  righ click on database in SSMS  choose Generate Scripts -  Database Create   Then re-play this script on your local database   Method  3 is described in the Blog here  http   dacguy wordpress com 2012 01 24 sql-azure-importexport-service-has-hit-production   There is a video clip with the process of transferring DB contents to Azure Blob storage as BACPAC  After that you can copy the file locally and import it to your SQL instance  Process of importing BACPAC to Data-Tier application is described here  http   msdn microsoft com en-us library hh710052 aspx

User · Answer

In SQL Server Management Studio  Right click on the database you want to import  click Tasks   Export data-tier application  and export your database to a local  dacpac file   In your local target SQL server instance  you can right click Databases   Import data-tier application  and once it s local  you can do things like backup and restore the database

User · Answer

Copy Azure database data to local database  Now you can use the SQL Server Management Studio to do this as below    Connect to the SQL Azure database  Right click the database in Object Explorer  Choose the option  Tasks     Deploy Database to SQL Azure   In the step named  Deployment Settings   connect local SQL Server and create New database       Next     Next     Finish

User · Answer

Use the Import Export service in SQL Azure to create a  bacpac file   Then take a look at this method in another Stack Overflow article   Azure SQL Database Bacpac Local Restore

User · Answer

I think it is a lot easier now    Launch SQL Management Studio  Right Click on  Databases  and select  Import Data-tier application     The wizard will take you through the process of connecting to your Azure account  creating a BACPAC file and creating your database    Additionally  I use Sql Backup and FTP  https   sqlbackupandftp com   to do daily backups to a secure FTP server   I simply pull a recent BACPAC file from there and it import it in the same dialog  which is faster and easier to create a local database

User · Answer

If anyone has a problem to import a Bacpac of a DB that uses Azure SQL Sync  Sandrino Di Mattia developed a great simple application to solve this    Export a Bacpac of your DB Dowload Di Mattia s binary With this console app repair the downloaded Bacpac Lauch SSMS Right Click on  Databases  and select  Import Data-tier Application  Select the repaired Bacpac

User · Answer

You can use the new Azure Mobile Services to do a nightly backup export from SQL Azure to a  bacpac file hosted in Azure Storage  This solution is 100  cloud  doesn t require a 3rd party tool and doesn t require a local hosted SQL Server instance to download copy backup anything    There s about 8 different steps  but they re all easy   http   geekswithblogs net BenBarreth archive 2013 04 15 how-to-create-a-nightly-backup-of-your-sql-azure aspx

User · Answer

Now you can use the SQL Server Management Studio to do this    Connect to the SQL Azure database  Right click the database in Object Explorer  Choose the option  Tasks     Deploy Database to SQL Azure   In the step named  Deployment Settings   select your local database connection   Next     Next     Finish

User · Answer

You can try with the tool  SQL Database Migration Wizard   This tool provide option to import and export data from azure sql   Please check more details here   https   sqlazuremw codeplex com

User · Answer

It s pretty easy  This worked for me   in terms of getting an Azure SQL database down onto your local machine       Open your SQL Management Studio and connect to your Azure SQL Server  Select the database you would like to get down onto your local machine  and     right-click   select  Generate Scripts    Follow the prompts      BUT  be careful in that if you ALSO want the DATA  as well as the scripts  be sure to check the Advanced Options before beginning the generating   scroll down to  Types of data to script   and make sure you have  Schema and data    or whatever is appropriate for you   It will give you a nice SQL script file which can then be run on your local machine and it will create the database as well as populate it with all the data   Bare in mind that in my case  I have no FK or other constraints   also  it wasn t a lot of data   I don t recommend this as a backup mechanism in general

User · Answer

If anyone wants a free and effective option  and don t mind doing it manually  to backup database to Local then use schema and data comparison functionality built into the latest version Microsoft Visual Studio 2015 Community Edition  Free  or Professional   Premium   Ultimate edition  It works like a charm   I have BizPark account with Azure and there is no way to backup database directly without paying  I found this option in VS works    Answer is taken from https   stackoverflow com a 685073 6796187

User · Answer

You can also check out SQL Azure Data Sync in the Windows Azure Management Portal   It allows you to retrieve and restore an entire database  including schema and data between SQL Azure and SQL Server

User · Answer

I couldn t get the SSIS import   export to work as I got the error  Failure inserting into the read-only column  id    Nor could I get  http   sqlazuremw codeplex com  to work  and the links above to SQL Azure Data Sync didn t work for me   But I found an excellent blog post about BACPAC files  http   dacguy wordpress com 2012 01 24 sql-azure-importexport-service-has-hit-production   In the video in the post the blog post s author runs through six steps    Make or go to a storage account in the Azure Management Portal  You ll need the Blob URL and the Primary access key of the storage account   The blog post advises making a new container for the bacpac file and suggests using the Azure Storage Explorer for that   N B  you ll need the Blob URL and the Primary access key of the storage account to add it to the Azure Storage Explorer    In the Azure Management Portal select the database you want to export and click  Export  in the Import and Export section of the ribbon   The resulting dialogue requires your username and password for the database  the blob URL  and the access key  Don t forget to include the container in the blob URL and to include a filename  e g  https   testazurestorage blob core windows net dbbackups mytable bacpac   After you click Finish the database will be exported to the BACPAC file  This can take a while  You may see a zero byte file show up immediately if you check in the Azure Storage Explorer  This is the Import   Export Service checking that it has write access to the blob-store   Once that is done you can use the Azure Storage Explorer to download the BACPAC file and then in the SQL Server Management Studio right-click your local server s database folder and choose Import Data Tier Application that will start the wizard which reads in the BACPAC file to produce the copy of your Azure database  The wizard can also connect directly to the blob-store to obtain the BACPAC file if you would rather not copy it locally first    The last step may only be available in the SQL Server 2012 edition of the SQL Server Management Studio  that s the version I am running   I do not have earlier ones on this machine to check  In the blog post the author uses the command line tool DacImportExportCli exe for the import which I believe is available at http   sqldacexamples codeplex com releases

User · Answer

In SQL Server 2016 Management Studio  the process for getting an azure database to your local machine has been streamlined   Right click on the database you want to import  click Tasks   Export data-tier application  and export your database to a local  dacpac file   In your local target SQL server instance  you can right click Databases   Import data-tier application  and once it s local  you can do things like backup and restore the database

User · Answer

The accepted answer is out of date  I found a better answer  Use Import Data-tier Application  More detailed information please see this article  Restoring Azure SQL Database to a Local Server

User · Answer

I just wanted to add a simplified version of dumbledad s answer  since it is the correct one    Export the Azure SQL Database to a BACPAC file on blob storage  From inside SQL Management studio  right-click your database  click  import data-tier application   You ll be prompted to enter the information to get to the BACPAC file on your Azure blob storage  Hit next a couple times and    Done

[sql-server] How do I copy SQL Azure database to my local development server?

Using `msdeploy.exe`

Using `SqlPackage.exe`

Examples related to sql-server

Examples related to azure

Examples related to azure-sql-database

Examples related to database-migration