Promoting T-SQL scripts keeping control of changes

An often overlooked part of promoting DML scripts is knowing what the intention is going to be before one is run. For instance if you work in an environment with a high throughput of DML script promotions then there is a good chance that those scripts will be ran via change control and then the results fed back. However what happens when then result counts end up being incorrect (i.e different to what was anticipated)? A rollback occurs and we are then talking about possible availability issues.

This is why it is very important to ensure that any DML script clearly has expected change counts listed and that there is always a WHERE clause specified in the DML statement. If the DML operation is on all rows of a table then the WHERE clause should still be included specifying 1=1 to demonstrate that this is the intent.

Finally executing the script in an open ended BEGIN TRAN would be preferable so that a simple ROLLBACK can be issued if the counts are incorrect.

Another safety cautionary measure you can take in addition to backups could be creating a snapshot on the database prior to any COMMIT which would provide you other recovery options beyond restoring the database such as using the snapshot data to revert the changed data back. Or worst case a very quick revert of the entire snapshot.

This entry was posted in SQL, SQLServerPedia Syndication. Bookmark the permalink.