Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
It's not rude, it just shows that he's still not familiar with how the system works. However, I agree that cross posting is needless. I'm removing the previous comment on that regard. – jcolebrand Jun 7 '12 at 19:34
sorry all, ill revert and wait for answers here – kacalapy Jun 7 '12 at 19:36
Sounds like you just need to self-implement some sort of a sql test runner on your own box – jcolebrand Jun 7 '12 at 19:37

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.

share|improve this answer

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.