12

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?

9

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
  • 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 – tobiak777 Jul 27 '15 at 8:39
  • @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. – Nick Chammas Jul 27 '15 at 22:35
4

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.

4

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

2

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:

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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