One of the testing approaches I implemented in my last position was as follows:

1) all code development for SQL was fully scripted in independent files in version control.

2) a single deployment script was written that could run against development, testing or QA. This reproduced the entire database structure along with lookup table data such as province/state, country, etc.

3) a second script was maintained with a small set of test data that covered most data conditions (new ones were added as we uncovered each new or unique situation).

This allowed us to run a full unit test at the SQL level, the business unit level or the UI level with a known data set and provided regression testing as we made changes. Of course, we could also run the tests against a full copy of the production data to confirm functionality against larger data sets.