Best way to migrate a SQL CE 4.0 database to Sql A

Question

I've got an older database, developed in VS2010, which I would like to move to SQL Azure.

I was hoping I could just upload the .sdf file to SQL Azure, but couldn't find a way to get that to work.

I can't seem to open the .sdf file in Management Studio 2008 (I get "Incompatible Database Version"), so the easy option of generating a script is not available.

Any quick and easy way to get this migration done?

Answer 1

Your best bet is using SQL Server Compact & SQLite Toolbox, it's a Visual Studio Add-in

• In Visual Studio (I'm using 2012) go to Tools > Extensions and Updates
• Search online for "SQL Server Compact & SQLite Toolbox" and install it.

You will have to restart Visual Studio after install.

• Open your solution containing the .sdf file
• Connect to your SQL CE database (Your SQL CE database is probably already under Data Connections)
• Select it
• Click on the (new) icon for SQL Server Compact & SQLite Toolbox
• In the SQL Server Compact/SQLite Toolbox right click on the database you want to export
• Script Database > Script Database Schema and Data for SQL Azure
• Select all tables
• Save the script as a .sql file.

Run the script against your SQL Azure database If the data script is more than 50MB (which might be the usual case, you can run them with sqlcmd):

sqlcmd -U user_dbo -P password -S cloudserver -d Database -i c:\dataScript.sql -o c:\azuremigration.txt

Answer 2

Use my SQL Server Compact addin/ standalone app to generate a SQL Azure compatible script and run the script against the SQL Azure database