Trying to Resolve “Failed to deploy database project:..” when running Database Tests

On certain occasions when you try to run the database tests you will receive the dreaded “Failed to deploy database project:” message and then many of your tests will fail. We speculated that this was primarily due to the test suite deploying (publishing) the database project with some default settings such as “Block if data loss may occur”. I will give a little background on how the test suite was setup by default in the template, the work around we were using, how we are trying to configure it for the automated testing, and how to change the settings locally.

The Template

Our database project template has a Test Project created within it by default with a single test.
It is configured to automatically deploy to an in memory instance of the database and run the tests against that.

To see the databases for your in memory instance you can go to the “View” memu and show the “SQL Server Object Explorer”. In the object explorer you look under SQL Server and see the in memory instances, as well as any other instances you have connected to.

The Work Around

On occasion when running the database test suite you would get a “Failed to deploy database project:” which would cause one or more tests to fail. In order to correct the issue we would typically:
1. Browse into SQL Server Object Explorer
2. Select the in memory version we were running our tests against
3. Expand Databases
4. Delete the database we are running our tests against
5. Close and Re-open visual studio
6. Run the tests

Exhibit A: SQL Server Object Explorer showing the In memory server instance and the databases within it

Since we deleted the database. When we next rebuild the solution it recreates the database within the in-memory instance without issue and the test will run.

Configuration for Automated Testing

Since we are now starting to run the tests automatically on build / check in. It is much more of a pain to troubleshoot and correct the “Failed to deploy database project:”. Not to mention it occurs more frequently. To this end I was trying to figure out how can we specify the publish settings to use when the test suite executes.
While I found an article talking about adding settings to the test suite it did not answer the questions. However, I did find a nugget of information in a comment on a community forum that stated by turning off the “Automatically deploy the database project before unit tests are run” setting it resolves the issue. Probably because then you deploy / publish with the settings you want.
So for projects where we are setting up automated builds and testing we are modifying the configuration settings for the test project.
We are modifying the Test Configuration

We are unchecking the automatic deploy option (we are also specifying a physical machine to run the tests against – not shown).

Note: all the Automatically deploy option changes (along with the other options on the page) is an entry in the app.Config. It removes the database deployment section.

Running Tests Locally

When you are running tests locally you can use these same options above to specify how you want to run the tests. But please do not check in local modifications to the App.Config file for the test suite.
If you turn off the automatic deploy option. You will need to publish manually to the database you are testing against. Whether that database is in-memory or on an actual SQL Server.
Note: You can create a publish file for the in memory instance just like an actual instance.