How to redeploy, recreate the database on each test run

Question

I am currently using Visual Studio 2012 RC and SQL Server 2012 RTM.

I would like to know how to redeploy / recreate the test database for each test run.

Keep in mind that I have a SQL Server database project for a database using the Visual Studio 2012 template.

Actually, I'm not very sure about the idea that I understood, but .testsettings has installation and cleaning scripts . Is that the way? For example, a PowerShell script reading a database project, a generated script and executing it against a database?

I think there are better ways to do this, and it should be a ready-made solution, but I ignore it, and Google does not help me find the right solution.

Answer 1

As already mentioned, you probably want to use the VS 2012.Local.testsettings> Setup and Cleanup scripts to create / stall a SQL Server database.

For a script, you can use powershell with .dacpac (and not just a T-SQL script) since you are using an SSDT project. Here is a link to some sample code - in particular, you can take a look at the "Deploy-Dac" command.

If you are not familiar with .dacpacs as the (assembly) output of database projects created by SSDT, look at the link.

Answer 2

The quickest solution, while a bit of hacking, is really simple. You can configure the properties of database projects on the debug tab of "always created database". Then check for two clicks, debug / build, then run all the tests. You should get the database you just created on localDB for you to test the tests. You can also change the target for the debug database (again, the properties of the database projects) to whatever you want so that you can deploy it to .dacpac or an existing SQL database or anywhere else. This means testing in two steps, and if your build is long, it can be annoying, but it works. Otherwise, I think the script is your only option.

Answer 3

Edit: although this does not answer the question in a simple SQL Server way, a simple Entity Framework approach would be the following: I found that I can create and destroy my database every time correctly using DbContext.Database. CreateIfNotExists () and DbContext.Database.Delete () in the steps of my setup and cleanup of my tests.