SQL Server Compact Code Snippet of the Week #9 : migrate a SQL Compact database to SQL Server

This week’s snippet directly follows the one from previous week, demonstrating my SQL Server Compact scripting API once again.

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

using (IRepository ceRepository = new DB4Repository(@"Data Source=C:\Data\SQLCE\Test\nw40.sdf"))
{
    string fileName = Path.GetTempFileName();
    var generator = new Generator4(ceRepository, fileName);
    generator.ScriptDatabaseToFile(Scope.SchemaData);
    using (IRepository serverRepository = new ServerDBRepository4("Data Source=.;Trusted_Connection=true;Initial Catalog=Test"))
    {
        serverRepository.ExecuteSqlFile(fileName);
    }
}

The code requires the following using statements:

using ErikEJ.SqlCeScripting;
using System;
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.