How to use ExportSqlCE to migrate from SQL Server to SQL Compact

Release 2.5 of the SQL Compact schema and data script utility Codeplex project contains a new command line utility, named Export2SqlCe, that allows you to export schema and data from a SQL Server 2005/2008 database in a SQL Compact compatible SQL format.

The Export2SqlCe tool does not create a SQL Compact sdf database file, but just creates a T-SQL script , that you can run with a tool like my SqlCeCmd Codeplex utility or SQL Server Management Studio 2008. This approach gives you the flexibility to modify the script in a text editor before creating the sdf file – but requires an extra step.

Below are the steps required to migrate a SQL Server database (tables and table indexes/constraints only) to a SQL Compact database.

First, run Export2SqlCe against your SQL Server database (2005 or 2008 are supported), using a command line similar to:

Export2sqlce "Data Source=(local);Initial Catalog=AdventureworksLT;Integrated Security=True" AW.sqlce

This will create a file named C:\aw.sqlce – let’s have a look:

This file contains a script to create tables, data and constraints (indexes and foreign keys), all in SQL Compact 3.5 compatible T-SQL dialect.

Then you can either open the aw.sqlce script in SQL Server Management Studio 2008 or use sqlcecmd to create the sdf file and populate the file based on the script – meaning the whole process can be run from a batch file and completely automated!

First create the database:

sqlcecmd -d "Data Source=C:\aw.sdf" -e create

Then run the generated script against the database:

sqlcecmd -d "Data Source=C:\aw.sdf" -i aw.sqlce > log.txt

Examine the log.txt to ensure no errors occurred- search for “error code” - I got an error from AdventureworksLT about an index that referred to a non-existing column. If this happens, modify aw.sqlce to avoid any errors, or modify the source database.

Entire batch file:

Export2sqlce "Data Source=(local);Initial Catalog=AdventureworksLT;Integrated Security=True" AW.sqlce
sqlcecmd -d "Data Source=C:\aw.sdf" -e create
sqlcecmd -d "Data Source=C:\aw.sdf" -i aw.sqlce > log.txt