Migrate a SQL Server Compact database to SQL Server using Web Deploy (MSdeploy)

You can use the latest version of the msdeploy command line utility, which is included with WebMatrix, to migrate a SQL Server Compct database to SQL Server, or simply generate a script (in SQL Server T-SQL dialect) of the entire database, both schema and data.

On my PC, the msdeply.exe version 2.0 is located at C:\Program Files (x86)\IIS\Microsoft Web Deploy V2

Below are some sample usages involving a SQL Server Compact 4 source database:

msdeploy -verb:sync
-source:dbFullSql="Data Source=C:\data\sqlce\test\nw40.sdf",sqlCe=true 
-dest:dbFullSql="c:\data\scriptnw.sql"

This will create a script of the entire database in SQL Server T-SQL dialect, both with schema and data.

msdeploy -verb:sync -source:dbFullSql="Data Source=C:\data\sqlce\test\nw40.sdf",sqlCe=true,scriptdata=False -dest:dbFullSql="c:\data\scriptnw.sql"

This will create a script of the entire database in SQL Server T-SQL dialect, schema only.

msdeploy -verb:sync -source:dbFullSql="Data Source=C:\data\sqlce\test\nw40.sdf",sqlCe=true,NoCollation=true,SchemaQualify=false -dest:dbFullSql="c:\data\scriptnw.sql"

This will create a script of the entire database in SQL Server Compact compatible T-SQL dialect, both with schema and data.

Unfortunately, the script generated on my machine contains the following statement, which is not valid T-SQL:

INSERT INTO [Order Details]([Order ID],[Product ID],[Unit Price],[Quantity],[Discount]) VALUES (10001,25,9,8,30,0,15)

7 values to 5 columns – it is a localization issue, that I have reported  here, it can be fixed by changing the decimal point in regional settings.

You can read more about the supported scripting options here.

You can read more about the dbFullSql provider and it’s support for SQL Server Compact 4.0 here.