SSDT: How to deploy to a specific LOCALDB file?

Question

In order to implement run and re-run my integration tests an indefinite number of times, I would like to make use of SSDT in VS2012 to publish to a LOCALDB file instance and run EF against that file during integration tests.

Few notes:

• We are using EF Database first
• We already have a SSDT project that we will use to deploy to a full database in our different environments
• I know that SSDT uses internally a LOCALDB instance to build/deploy/check for errors, so deploying to another custom localdb seems like it should make sense/be doable

Few questions:

1. Can I deploy to a specific LOCALDB file with SSDT?
2. Can I do this from the command line in order to automate it when I run integration tests?
3. Does this roughly seems like a good idea for integration tests with EF or is there a better way? ;-)

Thank you all

Answer 1

1. You can change the localdb for SSDT in the Debug options for the project. By default the debug options are set to the (localdb) instance and a DB name that corresponds to the project. You may have more success with Publish Profiles if you're trying to push the project changes to a DB server. You can use those with SQLPackage to push the changes along with a known set of options to a pre-defined server/database.

2. You can definitely push the changes through a command line. We're doing it with MSBuild to generate a dacpac file, then SQLPackage to publish the changes from the dacpac to the appropriate server/database.

3. Can't say for sure on this one. If it works for you, it's likely a good start. We do DB development outside of EF and try to do that first rather than trust EF to generate a good relational model.

I have a handful of blog posts on SSDT SQL Projects at http://schottsql.blogspot.com/search/label/SSDT that might be helpful.