Currently, our application uses SQL Server CE as our database. We decided to move forward (correct me if I am wrong) to SQL Server 2012 Express for scale and performance reasons.
My question: we need to migrate the database from SQL Server CE to 2012 Express with all SB attributes (schema, indexes, foreign keys, Data ...)
This migration should be performed in an upgrade process which handle in C# code.
How can we do it?
You can use my scripting api to script the SQL CE database to a file, and run the script against a SQL Server database (the sample expects an empty server database, and that the required SQL Server security is in place)
using ErikEJ.SqlCeScripting;
using System;
using System.IO;
class Program
{
static void Main(string[] args)
{
try
{
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);
Console.WriteLine("Database exported");
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
Console.ReadKey();
}
}
