By Nick Chammas


2011-09-16 22:12:58 8 Comments

I have an OLTP database hosted on a SQL Azure instance. I want to pull a copy of the database down from the cloud so I can run some heavy extracts and OLAP-style queries against it without impacting the source database.

How do I pull a copy of the database down to a local SQL Server instance?

5 comments

@user16741 2013-01-04 15:46:09

you can use the SSMS Data import wizard. Here is a good blog post about it, with pictures. The key point is to select .NET data provider for the source database. I had a lot of problems trying to make it work with other (and default - native) providers.

@Marian 2012-11-14 23:03:38

I noticed there's a new tool in this list, it's Idera's Azure SQL Database Backup. It's free and they usually make good tools, so it's worth a try.

Another way of exporting databases (but this time only schema, no data) to Azure is by using DAC packages (.dacpac files extracted from Management Studio or Visual Studio 2010). This works only from SQL 2008 R2 SP1+.

You can read details about the Data-tier Applications here:

@Nick Chammas 2011-09-16 22:22:07

Red Gate has an aptly named SQL Azure Backup Tool. It's free, but Red Gate has discontinued support for it.

@Nick Chammas 2011-09-16 22:58:01

Compose a bcp script that exports the contents of all your tables to local files.

Start by writing a query that will output a bcp command to export each table in your target database to a path on your destination machine:

SELECT 
      'bcp '
    + SCHEMA_NAME(schema_id) + '.' + name
    + ' out '
    + ' D:\local_backup_directory\' + SCHEMA_NAME(schema_id) + '.' + name + '.txt'
    + ' -c '
    + ' -S servername.database.windows.net '
    + ' -d database_name '
    + ' -U username '
    + ' -P password'
FROM sys.tables;

Execute this query using bcp against your SQL Azure database from the machine you want to copy to and save the results to a cmd file. Execute that cmd file to export each table to a text file.

C:\> REM ask bcp to save the results of the above query to a file
C:\> bcp "SELECT      'bcp '    + SCHEMA_NAME(schema_id) + '.' + name   + ' out '   + ' D:\backup_directory\' + SCHEMA_NAME(schema_id) + '.' + name + '.txt'    + ' -c '    + ' -S servername.database.windows.net '    + ' -d database_name '  + ' -U username '   + ' -P password' FROM sys.tables;" queryout output_path\bcp_script.cmd -c -S servername.database.windows.net -d database_name -U username -P password

C:\> REM execute the bcp commands saved to file
C:\> output_path\bcp_script.cmd

This is a quick and dirty approach, and is not suitable for large databases or complex schemata.

@Nick Chammas 2011-09-16 22:19:46

Use the SQL Azure Migration Wizard:

The SQL Azure Migration Wizard (SQLAzureMW) gives you the options to analyzes, generates scripts, and migrate data (via BCP) from:

  1. SQL Server to SQL Azure
  2. SQL Azure to SQL Server
  3. SQL Azure to SQL Azure

@tobiak777 2015-07-27 08:39:32

Doesn't seem to work with LocalDB, I have an error saying that 'Encryption is not supported' at the step when you select the destination database. The tool is not able to connect to my localdb instance

@Nick Chammas 2015-07-27 22:35:44

@red2nb - I don't know anything about LocalDB, but perhaps as the error suggests it's not an issue with LocalDB, but an issue with the destination database being encrypted.

Related Questions

Sponsored Content

1 Answered Questions

1 Answered Questions

1 Answered Questions

[SOLVED] Azure SQL DB vs Azure managed instance

2 Answered Questions

3 Answered Questions

[SOLVED] Migrating or exporting a database from azure to SQL Server 2016

1 Answered Questions

0 Answered Questions

1 Answered Questions

[SOLVED] How do I back up On Premise Microsoft SQL Server 2008 to Azure cloud

1 Answered Questions

[SOLVED] Connecting to an SQL Azure Database from SSMS - IP Restriction

Sponsored Content