1

We have a large database we restore daily from a single t-log backup from a remote supplier. We are looking to move to 15 minute t-logs, but applying these to the current database on prem it is then very hard to see just what has changed, to update the downstream warehouse tables. A hypothetical solution would be if it were possible to take a t-log as it came in, restore it to a new database, and then be able to compare tables in that with the tables in the full database to then create some sort of UNION view that reflects the current state.

But I am 99.9% sure you cannot restore a t-log if you have not restored a full backup before, hence I am looking at ApexSQL Log to read the t-logs instead.

But if I'm wrong, please let me know! I'd love to be able to restore a single t-log to a new database!

Improve this question
2
  • Welcome to DBA.SE. I'm having issues understanding the first sentence in your question: We have a large database we restore daily from a single t-log backup from a remote supplier How does that work? When I restore a SQL Server database, I have to restore a FULL backup and then a TLOG backup. It's hard to understand how restoring a single TLOG bakcup (in your case) would be any different than restoring a single TLOG backup from a 15 minute interval. Could you explain in detail, what you perform in order to restore that first step? Thanks.
    – John K. N.
    Commented Aug 5, 2020 at 12:26
  • Hi. Sorry that was not clear. So the issue we have in a business sense is that we want to refresh some downstream tables in this 15 minute interval. Currently the overnight process take about 5 hours. If I could simply see the data that has been updated, deleted or inserted in a separate database by applying the t-log to an empty DB, it would make my life far simpler. Finding these changes from one log in a DB that is 1.5TB and been in place for 15 years is otherwise very difficult. Can you also read my reply to Brent in this trail as I'm running out of space! Thank you for your help. Mike
    – Michael Edwards
    Commented Aug 6, 2020 at 7:18

1 Answer 1

Reset to default
2

No, in order to restore transaction logs, your database has to be in a restoring state first, and to do that, you'll need to restore a full backup.

Improve this answer
3
  • Thank you.To geta around it being restoring already, I was wondering if there was any way of 'hacking' this. Perhaps create a database with the same schema as the one I want to restore but no data. Somehow(!) hack the LSN to be appropriate to the t-log you want to apply, backup the database, restore it in standby/read only, and then take the t-log from the source database (not this database) and apply that. What do you think? Possible?
    – Michael Edwards
    Commented Aug 6, 2020 at 7:14
  • It wouldn't be possible for me, but you might be able to do it.
    – Brent Ozar
    Commented Aug 6, 2020 at 11:04
  • Thanks a lot Brent. I of course have come to the conclusion that you are correct. Just one example: seems the t-log only has deltas. eg to replace H with M, not full before and after values. So 'House' might become 'Mouse'. But as in my empty db there is no House' no update occurs.*Edit: I guess any update is going to fail in fact
    – Michael Edwards
    Commented Aug 7, 2020 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.