Search code examples
mysqlsqlsqlyog

Reverse an sql statement?

I have an sql file with alot of create, alterings and modifies to a database. If I need to back out at some point (up to a day maybe) after executing the sql script, is there an easy way to do that? For example, is there any tool to read an sql script and produce a 'rollback' script from it?

I am using sqlyog aswell, in case there happens to be any such features built-in (I havn't found any)

Solution

  • No, sorry, there are many statements that cannot be reversed from looking at the SQL command.

    • DROP TABLE (what was in the table that dropped?)
    • UPDATE mytable SET timestamp = NOW() (what was the timestamp before?)
    • INSERT INTO mytable (id) VALUES (NULL) (assuming id is auto-increment, what row was created?)

    Many others...

    If you want to recover the database from before your day's worth of changes, take a backup before you begin changing it.

    You can also do point-in-time recovery using binary logs, to restore the database to any moment since your last backup.