Database Deployment Scripts

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!'