Migrate SQL Server Compact Database SDF to SQL Server MDF File

As I was migrating an old project from a client to a new platform, his database was a SQL Server Compact 4.0. Lol, I haven't used this type of database in my entire career. :-D His objective was to migrate the schema and it's data to SQL Server 2012. Given the task, I made some research and came up with a tool created by ErikEJ on Scripting API samples. That is integrate some assemblies to the project and create some functions to write the schema and it's data to an external file.

Here's an example on how to use the tool.
1. First, make sure that the database file's Read-Only property is unchecked.
2. Create a C# console project then add the following assemblies ISqlCeScripting.dll, SqlCeScripting.dll and SqlCeScripting40.dll

3. Reference ErikEJ namespace in your project.

1	using ErikEJ.SqlCeScripting;

4. Function to generate table scripts including their constraints.

1 2 3 4 5 6 7 8 9 10 11 12 13

private static void GenerateCreateTableScript() { using (IRepository repository = new DB4Repository(@"Data Source=C:\Codes\School.sdf")) { Generator generator = new Generator(repository, null); foreach (var tableName in repository.GetAllTableNames()) { generator.GenerateTableScript(tableName); } System.IO.File.WriteAllText(@"C:\Codes\script.sqlce", generator.GeneratedScript); } }

5. Function to generate insert scripts.

1 2 3 4 5 6 7 8 9 10 11 12

private static void GenerateInsertData() { using (IRepository repository = new DB4Repository(@"Data Source=C:\Codes\School.sdf")) { Generator generator = new Generator(repository, null); foreach (var tableName in repository.GetAllTableNames()) { generator.GenerateTableContent(tableName, false); } System.IO.File.WriteAllText(@"C:\Codes\scriptInsertsqlce", generator.GeneratedScript); } }

Note: Make sure to change the path with correct values.

Reference: Scripting API Documentation