Over the last several months, I’ve had to write patch scripts for database updates. Writing the scripts tends to be easy and I came up with a fairly effective way to implement changes but writing the actual script got to be tedious. So, like any good programmer, I automated the task.

My Patch Implementation There are a lot of ways to write patch scripts, but one main concern is that you don’t overwrite procedures with old versions. For my environment, I came up with a fairly simple solution for procedures and user defined functions. Every update to a procedure or UDF creates a time stamped named version of itself. If I was generating a patched version of auditing.GeneralLogInsert today, I would check to see if auditing.[GeneralLogInsert-2011-06-22] exists. If it does, then I don’t proceed any further with that stored procedure. If it doesn’t, then I populate an nvarchar(max) variable with the create statement for the procedure/UDF, execute a rename on the existing object and then execute the create. It...

Read More »