How to script database validations?

Question

I want to create a smart way to execute a SQL script when rolling out DB changes into QA, UAT and production environments in order to smoke test database changes are correct after maintenance has been completed.

What's the best way to do this?

For now I am running multiple such SQL selects to do a visual compare of existing DB values to expected values. Here is my code:

select value as DBvalue, 'text' as PRODvalue -- could be 'text' or 'html'
from ConfigParams
where paramname = 'EmailBodyType'
and environment = 'prod'

Then the result will be a two-field result set that shows DB values next to expected values.

I have a listing of 10 or so SQL selects to validate param values are updated and correct after the DB work is done. Is there a better way?

Answer 1

You can create another table with expected records value and each time compare both tables records. for compare data between two table you can use SQL Data Compare of Redgate package.

Answer 2

My approach is as follows, assuming you have QA, staging, and production then the simple approach is to have a test suite you can run after making changes. Because the test suite usually involves running a bunch of inserts, updates, and/or deletes, you typically want these to be in transactions that always roll back, so the approach is:

1. Make db changes

2. Commit db changes (SQL Server does transactional DDL)

3. Run test transactions which insert/delete data and which roll back

4. If test scripts change look for a the problem and fix them right away. You should be able to ensure though that when you get to production there are no unexpected hitches.