If applying the SQL generated by DBTran.exe to a database (where all the triggers exist), is it better to:
The situation is practising a plan to revert a database file version update, if it turned out to be necessary. Some triggers refer to system variables that aren't there at the re-run time.
Update - Thank you everyone for your input.
Option 1 produced the desired results. John's comment about that essentially being how the recovery process does it being the key point. Since that isn't the default behaviour of dbtran, I wonder if the docs could be clarified?
What do you mean that the later logs would be from a later SQLA version? Are they from a different database? If not, a new SQLA server generally tries to keep new logs compatible with the database that they are for. Therefore, you could use a newer SQLA server to apply those logs to the older database. There is the caveat that there is only so much version mix & match testing that can be done. Also, downgrading the server version isn't often done so there is probably not much field experience with it either.
As for option #1, that is essentially what recovery does: it replays all operations including the ones logged by triggers but does not fire triggers.
answered 24 Sep '13, 09:18
The statement "Some triggers refer to system variables that aren't there at the re-run time." pretty much closes and locks Door Number 2... the triggers will fail to run properly.
Another problem with alternative 2 would be references to changing special values like CURRENT TIMESTAMP... presumably you want to apply exactly the same changes to the database that were originally applied, just like database recovery does.
Personally, I prefer Door Number 3: Restore from a backup.
answered 24 Sep '13, 08:21