Skip to main content

How to Copy Azure SQL Database to Local SQL Server

Azure SQL Database is a relational database-as-a service using the Microsoft SQL Server Engine. SQL Database is a high-performance, reliable, and secure database you can use to build data-driven applications and websites in the programming language of your choice, without needing to manage infrastructure.
Some times you may want to create a copy of azure database on you local server. So that you can use it for development and bugging activities.
Here’s what you do when you want to get a local copy of an Azure SQL Database.

Export Azure Database

  1. Select your database in the Azure Portal.
  2. Click Export in the Header
  3. Give it a name, storage, container, etc and start the export.
  4. Once export is completed. Select your server and scroll to the bottom and click on Import/Export History

Import to Local SQL Server

Once the Export has completed, you now Import it to your local machine. This works with SQL Express too, just install the BOTH x86 and x64 versions of the DAC Framework first: https://www.microsoft.com/en-us/download/details.aspx?id=45886
  1. Open SQL Management Studio and Right Click on Databases and Select Import Data-tier Application
  2. Connect to your Azure Database Export and click Next a couple of times.
That should be it. If it fails, make sure you install the DAC Framework as mentioned above.
If you are more comfortable with the CLI, then you can use following commands to restore a local copy Azure SQL DB on you local SQL Server.

Using msdeploy.exe

msdeploy.exe fails to create the destination database on its own, so you need to create it manually first.
  1. Copy the connection string on the database properties page. Adjust it so that it contains a correct password.
  2. Get the connection string for the destination DB.
  3. 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

  1. 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"
2. 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

Comments

Post a Comment

Popular posts from this blog

gcAllowVeryLargeObjects Element

There are numerous new features coming with .NET 4.5 and here, on this blog, you can find several posts about it. But the feature we are goint to talk about today is very exciting, because we were waiting for it more than 10 years. Since .NET 1.0 the memory limit of .NET object is 2GB. This means you cannot for example create array which contains elements with more than 2GB in total. If try to create such array, you will get the OutOfMemoryException. Let’s see an example how to produce OutOfMemoryException. Before that Open Visual Studio 2012, and create C# Console Application, like picture below. First lets create simple struct with two double members like example below: 1 2 3 4 5 6 7 8 9 10 11 12 public struct ComplexNumber {      public double Re;      public double Im;      public ComplexNumber( double re, double im)      {    ...
Post a Comment
Read more

Support for debugging lambda expressions with Visual Studio 2015

Anyone who uses LINQ (or lambdas in general) and the debugger will quickly discover the dreaded message “Expression cannot contain lambda expressions”. Lack of lambda support has been a limitation of the Visual Studio Debugger ever since Lambdas were added to C# and Visual Basic.  With visual studio 2015 Microsoft has added support for debugging lambda expressions. Let’s first look at an example, and then I’ll walk you through current limitations. Example To try this yourself, create a new C# Console app with this code: using System.Diagnostics; using System.Linq; class Program { static void Main() { float[] values = Enumerable.Range(0, 100).Select(i => (float)i / 10).ToArray(); Debugger.Break(); } } Then compile, start debugging, and add “values.Where(v => (int)v == 3).ToArray()” in the Watch window. You’ll be happy to see the same as what the screenshot above shows you. I am using Visual Studio 2015 Preview and it has some limitati...
1 comment
Read more

An Introduction to Windows Azure Table Storage

Windows Azure Tables are a non-relational, key-value-pair, storage system suitable for storing massive amounts of unstructured data.  Whereas relational stores such as SQL Server, with highly normalized designs, are optimized for storing data so that queries are easy to produce, the non-relational stores like Table Storage are optimized for simple retrieval and fast inserts.  This article will cover the very basics of Windows Azure Table storage and provide you with resources and suggested topics to continue your learning. Some people, when first learning about the Windows Azure platform, find it hard to understand the purpose of the Table Storage feature.  This is especially true of those who are familiar with developing applications using highly relational data.  To get a good understanding of how a Key-Value Pair system differs from a traditional relational database you can read Buck Woody’s article on the topic in his continuing series:...
Post a Comment
Read more