Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

SQL Server Compact Code Snippet #19 : Migrate a SQL Server Database to SQL Compact

DZone's Guide to

SQL Server Compact Code Snippet #19 : Migrate a SQL Server Database to SQL Compact

Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

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.

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:

Published at DZone with permission of Erik Ejlskov Jensen, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}