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?
There are multiple ways to do this:
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. Manual procedure for method #1 (using SSIS) is the following:
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.
Copy Azure database data to local database: Now you can use the SQL Server Management Studio to do this as below:
"Next" / "Next" / "Finish"
I just wanted to add a simplified version of dumbledad's answer, since it is the correct one.
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.
I think it is a lot easier now.
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.
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.
msdeploy.exeCaveat: msdeploy.exe fails to create the destination database on its own, so you need to create it manually first.
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
SqlPackage.exeExport 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
It's pretty easy. This worked for me...in terms of getting an Azure SQL database down onto your local machine...:
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...
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.
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
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.
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
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.
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
Download Optillect SQL Azure Backup - it has 15-day trial, so it will be enough to move your database :)
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:
Hope this helps!
Now you can use the SQL Server Management Studio to do this.
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.
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
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.
