One of my recent tasks, well before my month off anyway, was to upgrade a number of similar SQL Server 2005 databases to a new structure. The databases had all started off the same, and were going to end up the same, but had been subjected to a differing number of bug fixes and enhancements along the way.

If I was updating just one database I would have used the excellent Redgate SQL Compare (of which more in a future post) but as I had to update many then this would not have been the most reliable way.

Instead, I wrote defensive SQL, that is SQL which checks for impact before doing it. So, before adding a column for instance, it checks to see if that column exists, and only attempts to add it if it doesn't - thus preventing errors. The whole update script was wrapped in a Transaction, and by making use use of a variable @bError of type BIT to track any errors, the last statement could COMMIT or ROLLBACK the transaction as appropriate.

So, for example, to add a new column to a table, here is the script I used:
IF @bError = 0
BEGIN
  IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'MyTable' AND Column_Name = 'MyNewColumn')
  BEGIN

    ALTER TABLE MyTable ADD MyNewColumn INT NOT NULL

    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'MyTable' AND Column_Name = 'MyNewColumn')
    BEGIN
      PRINT 'Error Adding column ColumnName to table MyTable'
      SET @bError = 1
    END
    ELSE
    BEGIN
      PRINT 'Added column ColumnName to table MyTable'
    END
  END
END

By checking for the column's existance after applying the change, I could determine if an error had occurred, and set the @bError flag accordingly. I used the Print statements to produce a log of exactly what stages had been carried out on each database, using the function DB_NAME() to output the name of the current database at the beginning of the log.