Backup complex SQL Server 2008 Data into SQL Server Compact Edition (sqlce)

Question

I'm trying to build a .net class to make an emergency backup of some important data in sql server compact edition. It's not a massive amount of data but it does have a fairly complex data structure (30-odd tables with significant cross-referencing). We use Entity Framework to do most of our data work.

Documentation on working with sqlce and .net seems to be a bit thin on the ground and currently I'm struggling a bit. I've created the database file (.sdf) easily enough but ideally I'd like to be able to specify where that file lives rather than have it created in the default (C:\Program Files (x86)\Common Files\microsoft shared\DevServer\10.0 in my case). Is this possible, and if so how?

More importantly I'm concerned that I'm going to have to manually re-create all 30+ tables which is not only absurdly labour intensive but not very scalable either. Is there a way - preferably using entity framework - to grab the subset of data that I need from our central database and insert it into a clean SqlCE database, having it create the necessary tables automatically?

Cheers, Matt

can't you use sql replication, with you sql ce db as subscriber ? — Steve B
– Steve B, Commented Feb 17, 2011 at 10:39
Maybe the Microsoft Sync Framework could be a solution for you?? — marc_s
– marc_s, Commented Feb 17, 2011 at 11:14

Tony Abrams · Accepted Answer · 2011-02-17 12:36:54Z

1

1) You can generate a script using Entity Framework, but this might only be available in VS 2010. Just right click in the EF model and select Generate Database from Model. Then you can connect to a database and run the script to generate the tables. Be aware that this will also change your associations in EF for that model and 'hook' it to the new database.

1) a] You could always create a script in SQL Server using the existing database. Just script out the database and select all the tables, constraints, triggers, etc.

2) You might want to check into what data-types are available in SQL CE. If I am remembering correctly, then some data-types do not exist in CE.

answered Feb 17, 2011 at 12:36

Tony Abrams

4,6613 gold badges27 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bob Tway Over a year ago

Actually, you can't "just run the script" because EF generates a bunch of SQL commands that CE doesn't understand, to say nothing of the fact that it doesn't like certain data types or autogenerated keys. But as it turns out a bit of RegExp sorts that out nicely, so have a tick, thank you.

Tony Abrams Over a year ago

Yeah I figured something like that would happen. :) Just never got around to updating ... my bad.

ErikEJ · Accepted Answer · 2011-02-19 15:48:13Z

1

You can use my command line utility Export2sqlce to script the entire Server database in SQL Compact compatible SQL script: http://exportsqlce.codeplex.com

answered Feb 19, 2011 at 15:48

ErikEJ

42.1k5 gold badges82 silver badges120 bronze badges

Collectives™ on Stack Overflow

Backup complex SQL Server 2008 Data into SQL Server Compact Edition (sqlce)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related