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

Cesar De la Torre

image

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:

clip_image002

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

clip_image004

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:

clip_image006

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 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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,

CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED

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”:

clip_image008

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

clip_image009

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

clip_image011

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:

clip_image012

We paste the script and then we execute it:

clip_image014

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

clip_image015

Using BCP.EXE to EXPORT/IMPORT data

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

clip_image017

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 tcp:mysqlazureservername.database.windows.net -P mypassword

clip_image019

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

clip_image021

Easy! 🙂

0 comments

Discussion is closed.

Feedback usabilla icon