I recently read Solomon Rutzky's article on DBFit (http://www.sqlservercentral.com/articles/Testing/64636/) and I've been working with it. Once I get past a few of the gotchas such as needing to use True\False in the DBFit code where SQL Server expects 0\1 for bit values it's a nice tool.
I was wondering if there are best practices for use of the tool or even for automated database testing in general? For example, the article talks about creating objects (tables, procs, etc) on the fly so they can be rolled back. That's a great concept but I'm running into problems implementing it. For example, I comment my procs so I can remember just what the heck I was doing but double dashes won't work when creating the proc from DBFit.
I'm considering maintaining the database by running the scripts using SQLCMD Mode from SSMS then handing over the DBFit html to the testers. That separates the database maintenance from the testing while still being able to build the database to a known state. The maintenance scripts and testing html would be stored in the same place in source control and checked out all at once when needed.
Hey Don. I am glad that you found the DbFit article helpful. :-)
First off, I would suggest that you check out the Google Group for DbFit at: http://groups.google.com/group/dbfit
Regarding comments in the SQL within an Execute block, I noted in the article that "you cannot use inline (--) comments in block SQL code, but you can use block comments (/* ... */)". Hopefully using block comments is acceptable.
Regarding the temporary objects noted in the article examples, that was just to show a simple and self-contained example. I do not recommend creating the objects you want to test within the DbFit tests themselves. The beauty of DbFit is that you don't need to alter your environment at all with the testing apparatus. This leaves a clean testing environment that mirrors production and provides a more trustworthy test. The only objects that should be created within a DbFit test are ones that are specifically for testing and are not part of your application.
I believe DbFit also provides a way to fully automate the testing so that you can have a CMD script that first calls SQLCMD to push the changes to your QA environment and then DbFit to test them.
We use DbFit a lot where I work and we set up a "Suite" that contains other "Suites" which contain the "Tests". This way we can logically separate the functionality to test by either doing a specific "Test", a specific "Suite", or all "Suites" by selecting the top-level "Suite". We also put our connection strings and other global variables in the Setup page so that they can be reused easily.
I hope this helps. Take care,
SQL# - https://SQLsharp.com/
Sql Quantum Lift - https://SqlQuantumLift.com/