Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

5 Answers 5

up vote 6 down vote accepted

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
share|improve this answer

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

share|improve this answer

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.

share|improve this answer

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:

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.