CODEWITHSUNDEEP
.netsql-serverentity-frameworksql-server-ce
Bob Tway
Bob Tway

Reputation: 9603

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

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

Upvotes: 2

Views: 471

Answers (2)

ErikEJ
ErikEJ

Reputation: 41759

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

Upvotes: 1

Tony Abrams
Tony Abrams

Reputation: 4673

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.

Upvotes: 1

Related Questions