Options
SQL Backup Logs and fn_dump_dblog()
DBNewbie_2007
Posts: 47 Bronze 3
in SQL Backup
We had an incident earlier in the week where data was accidentally deleted from a database and we needed to track down when it happened and who caused it.
There are a few undocumented functions for reviewing the logs,
https://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/
one of which is fn_dump_dblog, which can review data in a log file. However, since the Backup Pro logs are compressed and have a .sqb extension, is there any way, other than the slow and time consuming "backup file converter" utility?
We were able to figure it out using a SQL monitor app (sorry, not SQL Monitor) to pin-point the time and user, then restore the log to a specific time to confirm it, but it would have been nice to use these undocumented function(s) instead.
There are a few undocumented functions for reviewing the logs,
https://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/
one of which is fn_dump_dblog, which can review data in a log file. However, since the Backup Pro logs are compressed and have a .sqb extension, is there any way, other than the slow and time consuming "backup file converter" utility?
We were able to figure it out using a SQL monitor app (sorry, not SQL Monitor) to pin-point the time and user, then restore the log to a specific time to confirm it, but it would have been nice to use these undocumented function(s) instead.
Tagged:
Answers
Excellent news that you were able to track down the point in time to recover.
The Restore Wizard with Redgate SQL Backup does not allow a user to specify a LSN for restore. Also there is no feature within the tool to make use of the fn_dump_dblog function. Sadly this will mean having to convert the backup files using the conversion tools.
I will feedback the comments in this forum article to the SQL Backup Product Manager and Development Team.
You may wish also to provide feedback from within the SQL Backup tool itself. SQL Backup GUI ->Help->Provide Feedback... and submit a feature suggestion.
If the feature suggestion receives a lot of votes, the development team may wish to add support for the fn_dump_dblog function in a future version of the tool.
SQL Backup does include an Object Level Recovery feature, did you try out this feature? If yes, did you encounter problems which prevented you from recovering the missing object and data?
Alternatively, using the SQL Server Comparison tools SQL Compare and SQL Data Compare to recover the missing object and data.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com