Importing/Exporting data to SQL Azure databases using BCP and SQL Scripts


The basic way: SQL Scripts + BCP

There are also other methods you can use to export/import or even sync data like SQL Azure Migration Wizard, SQL Azure Data Sync, and even SSIS (SQL Server Integration Services).

Also, instead of creating database schema using SQL Scripts, another new option is using the new DACPAC (Data Tier Packages), in SQL Server 2008 R2, SQL Azure and Visual Studio 2010.

But, in this post I’m going to show the most basic way, but also, a way that you can instrument from execution scripts , etc.

First of all, we need a SQL Script to apply to our empty SQL Azure database. SQL Server 2008 R2 Management Studio has a new convenient feature: “Generate Scripts for SQL Azure”.

So, right-click on your SQL Server database and select Tasks –> Generate Scripts… option:


Then, you’ll be able to set the ‘Scripting Options’:


But, especially, we want to specify that the SQL Script must be generated for SQL Azure compatibility, therefore, you have to enter into the Advanced Settings, pressing the ‘Advanced’ button:


So now, we have the SQL Script file containing our database schema, but specifically made for SQL Azure. We’ll have something like the following script:

/****** Object: Table [dbo].[Product] Script Date: 06/01/2010 15:16:21 ******/





CREATE TABLE [dbo].[Product](

[ProductId] [int] IDENTITY(1,1) NOT NULL,

[ProductDescription] [nvarchar](100) NULL,

[UnitPrice] [money] NULL,

[UnitAmount] [nvarchar](50) NULL,

[Publisher] [nvarchar](200) NULL,

[AmountInStock] [smallint] NULL,


Now, we’re able to create our Database in SQL Azure and apply this SQL Script to it, so we’ll have our tables.

Go to Windows Azure portal and then to your SQL Azure site and create an empty database. In my case, I created a new database called “NLayerApp”:


Now we can connect to SQL Azure from SQL Server 2008 R2 Management Studio, providing the DNS server name:


And therefore, we can see our SQL Azure database already created:


Next step would be to execute our SQL Script, in order to create our tables and database objects. We create a new query and paste our file content within it:


We paste the script and then we execute it:


So, at the end, we can see our tables within the SQL Azure database:



The bcp utility is a command line utility that ships with Microsoft SQL Server. It bulk copies data between SQL Azure (or SQL Server) and a data file in a user-specified format. The bcp utility that ships with SQL Server 2008 R2 is fully supported by SQL Azure.

You can use BCP to backup and restore your data on SQL Azure database.

You can import large numbers of new rows into SQL Azure tables or export data out of tables into data files by using bcp.

The bcp utility is not a migration tool. It does not extract or create any schema or format information from/in a data file or your table. This means, if you use bcp to back up your data, make sure to create a schema or format file somewhere else to record the schema of the table you are backing up. bcp data files do not include any schema or format information, so if a table or view is dropped and you do not have a format file, you may be unable to import the data. The bcp utility has several command line arguments. For more information on the arguments, see SQL Server Books Online documentation. For more information on how to use bcp with views, see Bulk Exporting Data from or Bulk Importing Data to a View.

Now, we can export data from our SQL Server database, using BCP.EXE, in this case using integrated security:

bcp NLayerApp.dbo.Customer out “C:\MyFolderPath\Customer.txt” -T -c -S WIN7VS2010RC1\SQLEXPRESS


Then, we’ll have a generated file with all the table’s data.

Now, we import that data into SQL-Azure database:

bcp TestDB.dbo.Customer in “C:\Users\cesardl\Desktop\Arquitectura Marco .NET DPE Spain\_Windows Azure V\BCP\Customer.txt” -c -U mysqlazureuser@mysqlazureservername -S -P mypassword


Now, we can check that data is already within the SQL Azure database:


Easy! 🙂

Comments (6)

  1. Yasser_Bilog says:


    Yes it is a tutorial with its simplicity is very useful.

    The tutorials that already exist on the net and said almost the same thing, does not feature motion compatibility: "engine type".

    The trick is that you also have a SQL Server 2008 R2 (especially R2).


  2. Marilena_Derdelakou says:

    I tried scripting database objects from SQL R2 but the option for SQL Azure wasn't there. Should i download a SP?

  3. Sam says:


    the "SQL Azure Database" value should be specified for the "Script for the database engine type" property and not the "Script for Server Version" property in the Advanced Scripting options dialog box…

  4. does the file need to be in azure for bulk import ? says:

    does the file need to be in azure for bulk import ?

  5. Cesar says:

    No, the file will be usually in your local machine or local network where your SQL Server 'on-premise' is placed.

  6. Thomas says:

    Try to sync data between SQL Azure and local DBs. It supports UPDATE scripts (not only INSERT) and its also helpful for non R2 versions.