How to re-deploy, re-create database on each test run

Question

Currently I'm using Visual Studio 2012 RC and SQL Server 2012 RTM.

I'd like to know how to re-deploy/re-create a test database for each test run.

Keep in mind I've a SQL Server database project for the database using Visual Studio 2012's template.

Actually I'm not very sure about an idea I got in my mind, but .testsettings file has Setup and cleanup scripts. Is this the way to go? For example, a PowerShell script reading the database project generated script and executing it against the database?

I guess there're better ways of doing that and it should be an out-of-the-box solution but I ignore it and Google doesn't help me in finding the right solution.

Answer 1

As mentioned you'll probably want to use the VS 2012 .Local.testsettings > Setup and Cleanup scripts to create / tear down you SQL Server database.

For the script you may want to use powershell with a .dacpac (rather than just a T-SQL script), since you are using a SSDT project. Here's a link to some example code - in particular you may want to take a look at the 'Deploy-Dac' command.

If you are unfamiliar with .dacpacs as the (build) output of SSDT-created database projects, take a look at this reference link.

Answer 2

Edit: Although this doesn't answer the question in a plain SQL Server way, an easy Entity Framework approach would be the following: I found that I could create and destroy my database every time correctly by using the DbContext.Database.CreateIfNotExists() and DbContext.Database.Delete() methods in my setup and cleanup phases of my tests.

Answer 3

The fastest solution, while a bit of a hack, is really straightforward. You can set the DB Projects properties under the debugging tab to "always re-created DB". Then test in two clicks, do a debug/build, then run all tests. You should get a freshly built DB on localDB for you tests to be ran against. You can also change the target for the debugging DB (again the DB projects properties) to whatever you want, so you can deploy to a .dacpac, or to an existing SQL DB or wherever. It means testing in two steps, and if your build is long, it may be annoying, but it works. Otherwise, I believe scripting is your only option.