Point in time recovery with backups from different sources

  • TrailRunner

    SSCommitted

    Points: 1540

    Users came to me recently with a corrupted database after server crash. They have a full backup from a 3rd party backup software, and transaction log backups from a native SQL maintenance plan. Is it possible to restore this database to point in time before the server crash using these backup files from different sources? I tried the following:
    Recover database from full backup with norecovery.
    Recover trn file that is earlier than the full backup, SQL complaints about the time discrepancy...LSN... (I was excited as SQL at least recognize something here).
    Recover trn made after full backup was made, SQL complaints about the backup sources.

    users are in a bad situation as they stand to lose 3 days of data...

    Thanks for your help.

  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32402

    It depends on what 3rd party tool it was. You should theoretically be able to restore a 3rd party Full backup with norecovery followed by however many native transaction log backups you need.

    What was the "complaint" about the backup source?

  • John Mitchell-245523

    SSC Guru

    Points: 148652

    You need the full backup, plus every log backup taken after that, up to and including the one taken after the intended recovery point.  It doesn't matter whether the log backups were made in different ways - you just need them all available.

    John

  • TrailRunner

    SSCommitted

    Points: 1540

    They use Unitrend to back up the database. Unitrend only restores back to a live database. Backup files restore and restore with no recovery options are not available. So we restored the database to SQL, backed the restored db up, then restored it again with norecovery. After that we tried to apply the transaction log backups taken natively to it...it sounds like a hack, but I am hoping SQL is smart enough or not smart enough that it will actually take the transaction log backups...so far, no luck. I will have to get back to you on the actual error message. Thanks for all your replies so far.

  • Grant Fritchey

    SSC Guru

    Points: 396339

    TrailRunner - Thursday, May 10, 2018 11:00 AM

    They use Unitrend to back up the database. Unitrend only restores back to a live database. Backup files restore and restore with no recovery options are not available. So we restored the database to SQL, backed the restored db up, then restored it again with norecovery. After that we tried to apply the transaction log backups taken natively to it...it sounds like a hack, but I am hoping SQL is smart enough or not smart enough that it will actually take the transaction log backups...so far, no luck. I will have to get back to you on the actual error message. Thanks for all your replies so far.

    It can't work that way because the transaction logs counters are different between the backup after recovery and the native log backups. When you restored the Unitrend backup, it had to do recovery, which rolled forward or rolled back transactions. Now your transaction state is in a different state. In short, without a valid FULL backup, your client is losing three days of data. I'm so sorry. The only real option you have is to look to hiring a recovery specialist who can literally rewrite the backup files and/or the log files to make this recovery work. They're blindlingly expensive to hire. Depending on the business, may just want to eat the three days of data loss.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • goher2000

    SSCertifiable

    Points: 5465

    Perfect example of disaster, inability of DBA not to implement correct backup strategy and/or not having control over database backups, This is what happens if you give control of backup to sysadmins via 3rd party tools or do not test 3rd party tools properly

  • TrailRunner

    SSCommitted

    Points: 1540

    Grant Fritchey - Thursday, May 10, 2018 12:26 PM

    TrailRunner - Thursday, May 10, 2018 11:00 AM

    They use Unitrend to back up the database. Unitrend only restores back to a live database. Backup files restore and restore with no recovery options are not available. So we restored the database to SQL, backed the restored db up, then restored it again with norecovery. After that we tried to apply the transaction log backups taken natively to it...it sounds like a hack, but I am hoping SQL is smart enough or not smart enough that it will actually take the transaction log backups...so far, no luck. I will have to get back to you on the actual error message. Thanks for all your replies so far.

    It can't work that way because the transaction logs counters are different between the backup after recovery and the native log backups. When you restored the Unitrend backup, it had to do recovery, which rolled forward or rolled back transactions. Now your transaction state is in a different state. In short, without a valid FULL backup, your client is losing three days of data. I'm so sorry. The only real option you have is to look to hiring a recovery specialist who can literally rewrite the backup files and/or the log files to make this recovery work. They're blindlingly expensive to hire. Depending on the business, may just want to eat the three days of data loss.

    Thank you.

  • Lynn Pettis

    SSC Guru

    Points: 442238

    Does Unitrend have a utility program to create a native SQL Server backup file from their backup file?

  • TrailRunner

    SSCommitted

    Points: 1540

    Lynn Pettis - Friday, May 11, 2018 2:18 PM

    Does Unitrend have a utility program to create a native SQL Server backup file from their backup file?

    I have never used Unitrend before. First time I have heard of it when this user came to me for assistance. I was on that thought as well. From what I read and some quick navigation inside the tool, there is no way to restore to files, or even do a norecovery restore. Also, it lets you choose a different path for the .mdf and .ldf location, but...it will overwrite the existing db and delete the files in the original folder unless you give it a different name (it kind of makes sense, but the option for diff db file path is confusing).

  • sharky

    Default port

    Points: 1494

    Without the log chain, only option might be to somehow get access to the corrupted database.  Can you start the corrupted database in emergency mode? You might be able to restore some data using checkdb and the  allow_data_loss option. See Paul Randall's (he wrote checkdb) articles (https://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort/). He also have a good pruralsight course on last resort hacking of system tables. Good luck!

  • TrailRunner

    SSCommitted

    Points: 1540

    sharky - Sunday, May 13, 2018 1:38 PM

    Without the log chain, only option might be to somehow get access to the corrupted database.  Can you start the corrupted database in emergency mode? You might be able to restore some data using checkdb and the  allow_data_loss option. See Paul Randall's (he wrote checkdb) articles (https://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort/). He also have a good pruralsight course on last resort hacking of system tables. Good luck!

    Thanks for the suggestion Sharky. I had tried that route before posting this topic. We also has an open ticket with Microsoft premier support on this issue. So far, no luck.

  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32402

    TrailRunner - Friday, May 11, 2018 2:39 PM

    Lynn Pettis - Friday, May 11, 2018 2:18 PM

    Does Unitrend have a utility program to create a native SQL Server backup file from their backup file?

    I have never used Unitrend before. First time I have heard of it when this user came to me for assistance. I was on that thought as well. From what I read and some quick navigation inside the tool, there is no way to restore to files, or even do a norecovery restore. Also, it lets you choose a different path for the .mdf and .ldf location, but...it will overwrite the existing db and delete the files in the original folder unless you give it a different name (it kind of makes sense, but the option for diff db file path is confusing).

    I'm not familiar with this particular tool, but I use another one that attaches databases rather than restoring them, and that tool does allow databases to be attached with norecovery (as opposed to restore with norecovery). Is that definitely not the case here?

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply