You’d want to have a build environment where you have control of some test version of your DBs as opposed to an environment that uses production DBs. If you need to switch between test DBs and production DBs you can write
a script that does restore DB from some backups. Unless you have really big DBs, switching from one set of backups to another will be relatively fast.
In many cases it is useful if as part of your build process you include the process that initializes your DBs. In that case the test automation can be integrated as one of the final steps of the build process and you can work
on top of a clean and predictable set of DBs. In some cases you may also want to create a back-up of the clean DB (this typically takes a few seconds) and restore it at different moments during the test process.
Having this type of environment where you have a clean DB, you will have to set the initial data as part of your test. Typically a test will not do a lot of things. It will be focused on a limited aspect and it will have the
- The test fixture. The part where you set up the data context for your test. Can be INSERTS / UPDATES or even better calls to sprocs.
- Exercise the unit under test. Here you invoke the sprocs / functions, views, etc that are the subject of your test.
- Read and verify the expected result.
My point here is that 1. (the test fixture) is part of the test. Normally you should not rely on the right context being already in place. Rather you should start from a clean or at least predictable state and set-up the right
context in the test.
There are plenty of reason why in a test environment you really want to avoid having to work with a production DB: Complexity that is out of your control, performance, the inability of scoping tests to only well defined scenarios,
and so on.