The current state of Sql Server 2008 R2 seems like it has a ways to go when dealing with Sql Azure. I tried several different ways of exporting data to load into Sql Azure and after several attempts, I have documented what I needed to move data over. Here is a quick and dirty way of moving data from Sql Server 2008 R2 to Sql Azure:
The first thing I did was generate scripts from my local sql server 2008 R2 database.
Next, you will have to go through the wizard steps of selecting what you would like scripted out:
The following step requires a a few checks.
-
Make sure Convert UDDTs as Base Types = True
-
Script Extended Properties = False
-
Script Logins = False
-
Script USE DATABASE = False
-
Types of data to script = Schema and data (Note: If you already applied the schema, then you can choose data only)
SQL Azure currently does not support windows authentication, USE statements, extended properties, or user-defined data types.
You now have a generated sql file.
Next, Open the sql file generated by the script and delete the following statement from the generated CREATE TABLE statement ( "WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]" )
Also, delete the ( "ON PRIMARY]" ) clause from the create table as well.
Now we are ready to connect to SQL Azure and run the script. Make a connection to SQL Azure.
Follow Step 1, 2, and 3 below.
On connect to database, select the target database from the drop down list.
Once connected, open Query menu and load the generated sql file. Press F5 to execute the script.