Monday, February 27, 2012

Migrating databases between SQL Server and SQL Server Compact

In this post, I will try to give an overview of the free tools available for developers to move databases from SQL Server to SQL Server Compact and vice versa. I will also show how you can do this with the SQL Server Compact Toolbox (add-in and standalone editions).

Moving databases from SQL Server Compact to SQL Server

This can be useful for situations where you already have developed an application that depends on SQL Server Compact, and would like the increased power of SQL Server or would like to use some feature, that is not available on SQL Server Compact. I have an informal comparison of the two products here. Microsoft offers a GUI based tool and a command line tool to do this: WebMatrix and MsDeploy. You can also use the ExportSqlCe command line tool or the SQL Server Compact Toolbox to do this. To use the ExportSqlCE (or ExportSqlCE40) command line, use a command similar to:

ExportSQLCE.exe "Data Source=D:\Northwind.sdf;" Northwind.sql

The resulting script file (Northwind.sql) can the be run against a SQL Server database, using for example the SQL Server sqlcmd command line utility:

sqlcmd -S mySQLServer –d NorthWindSQL -i C:\Northwind.sql

To use the SQL Server Compact Toolbox:

Connect the Toolbox to the database file that you want to move to SQL Server:

image

Right click the database connection, and select to script Schema and Data:

image

Optionally, select which tables to script and click OK:

image

Enter the filename for the script, default extension is .sqlce:

image

Click OK to the confirmation message:

image

You can now open the generated script in Management Studio and execute it against a SQL Server database, or run it with sqlcmd as described above.

Moving databases from SQL Server to SQL Server Compact

Microsoft offers no tools for doing this “downsizing” of a SQL Server database to SQL Server Compact, and of course not all objects in a SQL Server database CAN be downsized, as only tables exists in a SQL Server Compact database, so no stored procedures, views, triggers, users, schema and so on. I have blogged about how this can be done from the command line, and you can also do this with the SQL Server Compact Toolbox (of course):

From the root node, select Script Server Data and Schema:

image

Follow a procedure like the one above, but connecting to a SQL Server database instead.

The export process will convert the SQL Server data types to a matching SQL Server Compact data type, for example varchar(50) becomes nvarchar(50) and so on. Any unsupported data types will be ignored, this includes for example computed columns and sql_variant. The new date types in SQL Server 2008+, like date, time, datetime2 will be converted to nvarchar based data types, as only datetime is supported in SQL Server Compact. A full list of the SQL Server Compact data types is available here.

31 comments:

Alex said...

I am trying to port my sql server db to my sql server ce db. My SQL server instance is hosted on a shared hosting server at seaweed.arvixe.com so I don't have the permissions I would have if I owned the server. When I put in all my connection data and do test connection it is successful, but when I click OK I get this error: http://www.codetunnel.com/content/images/sqlceimport.jpg

Alex said...

I am trying to port my sql server db to my sql server ce db. My SQL server instance is hosted on a shared hosting server at seaweed.arvixe.com so I don't have the permissions I would have if I owned the server. When I put in all my connection data and do test connection it is successful, but when I click OK I get this error: http://www.codetunnel.com/content/images/sqlceimport.jpg

ErikEJ said...

It is a login error, you will have to discuss with arvixe - more info here: http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

Unknown said...

Hello Erik, i'm trying to use your interface to get my sql server 2008 table (called monsters) to sql CE 4.0 using your tool, but I cant do it.

I'm trying in the sql server compact toolbox to generate the scrip for data and schema but after testing the connexion I'm asked to save the script somewhere. After giving it a name, and clicking ok, I get this pop-up :

Message : Incorrect syntax near 'monsters'.
Source : .Net SqlClient Data provider
Number: 102

think you can figure out whats wrong ? or what could cause your script to fail ?

been struggling with this the whole day and cant seem to find why

ErikEJ said...

Thierry: Please create an issue at the Codeplex issue tracker, and provide a CREATE TABLE script for your server table (assume you are only scripting this single table?) And if possible screenshots of your process.

Unknown said...

thanks to take the time to help ! this tool could really save alot of time !

I created an issue in codeplex as you asked. Joined screenshot of the steps I took, and added the script of the create table I generate in ssms.

And yes, right now it was only this table. If I can get this to work I'll probably use it on other table for this project as well as a couple of other stuff I have.

Unknown said...

I found one of the problems, it seems your script cant handle float type as in your query every attribute is surrounded by single quotes, I manually create the table and move all float to nvarchar, and now I can at least use the import csv option

ErikEJ said...

Thierry: I think it is an issue with your unusualtable name, so please try to rename it as suggested by me on CodePlex

Noname said...

Hi Erik

May I ask your advice on something? I am a novice SQL Compact 4.0 Toolbox User.
I have a .sdf file, for which I would like to create the .sql script through command prompt. I need you to confirm the following:
After downloading the ExportSQLCE40.exe tool. I simply have to enter : ExportSQLCE40.exe "Data Source="Path.sdf";" name.sql

Upon this entry, we are encountering the following error:

C:\test>ExportSqlCE40.exe "C:\test\Sample File.sdf;" adsf.sql
Error: System.ArgumentException: Format of the initialization string does not co
nform to specification starting at index 0.
at System.Data.Common.DbConnectionOptions.GetKeyValuePair(String connectionSt
ring, Int32 currentPosition, StringBuilder buffer, Boolean useOdbcRules, String&
keyname, String& keyvalue)
at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable,
String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstK
ey)
at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hash
table synonyms, Boolean useOdbcRules)
at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String v
alue)
at System.Data.SqlServerCe.SqlCeConnectionStringBuilder..ctor(String connecti
onString)
at System.Data.SqlServerCe.SqlCeConnection.set_ConnectionString(String value)

at System.Data.SqlServerCe.SqlCeConnection..ctor(String connectionString)
at ErikEJ.SqlCeScripting.DBRepository..ctor(String connectionString)
at ExportSqlCE.Program.Main(String[] args)


Any ideas?

Thanks, your help is much appreciated.

ErikEJ said...

The command line should be:
ExportSqlCE40.exe "Data Source=C:\test\SampleFile.sdf;" adsf.sql

Noname said...

Thank you very much :)

Tanito said...

Hi,
can you help with this please? May be you know this one.
I'm trying to export a SQL Server to SQL Server Compact 4.0 and I'm getting this error:

Application: SqlCe40Toolbox.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception.
Exception Info: System.IO.FileNotFoundException
Stack:
at ErikEJ.SqlCeScripting.SqlCeHelper4.FormatError(System.Exception)
at ErikEJ.SqlCeToolbox.Helpers.DataConnectionHelper.ShowErrors(System.Exception)
at ErikEJ.SqlCeToolbox.Commands.DatabaseMenuCommandsHandler.ExportServerDatabaseTo40(System.Object, System.Windows.Input.ExecutedRoutedEventArgs)
at System.Windows.Input.CommandBinding.OnExecuted(System.Object, System.Windows.Input.ExecutedRoutedEventArgs)
...

ErikEJ said...

Tanito: You must have the SQLCE 4.0 runtime installed.

Nenad said...

Hi *.
I'm trying to export sql express db to sql ce, but I receive same message as Tanito. I have sqlce 4.0 runtime installed (x64) version.
Do you have any idea?
Thank you in advance.
Nenad

ErikEJ said...

Nenad: Suggest you post an issue with more information on the Codeplex issue tracker

Mohammed said...

You can now open the generated script in Management Studio and execute it against a SQL Server database, or run it with sqlcmd as described above.

____
i am not under stand i am want to convert my sql compact 4 to sql express database "becouse i failed to share .sdf file on local network and alot of people said to me it is not a proved that " so i am already convert sql compact file to .sqlce
and i want now to totaly convert it to sql express 2014 studio what should i do i tried but useless

ErikEJ said...

Mohamed: Open the .sqlce script in Management Studio, connect to your new SQL Server Express db, and run the script.

Mohammed said...

thank u about your reapply but
http://im53.gulfup.com/fMhvt2.bmp
see please this image this after i opened the script on sql server what is the problem??

ErikEJ said...

You need to connect to the database, or create a new one.

Mohammed said...

Hi ErikEJ
i am very grade about your help to me
i make it but make some diffirent first i make .sql script not .sqlce but it insert only tables with out dataim master data and i want to ask here why in master data ??
and after that i copy what inside .sqlce and but it in the .sql and execute
and all data convert success and i make .mdf data separated from the server and try to contact it to vb studio but it give me this error
http://im51.gulfup.com/jljhcw.bmp
so why ?? and please i want from u the final answer of can i use .sdf in network by but it on the server and share it to users if yes How by Detail and clarify the steps"i have sql compact 4 Now"?? if no can i get sure that sql data file .mdf will get me what i need in the network ???
sorry about all this question but i am in a very big Problem to make my program work on local net work as fast as i can

ErikEJ said...

Mohamed: Please use the MSDN forum for lengthy support issues. Master is a system database, and is not for user data

ErikEJ said...

Mohamed: Please use the MSDN forum for lengthy support issues. Master is a system database, and is not for user data

Nanda said...

Hi,

I want to migrate my SQL CE DB to SQL 2012 DB. Is there any SDK compatibl;e to be used in .Net?

ErikEJ said...

Nanda: Yes, of course : http://erikej.blogspot.dk/2013/03/sql-server-compact-code-snippet-of-week_8.html

Unknown said...

Just want to say thank you for your blog posts and tools. You are also always willing to help.
I really appreciate it.

Unknown said...

Thank you for all your blog posts and your invaluable tools! It really makes dev'ing a breeze.

ErikEJ said...

Sanvir: Thanks: If you use my tools, I would be very grateful for a rating or review here:

https://visualstudiogallery.msdn.microsoft.com/0e313dfd-be80-4afb-b5e9-6e74d369f7a1/view/Reviews

Unknown said...

Hi sir
i have Compact CE database file (.sdf) i just want to connect it using windows forms application.i used your toolbox to working on it like performing queries migrating from CE to SQL Server. but now i want to directly connect to .sdf file fetching some data from it and insert that data into the SqlServer tables. i follow all the processes like Connection, Command, Dataadapter and then filling data into dataset, but when i reach the dataset fill i found that there is no data in dataset. do you have any idea.....
Thanx and regards

ErikEJ said...

Shrikant: I assume you are doing it wrong - I have some working code here: https://github.com/ErikEJ/SqlCeToolbox/blob/master/src/GUI/SqlCe35Toolbox/WinForms/ResultsetGrid.cs

Unknown said...

Hi Erik,

I need to copy the data from existing SQLServer Db files to .sdf files and i developed a solution for that. Its working great. Now i need to mark the database once it gets copied so that i can prevent copying it again when we run the solution in the same system in future.

Please advise.

Thanks in advance!!

ErikEJ said...

Sandeep: You could make the sdf file read-only, or you could create a table in the sdf file with a single row and a bit column, and set it to true