Let us take a closer look at the SQL server BCP Azure and the configurations required to set up the backup process with BCP. At Bobcares our MSSQL support services will give you a detailed note on the whole process.

BCP and SQL Azure

BCP is an excellent solution to back up SQL Azure data locally, and by altering the BCP-based backup files, we can import data into SQL Azure as well. Now with this article let’s learn more on:

• How to export data from SQL Azure server tables into a data file using BCP?
• How to use the BCP utility to import new rows into SQL Azure tables from a data file?

What is BCP?

The BCP utility is a command-line tool included with Microsoft SQL Server. It performs bulk data transfers between SQL Azure (or SQL Server) and a data file in a user-specified format. SQL Azure fully supports the BCP utility included with SQL Server 2008 R2.

BCP can be used to back up and restore data on SQL Azure. Using BCP, we can import large amounts of new rows into SQL Azure tables or export data from tables into data files.

The BCP utility is not a tool for migration. It does not extract or generate any schema or format information from a data file or table. This means that if we use BCP to back up the data, we must generate a schema or format file somewhere else to reflect the schema of the table being backed up.

Because BCP data files do not carry any schema or format information, we may be unable to import the data if a table or view is discarded and we do not have a format file. There are various command line arguments available for the BCP utility.

Exporting Data out of SQL Azure with BCP

To explain this better, Consider the following: we are developers or database administrators. We wish to back up a large amount of data on SQL Azure.

To export data from the SQL Azure database, use the following command in the Windows command prompt:

bcp AdventureWorksLTAZ2008R2.SalesLT.Customer out C:\Users\user\Documents\GetDataFromSQLAzure.txt -c -U username@servername -S tcp:servername.database.windows.net -P password

Exporting Data out of SQL Azure with BCP

Similarly, we may have existing data in a local database that we wish to migrate to the cloud. We require a really easy method to accomplish this.

Create a data file with the same schema format as the destination table in the SQL Azure database to import data into it.

For instance, rename the GetDataFromSQLAzure.txt file MoveDataToSQLAzure.txt, delete all current entries, and add 5 new items as shown below.

Then, at the Windows command prompt, type the following:

bcp AdventureWorksLTAZ2008R2.SalesLT.Customer in C:\Users\user\Documents\MoveDataToSQLAzure.txt -c -U username@servername -S tcp:servername.database.windows.net -P password

In the command line window, this will result in the following output:

 Adventure arkeLTAZ200 R2. Salult.Customer out Cusers Starting copy...
199 copied.
Network packet size bytes) 204 Average : 1344.55 rows per sec.)


It should be noted that the Output has been altered to safeguard sensitive information. The BCP utility accepts a variety of command line parameters.

Command lines

• database name.schema. table name: The database name identifies the database that contains the given table or view. If not selected, this is the user’s default database.

  While importing data into the SQL Azure server (in), the table name is the name of the destination table; when exporting data from the SQL Azure server, the table name is the name of the source table (out).
• in: inserts data from a file into a database table or view.
• out: saves a copy of a database table or view to a file. If we specify an already existing file, it gets rewritten.
• -c: Uses a character data type to carry out the operation.
• -U: The login ID used to connect to the SQL Azure server. Using the login>@servername> notation, we must append the SQL Azure server name to the login name in the connection string.
• -S: Indicates the SQL Azure server to which we want to connect. The SQL Azure server’s fully qualified name is servername.database.windows.net.
• -i: Specifies the input file.
• queryout: Beginning with SQL Server 2008 R2, SQL Azure also supports the queryout option. When using the queryout parameter, provide the -d database name argument. Otherwise, the BCP program will be unable to find a database to connect to.

This blog post demonstrated how to utilize BCP with SQL Azure in a straightforward manner. Even if we have millions of records in the table in the local database, it remains fast and simple to use.

[Need assistance with similar queries? We are here to help]

Conclusion

To conclude we have now learned more about the SQL server BCP azure and importing and exporting data into and out of SQL Azure with the support of MSSQL support services.