Identification and Application of Retrospective Changes Done in Databases

Introduction

One of the common scenarios that we have come across during the course of every project life cycle is the need to identify the changes done retrospectively and revert them.
This usually happens during subsequent phases of the project when we start implementing enhancements to the main project artifacts.
Quite often the introduction of changes leads to breaking functionality in some of the dependent code which would force us to abort the changes. Then challenge would be in identifying the objects that got changed as a part of the breaking change to revert them. 

This blog post explains few approaches that can be applied for scenarios like this.

By Retrieving the Changes from Active Portion of Transaction Log

If the change done was very recent and you want to revert it then the best option is to make use of transaction log for the same. You can make use of functions like fn_dblog and fn_dump_dblog for reading the active portion of the log or from transaction log backup to get details of the changes to identify the change you want to revert and then apply the fix.

The approach to be followed for this is explained thoroughly in the below link

http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/

By Retrieving the Changes from an Earlier Backup

Backups are key to any system for providing recovery from any intermediate breakdown scenarios. Provided we have a proper backup chain in place we can very well use it to restore the db to earlier stage and then copy over the object to revert it to its earlier stage. For this to happen we should be aware of the approximate period during which a change was applied. Then we can apply last full backup available before the period as a separate copy and then copy the object from it to our original database. One thing we need to be careful in this approach is to make sure we also consider the data and object dependencies. If there are some dependent objects as well as data we need to ensure those are also reverted to same stage otherwise we will end up breaking the referential integrity of the system or breaking the dependent object.

As an example consider the case where we have a table where a new column was added at some point of time as a part of change. Later it was identified that the change causes a breaking impact and decision was to revert the changes to earlier state. Now suppose we have an earlier backup of the database before we applied the change we can take the backup and restore it as a new copy.
Imagine if table had a parent child relationship with another table which has column with FK constraint to this table. Also there's a stored procedure which uses the table internally for some CRUD operations.

If this is the case then simple copying the object from restored db copy to original db will not sufficient. This is because when we try to revert table to earlier state there will be lots of data which gets lost as the data was created later than the period to which we reverted the code. This would cause referential integrity issues and would create orphaned records in the dependent child tables. So we need to make sure we revert dependent objects also to the same state to keep them in sync.

Secondly we also need to revert the dependent stored procedure code to the same state as that of the table, Otherwise stored procedure will also break if it had any references to any columns which got added at a period ahead of the backup period to which we reverted.

By Reverting to Earlier Version Available in a Version Control System

Version Control Softwares are integral part of any project in configuration control. They provide a platform to maintain versioning of every project artifact and also allow parallel activity on a single artifact by multiple team members. The presence of a Version Control System can help us a lot in cases like above where we need to revert code to an earlier stage. To handle a scenario like above all we would need to do is to go for version control and check the history for the object. Based on the period when change was applied we can revert to the earlier version of the artifact from version control. If we follow a strict process to properly mark the versions while checking in to the version control then it would become even more easier as each version will specifically say the feature / changes contained in it from the label and it becomes easier to identify our required version and revert to it. This helps in the overall configuration control within the project.

Conclusion

As explained above you can apply any of the approaches for identifying the changes done retrospectively on objects in your database and revert them in case of any failures in the system