Databases Reference
In-Depth Information
4.
In the Connect to Database Engine dialog, enter the information for your SQL Azure instance,
and enter the name of the database you just created on the Connection Properties tab.
5.
Click Connect.
You now have your script, a database, and a connection to that database. Click the Execute button. Your script
should run and create the tables, procedures, and data in your SQL Azure Database.
The SQL Server Generate and Publish Script wizard is a great way to start understanding the required changes
that need to be made when migrating to SQL Azure. With this foundation, let's discuss one of the other options, the
bcp
utility.
The bcp Utility
The
bcp
utility provides bulk copying of data between instances of Microsoft SQL Server. This utility is installed with
SQL Server and requires no knowledge or understanding of T-SQL syntax. If you aren't familiar with the
bcp
utility,
don't confuse or associate its functionality with that of the Import/Export Wizard in SQL Server. Although the
bcp
documentation refers to what
bcp
does as a “bulk copy,” be aware that you can't
bcp
data from a source into a destination
with a single statement. You must first
bcp
the data out of the source; then, you can
bcp
the data in to the destination.
The
bcp
utility is a great way to move data. Since it doesn't do schema (that is, the schema must already exist), the
bcp
utility is a very quick way of migrating data. For example, you need to refresh your SQL Database with data from
an on-premises database, the
bcp
option is a great solution to do that.
■
the
bcp
utility is very flexible and powerful, and you can apply a lot of options to it. this section doesn't go into
the entire range of
bcp
options or dive deep into the many uses of the utility. You can find that information in the SQL Server
Books online or on the Microsoft MSDn web site at
http://msdn.microsoft.com/en-us/library/ms162802.aspx
.
Note
This section describes how to use the
bcp
utility to export data from a local database and import the data into
your SQL Azure database. It also discusses some things you should watch out for when using the
bcp
utility for
SQL Azure.
Invoking bcp
The
bcp
utility has no GUI; it's a command prompt-driven utility. But don't let that intimidate you, especially given
what you're using it for. It's very flexible and can seem a bit overwhelming, but it's quite simple. The basic syntax for
the
bcp
utility is as follows:
bcp
table direction filename
-
servername
-
username
-
password
where:
table
is the source or destination table based on the direction parameter.
•
direction
is
in
or
out
, depending on whether you're copying data into the database or out of
the database.
•
filename
is the filename you're copying data to or from.
•
servername
is the name of the server you're copying data to or from.
•
username
is the username used to connect to either the local or SQL Azure database.
•
password
is the password associated with the username.
•
Let's get started by exporting the data from your source database.
