Export and Import (Backup and Restore) SQL Azure (SQL Server) table data using BCP utility

Tweet

bcp utility can be used for bulk copy data between two instance of SQL Server or SQL Azure. So for importing and exporting large number of rows, going for bcp utility is a best tool.

This is a command line tool, will be installed as part of SQL Server installation and introduced with SQL Server 2000 and later versions. Here, I use SQL Server 2008 R2 Express for doing this example.

For more information on bcp utility and the arguments usage, please look at the msdn link (http://msdn.microsoft.com/en-us/library/ms162802.aspx).

I use Northwind database in SQL Azure or SQL Server for exporting and importing the data. The download the Northwind database script, look at this link (https://thirumalaipm.blogspot.com/2011/05/northwind-database-scripts-for-sql.html).

1. Exporting the data from SQL Azure table:
   
   Below is the command for exporting the data to a text file –
   
   bcp database_name.schema.table_name out data_file –c –U username –P password –S server_name.database.windows.net
   
   For Example, below command is for extracting Customers table from Northwind database of my SQL Azure server. This command extract the data and store the text file at C:\DatabaseBackup\BCPBackup location. The file name will be Customers.txt.
   
   bcp Northwind.dbo.Customers out C:\DatabaseBackup\BCPBackup\Customers.txt –c –U dbuser.servername –P Pass@ord –S j5*****4*5.database.windows.net
   
   The screen shot of the text file would be:
   
   
   
   
   Note: If the output file already exists in the destination directory, it will be overwritten. The screenshot of the output text file (Customers.txt) would be.
   
   
   
   
   
2. Importing the exported data to another SQL Azure Table:
   
   Below is the command for importing the data from the text file exported –
   
   bcp database_name.schema.table_name in data_file –c –U username –P password –S server_name.database.windows.net
   
   For Example, I want to import the exported Customers table data from Northwind to another database Northwind2. So, before importing the data I should make sure to create Customers table as similar to table created in Northwind.
   
   
   
   
   bcp Northwind2.dbo.Customers in C:\DatabaseBackup\BCPBackup\Customers.txt –c –U dbuser.servername –P Pass@ord –S j5*****4*5.database.windows.net
   
   
   
   
   As already mentioned, this command will only insert when the same schema table exist in the destination table and it will only insert the data into the table.
   
   By using this utility, it is easy to import large number of rows easily. Suppose the business required to insert rows addition to the existing rows in a table, that new rows can be defined into the text file and import the data using bcp utility. Before importing the new rows, the existing rows must be removed from the text file.
   
   After running the command it will insert all the records exported
   
   
   
   
   
3. Exporting the data from (on premise) SQL Server table:
   
   Below is the command for exporting the data to a text file –
   
   For Windows Authentication
   bcp database_name.schema.table_name out data_file –c –T –S server_name\instance_name
   
   For SQL Server Authentication
   bcp database_name.schema.table_name out data_file –c –U username –P password –S server_name\instance_name
   
   For Example, below command is for extracting Customers table from Northwind database of my on premise SQL Server server using Windows Authentication.
   
   bcp Northwind.dbo.Customers out C:\DatabaseBackup\BCPBackup\Customers.txt –c –T –S SERVER_NAME\SQLEXPRESS
   
   The screen shot of the text file would be:
   
   
   
4. Importing the data to (on premise) SQL Server table:
   
   Below is the command for importing the data from the text file already exported–
   
   For Windows Authentication
   bcp database_name.schema.table_name in data_file –c –T –S server_name\instance_name
   
   For SQL Server Authentication
   bcp database_name.schema.table_name in data_file –c –U username –P password –S server_name\instance_name
   
   For Example, below command is for importing Customers data to Northwind2 database (another database in my SQL Server) using Windows Authentication.
   
   bcp Northwind.dbo.Customers in C:\DatabaseBackup\BCPBackup\Customers.txt –c –T –S SERVER_NAME\SQLEXPRESS
   
   The screen shot of the text file would be:
   
   
   
5. Exporting consolidated data from SQL Azure database and importing into SQL Server database:
   
   Exporting consolidated data is one of an useful option for Analysis at read only records. So, here I am taking an example of exporting consolidated data from SQL Azure and import into on premise SQL Server for further analysis.
   
   There are three options to export the data using bcp utility.
   
   1. By specifying Table
   2. By specifying View
   3. By specifying direct query
   
   In this example, I created a View in SQL Azure database and exported the data using bcp utility. Once the data exported, I transfered the data file to my local system and imported into my on premise SQL Server. As the table having read only data, I can do analysis further and prepare some report.
   
   Step 1: Created a View in SQL Azure database (this view returned list of Order details consolidating some tables from database)
   
   
   
   Step 2: Exported records using bcp utility
   
   
   
   Step 3: Created a table for inserting the records. So the table must have same number of columns with the types what the View has.
   
   
   
   Step 4: Imported records using bcp utility.
   
   
   
   Step 5: Imported data in on premise database
   
   

Note:

1. Before running this command make sure outbound port 1433 open on your system and the system IP address registered in the firewall settings of the SQL Azure database server.
2. While inserting the data into the table, the bcp utility enforces data validation and data checks that might cause scripts to fail if they are executed on invalid data in a data file.
3. The data can be exported into an XML format by specifying -x arguments.

For automating SQL Azure backup using Worker Role, please look at another post Automating SQL Azure backup using Worker Role.