BCP is an ideal straightforward command prompt driven tool to push data from one table to another similar table. This makes it an excellent tool to carry out data migration from SQL server to SQL Azure. In this blog post, we will see how we can migrate SQL server data to SQL Azure using BCP tool.

If you are looking to migrate SQL server database (data + schema) to SQL Azure, you should first generate table schema and then BCP data from SQL server to SQL Azure (One of the tool to carry out schema migration is Generate script wizard)

I have a table in SQL Azure DB that is similar (schema wise) to a table in my local SQL server database. And my aim is to migrate data from table in local SQL server database to the table in SQL Azure DB.

Table in local SQL server 2008R2 database:

Table in SQL Azure, Before Data Migration:

Now to carry out the data migration, the first step is to export data out from SQL server’s Table.

The format to Export table using BCP is: bcp table out filename -servername -username –password

Now here is the command in my case:  bcp adventureworkslt2008r2.saleslt.address out c:\temp\datafromsqlserver.txt -c -S (local) –T

here ‘adventureworkslt2008r2.saleslt.address’ is the table. ‘out’ states that we are exporting data out of the table. ‘c:\temp\datafromsqlserver.txt’ is the location of the text file where data will be pushed. “–S (local) –T” is to state that I want to connect to SQL server instance named (local) and ‘–T’ is used for windows Authentication. And if you are using SQL server Authentication, you can specify it in the U username-S servername-P password format.

Now, after we have exported the data from a local table, it’s time to push the data to cloud! So we will import the data to SQL Azure from the text file (we created the text file in above step, remember?!)

The format to Import table using BCP is bcp table in filename -servername -username –password

Now here is the command in my case: bcp adventureworksltaz2008r2.saleslt.address in c:\temp\datafromsqlserver.txt -c -U parassqlazure@bpqjia83ct -S tcp:bpqjia83ct.database.windows.
net –P *************

Now,  Let’s see the status of the table in SQL Azure database. Were we able to push those rows (Data) to cloud?!

Table in SQL Azure, After Data Migration:

Voila! as you might have noticed that we were able to migrate 450 rows (data) from ‘local’ table to ‘cloud’ table using BCP! it’s very easy and straight forward, isn’t it? And what’s more, it is an excellent tool to push lots of data to SQL Azure!

That’s it. please take a moment and drop your valuable feedback/comment/suggestion. Thank you!

Further read:

How to use SQL server Integration services (SSIS) to migrate data from SQL server to SQL Azure << Paras Doshi

“Migrating Data into Microsoft’s Data Platform – SQL Azure” << SolidQ Journal Article

Migrating TSQL file and SQL server 2005 / 2008 Database to SQL Azure using SQL Azure Migration wizard << Paras Doshi

About BCP utility

BCP and SQL Azure