Post reply

Tables versioning - DML and DDL tracking changes and roll back to previous version

  • June 9, 2018 at 1:58 pm

    #1993487

    Anyone?

    Jacek

  • June 11, 2018 at 7:52 am

    #1993547

    Version control software would handle this much better. High-volume transactional tables will be problematic for you here.

    For slowly changing data tables, you could maybe use audit tables, where any INSERT/UPDATE/DELETE gets logged to a mirrored table that also captured the time of change and type of change. Then you could set triggers to capture that info and log into the audit table (triggers are maybe not the best option here, research alternatives and make sure you make the best choice for you).

    You'd also have to think about "previous version" and how it would impact things if you rolled Table A back but nothing else, and how you could be impacting the way data is tied together throughout the system inadvertently (in other words, are you going to analyze when data was updated in all tables if you roll TableA back to point in time, and roll all back to that point in time? Or just tell the user to deal with it?). This might have an impact on data that isn't a foreign key relationship either, and would just end up being orphaned in other tables accidentally.

    Also doesn't handle truncate/drop of tables, I've seen a couple suggestions for that.
    http://jackworthen.com/2018/03/19/creating-a-log-table-to-track-changes-to-database-objects-in-sql-server/
    https://www.mssqltips.com/sqlservertip/2583/grant-truncate-table-permissions-in-sql-server-without-alter-table/

    I'm restricting rights to our database through security groups, then also adding unittests for whether objects exist, so that if one fails I know to go fix it.

    hope those thoughts help.

    Jon

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • June 12, 2018 at 1:23 am

    #1993633

    Hi Jon,

    thank you for your reply.

    I have idea how to track all changes. 
    I want to create temporary database and create there copies of tables as version. 
    And if user wants to go back to previous version he can go and from dump file restore databse into temporary and see tables for exact version. 

    And working on new version using GIT solution for that.
    Best,
    Jacek

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply