Reverse changes from transaction log in SQL Server 2008 R2?

Question

We have a SQL Server 2008 R2 database that backs up transaction logs every now and then. Today there was a big error in the database caused at around 12am... I have transaction logs up to 8am and then 12am - 16pm - etc.

My question is: can I sort of reverse-merge those transaction logs into database, so that I return to the database state at 8am?

Or is my best chance to recover an older full backup and restore all transaction logs up to 8am?

The first option is preferable since full backup has been performed a bit of a while ago and I am afraid to f*ck things up restoring from there and applying trn logs. Am I falsely alarmed about that? Is it actually possible for anything bad to happen if going by that scenario (restoring the full backup and applying trn logs)?

Answer 1

The fact that you don’t create regular transaction log backups doesn’t affect the success of the recovery process. As long as your database is in the Full recovery model, the transactions are stored in the online transaction log and kept in it until a transaction log backup is made. If you make a transaction log backup later than usual, it only means that the online transaction log may grow and that the backup might be bigger. It will not cause any transaction history to be lost.

With a complete chain of transaction log backups back to 8 AM, you can successfully roll back the whole database to a point in time.

As for restoring the full backup and applying trn logs – nothing should go wrong, but it’s always recommended to test the scenario on a test server first, and not directly in production

To restore to a point in time:

1. In SSMS expand Databases
2. Right-click the database, select Tasks | Restore| Database
3. In the General tab, in the Backup sets the available backups will be listed. Click Timeline
4. Select Specific date and time, change the Time interval to show a wider time range, and move the slider to the time you want to roll back to

You can find more detailed instructions here: How to: Restore to a Point in Time (SQL Server Management Studio)

Keep in mind that this process will roll back all changes made to the database. If you want to roll back only specific changes (e.g. only recover some deleted data, or reverse wrong updates), I suggest a third party tool, such as ApexSQL Log

Reverting your SQL Server database back to a specific point in time

Restore a database to a point in time

Disclaimer: I work for ApexSQL as a Support Engineer