How to migrate schema from SQL Server 2008 to SQL Server CE 4?

Yes, the title is correct

the app now needs a local database which is going to be ssce4.0 开发者_运维技巧and hence...

is there's any tool to help with that ?

There is actually a highly-rated and well reviewed CodeProject article that does pretty close to what you're looking for. (It seems to support SSCE up to version 3.5, not 4.0) I didn't see anything commercial grade but hey, free is a very good price, especially when you can muck with the source code!

You can use my scripting tools, that support 4.0, http://exportsqlce.codeplex.com and http://sqlcetoolbox.codeplex.com. They allow you to export both schema and data from SQL Server 2005+ to SQL Compact 3.5 and 4.0. See this blog post for a walkthrough (command line): http://erikej.blogspot.com/2010/02/how-to-use-exportsqlce-to-migrate-from.html