Scripting / backing up a SqlServerCE db to management studio

Question

I have been developing a site using entity framework and sql server compact edition that is no wready to be deployed. SqlCE along with EF code first has been great - it has dramatically reduced my development time.

However, I am hosting the site with an external hosting company, and due to potential issues with file access and reliability - I now wish to script the database and copy it to an instance of sql server management studio that I get included with my hosting.

• First - is it possible to script the tables in Visual Studio server explorer?

• Second - if not, how can I connect to the .sdf file in management studio? I have attempted to connect to the file in management studio (the option to open sql compact edition is there in the connect dialog), but the error This is not a valid sql server compact database file or this file version is not supported by current Sql server compact engine

Anyone got any ideas?

p.s. I am using Sql Server Management Studio 2008 R2 and Sql Server CE 4

Answer 1

Sql Server Management studio doesn't support Sql Server CE 4. The only support is in Visual Studio 2010 SP1 (I'm not sure if you need SQL Ce 4 Tools as well). I guess you can try very new EF Power Tools CTP1 which offers View Entity Data Model DDL SQL - it should be exactly what you need.

Answer 2

You can use my tools to create a script that you can run against your SQL Server Database. SSMS 2008 R2 only supports SQL Server Compact version 3.5. You can use the 4.0 version command line tools from here: http://exportsqlce.codeplex.com or my Visual Studio add-in: http://sqlcetoolbox.codeplex.com