When setting up an Orchard website you’re given a choice to use a ’normal’ SQL database, or SQL Compact. When developing new modules I often choose for the SQL Compact option. I choose this option, because it’s very easy to backup and restore the database file. If you mess something up, you’re fairly safe.

You can of course backup and restore normal SQL databases, but this takes a bit more effort compared to copy-pasting a database file.

I hardly ever use a SQL Compact database in other environments as development. This means whenever I need a dump from the Testing environment to Development I’ll get a normal database backup file, or a bacpac export file. These files can only be restored to normal SQL databases, so that’s a small problem.

Apparently no one at the SQL-team has considered that someone might actually want to ‘downgrade’ their database to SQL Compact.

When searching the web you’ll find a lot of pages people asking the question “How can a SQL database be migrated to SQL Compact”. As it happens, migrating to SQL Compact is rather easy, once you’ve found the appropriate posts on Stack Overflow. @ErikEJ has written 2 applications which make it really easy to do such a migration.

First there’s the ExportSqlCe application. This application is able to generate scripts of the database you wish to migrate which can be executed on a SQL Compact database.

The second application is SqlCeCmd application. This application is able to do a variety of actions on a SQL Compact database, like the creation of such a file and running SQL scripts.

Erik describes all steps necessary to do a migration from a SQL to a SQL Compact database on his blog. All you need to do is execute the following commands (with your own settings of course)

Export2sqlce "Data Source=(local);Initial Catalog=AdventureworksLT;Integrated Security=True" AW.sqlce
sqlcecmd40 -d "Data Source=C:\aw.sdf" -e create
sqlcecmd40 -d "Data Source=C:\aw.sdf" -i aw.sqlce > log.txt

Let’s go through this step by step.

The first command creates an export script of the database you wish to migrate. The first parameter of Export2SqlCe is the connectionstring to the database you wish to migrate, the second parameter is the output file where the script will be saved. This script will be imported later on.

The second command will create a new SQL Compact database file, no need to explain the parameters.

The third command will use the newly created database file and run the SQL script in it.

After having successfully executed these commands, you’ll have a new SQL Compact database containing all information of the selected SQL database. All of this will only work if the database is capable of being migrated of course. SQL Compact is not able to do everything a real SQL database can.

Looing at the above steps, there’s a lot of typing and repetition involved. Because I’m a developer and have to save my keystrokes, I’ve created a PowerShell script which will minimize the amount of typing you need to do when migrating. As you can see, executing this script requires you to have both executables (Export2SqlCe & SqlCeCmd40) to be in the same directory as the script, otherwise it won’t work. Also, I’m not a PowerShell guru, so if you have any improvements for the script, let me know.

param(
	[string]$connectionString = "",
	[string]$databaseName = ""
)
$sqlCEScriptFile = ".\CreateSQLCEDatabaseScript.sqlce"
function CreateSQLScript
{
	try
	{
		IF (Test-Path $sqlCEScriptFile){
			Write-Host "Removing old script file"
			Remove-Item $sqlCEScriptFile
		}
		Write-Host "Starting to create an export of the specified database in the connectionstring parameter."
		& .\export2sqlce.exe "$connectionString" $sqlCEScriptFile
		Write-Host "Finished exporting the script"
	}
	catch
	{
		Write-Host "Exporting the script failed!"
		throw
	}
}
function CreateDatabaseFile
{
	try
	{
		IF (Test-Path ".\$databaseName"){
			Write-Host "Removing old database file"
			Remove-Item ".\$databaseName"
		}
		Write-Host "Creating a new Compact Database file"
		& .\sqlcecmd40.exe -d "Data Source=.\$databaseName" -e create
		Write-Host "Finished creating the Compact Database file"
	}
	catch
	{
		Write-Host "Failed creating the database file!"
		throw
	}
}
function FillUpTheDatabase
{
	try
	{
		Write-Host "Importing the script into the database"
		& .\sqlcecmd40.exe -d "Data Source=.\$databaseName" -i $sqlCEScriptFile > .\log.txt
		Write-Host "Finished importing the script"
	}
	catch
	{
		Write-Host "Failed importing the script to the database!"
		throw
	}
}
try
{
	CreateSQLScript
	CreateDatabaseFile
	FillUpTheDatabase
}
catch
{
	Write-Host "Failed creating a good export!"
}

Just specify the connectionstring and name of the SQL Compact database file when running the script and you’re good to go.

Hope this will help you migrating your SQL databases to SQL Compact.