Adventures of a wannabe geek!

Ranting within

Extracting a Sql Server Schema From Sql Server CE File

My current project at work sees me building an ASP.NET MVC3 project. When the project was being built initially, we had no idea of what the final schema of the database would look like. So we built the app using a fake dataset that we can in cache and read / wrote to that cache during the application. The decision was made that we would use Entity Framework 4 for our data access method and as we already had  business entities in place, we thought that Code First would be a good way to go since it was in RC.

We didnt get our DBAs involved as the schema wasn’t completely finished. So we decided to use Sql Server CE 4. I know what you are thinking  - MS Fan Boy, new technology freak – and you would be right. I’m happy with what we have chosen and its worked for us. We have now managed to get a database schema that we are happy with.

BUT we hit a flaw when we looked at how we migrate our Sql Server CE 4 database to Sql Server. I know there are tools available but I didn’t feel that I had a huge amount of control over these and I wanted one to be able to run from msbuild since I will (eventually) be automating the process.

Step up web deploy v2! I managed to find an article a while back that was tweeted by Sayed Hashim on the new features of web deploy v2. There is build in support for this exact purpose. So in order to run the process I opened a command line and change directory to the Web Deploy v2 folder (C:\Program Files (x86)\IIS\Microsoft Web Deploy V2). I can then run the command as follows:

msdeploy
-verb:sync
-source:dbFullSql=”Data Source=<path to .sdf>”,ScriptData=false,sqlCe=true
-dest:dbFullSql=”<output .sql path>”

This is pretty self explanatory but the important parts are to include sqlCe = true as this will make msdeploy know its a sql compact edition database. ScriptData = false will mean that only the schema will be output and no data will be included. If this is omitted then the schema will be exported as well as the data.

When I run this i get something similar to this screen on my console:

image 

Of course this command can be included in a batch file and be executed from TeamCity (post coming soon on this!) or manually run when necessary.

The output of the script can be opened and run against Sql Server – or if you wait for my next post – to be used to create sql change scripts that can be used from continuous database deployment.