How to migrate databases between SQL Server and SQL Server Compact

In this post, I will try to give an overview of the free tools available for developers to move databases from SQL Server to SQL Server Compact and vice versa. I will also show how you can do this with the SQL Server Compact Toolbox (add-in and standalone editions).

Moving databases from SQL Server Compact to SQL Server

This can be useful for situations where you already have developed an application that depends on SQL Server Compact, and would like the increased power of SQL Server or would like to use some feature, that is not available on SQL Server Compact. I have an informal comparison of the two products here. Microsoft offers a GUI based tool and a command line tool to do this: WebMatrix and MsDeploy. You can also use the ExportSqlCe command line tool or the SQL Server Compact Toolbox to do this. To use the ExportSqlCE (or ExportSqlCE40) command line, use a command similar to:

ExportSQLCE.exe "Data Source=D:\Northwind.sdf;" Northwind.sql

The resulting script file (Northwind.sql) can the be run against a SQL Server database, using for example the SQL Server sqlcmd command line utility:

sqlcmd -S mySQLServer –d NorthWindSQL -i C:\Northwind.sql

To use the SQL Server Compact Toolbox:

Connect the Toolbox to the database file that you want to move to SQL Server:

Right click the database connection, and select to script Schema and Data:

Optionally, select which tables to script and click OK:

Enter the filename for the script, default extension is .sqlce:

Click OK to the confirmation message:

You can now open the generated script in Management Studio and execute it against a SQL Server database, or run it with sqlcmd as described above.

Moving databases from SQL Server to SQL Server Compact

Microsoft offers no tools for doing this “downsizing” of a SQL Server database to SQL Server Compact, and of course not all objects in a SQL Server database CAN be downsized, as only tables exists in a SQL Server Compact database, so no stored procedures, views, triggers, users, schema and so on. I have blogged about how this can be done from the command line, and you can also do this with the SQL Server Compact Toolbox (of course):

From the root node, select Script Server Data and Schema:

Follow a procedure like the one above, but connecting to a SQL Server database instead.

The export process will convert the SQL Server data types to a matching SQL Server Compact data type, for example varchar(50) becomes nvarchar(50) and so on. Any unsupported data types will be ignored, this includes for example computed columns and sql_variant. The new date types in SQL Server 2008+, like date, time, datetime2 will be converted to nvarchar based data types, as only datetime is supported in SQL Server Compact. A full list of the SQL Server Compact data types is available here.