Orchard CMS — 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 extract 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:

From:

DataProvider: SqlCe
DataConnectionString: null

To:

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

Migration is Completed!!!

Originally published at https://orchardtricks.dotnest.com.