SQL Server Compact Code Snippet #19 : migrate a SQL Server database to SQL Compact

This snippet again demonstrates my SQL Server Compact scripting API, some initial guidance here. For a blog post describing the opposite direction, see this. Notice, that is you are scripting a SQL Server Compact 4.0 database file, you can install the required DLL files via NuGet (ErikEJ.SqlCEScripting).

This time I will demonstrate how to migrate a complete SQL Server (LocalDB/Express/Full) database to SQL Server Compact. The requirements are simply that the current user has read access to the SQL Server database. Then all tables, constraints, indexes and data will be moved to an empty SQL Compact database, all in just 6 lines of code:

using (IRepository serverRepository = new ServerDBRepository4(@"Data Source=.;Trusted_Connection=true;Initial Catalog=Chinook"))
{
    string fileName = Path.GetTempFileName();
    var generator = new Generator4(serverRepository, fileName);
    generator.ScriptDatabaseToFile(Scope.SchemaData);

    var helper = new SqlCeHelper4();
    var sqlCeConnectionString = @"Data Source=C:\temp\newdb.sdf";
    helper.CreateDatabase(sqlCeConnectionString);
                
    using (IRepository sqlCeRepository = new DB4Repository(sqlCeConnectionString))
    {
        sqlCeRepository.ExecuteSqlFile(fileName);
    }
}

The code requires the following using statements:

using ErikEJ.SqlCeScripting;
using System.IO;

The ServerDBRepository constructor simply requires any valid SQL Server ADO.NET connection string.

The ScriptDatabaseToFile creates a script file with all content of the database, and the ExecuteSqlFile method runs the script against a SQL Server database.

Notice the use of the SqlCeHelper4 class, which creates an empty database file.