Migrate from SQL Compact Edition to SQL Server

Migrate from SQL Compact Edition to SQL Server

The installation of Orchard CMS is really fast and you do not even need SQL Server because Orchard CMS can run with SQL Server Compact. SQL Compact is great for development and small websites e.g. Landing Pages. In this post I will show how to migrate from SQL Compact Edition to SQL Server in 5 simple steps.

  1. Download the ExportSqlCE40.exe from here and copy the ExportSqlCE40.exe to SDF location e.g. \src\Orchard.Web\App_Data\Sites\Default
  2. Open CMD in the SDF folder (Shift + Right Click, Open command window here) and type this command: 
    ExportSqlCE40.exe "Data Source=LocationOfTheSDF" database.sql 

    LocationOfTheSDF is the location of your SDF e.g. C:\Websites\TestWebsite\src\Orchard.Web\App_Data\Sites\Default\Orchard.sdf

  3. Open SQL Server Management Studio, right click in Databases and choose New Database


    Give the name you want for Database name and click ok

  4. Double click in database.sql file. This will open SQL Server Management Studio. Choose on the left the database you just created and click Execute


  5. Change to Settings.txt (\src\Orchard.Web\App_Data\Sites\Default\Settings.txt) the following:

    DataProvider: SqlCe
    DataConnectionString: null

    DataProvider: SqlServer
    Data Source=YourServerName; InitialCatalog=YourDatabaseName; Persist Security Info=True; User ID=username; Password=password

    Migration is Completed!!!