Many of us as part of our daily job perform database deployments. It is imperative that organizations implement adequate change management procedures to ensure programmers do not make changes directly into the databases of the production environment without first properly justifying the value of this change and testing their change in an appropriate testing databases.
Most database admins and programmers, write scripts to make mass changes to the data in their databases. We are all guilty of writing scripts without proper testingĀ and transactions only to realize a second later after running the script we made a big bobo. Also in many organizations the team or person writing the deployment may not be the same person/team deploying the change, this script will also prove handy in these situations. In-light of this, I decidedĀ to share an example of a proper database deployment script. This script displays on screen updates as to what happening and also verifies expected changes before finally commiting the entire transaction.
------------------------------------------------------------------------- --KDaniel : 2015-02-06 --ISSUE: http://issuetracking/issue/1000 --Description: Used to update tax on sales ------------------------------------------------------------------------- -- Always specify database to use USE MAINDATABASE SET NOCOUNT ON --Set variables DECLARE @TransactionName VARCHAR(20), @cnt int, @oldTax int, @newTax int, @expectedChange int SET @oldTax = .05 SET @newTax = .09 SET @expectedChange = 56 SET @TransactionName = 'ThisIsAnUnneccessarilyLongTransactionNameJustForFunAndJustBecauseICan' --Alert user of what's about to happen RAISERROR( 'UPDATE TAX of Sales',0,1) WITH NOWAIT --Do the work BEGIN TRANSACTION @TransactionName RAISERROR( ' 1.0 UPDATING Sales Tax',0,1) WITH NOWAIT UPDATE Sales SET Sales.Tax = @newTax WHERE Sales.Tax = @oldTax AND Sales.Status = 'Open'; --Verify Data RAISERROR( ' 1.1 Verifying Sales updates',0,1) WITH NOWAIT PRINT ' ...Expected: ' + cast (@expectedChange AS varchar) SET @cnt = (SELECT count(*) FROM dbo.SALES WHERE Sales.Status = 'Open' and Sales.Tax = @newTax) PRINT ' ...Found: ' + cast (@cnt AS varchar) if(@cnt=@expectedChange) BEGIN COMMIT TRANSACTION @TransactionName END ELSE BEGIN -- If FAIL then display error, roll back transaction and STOP PRINT ' ...ERROR: Wrong number of SALES updates!' ROLLBACK TRANSACTION @TransactionName; RETURN END PRINT 'ALL DONE!'