May 10, 2018 at 8:19 am
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.
May 10, 2018 at 9:15 am
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?
May 10, 2018 at 9:34 am
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
May 10, 2018 at 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.
May 10, 2018 at 12:26 pm
TrailRunner - Thursday, May 10, 2018 11:00 AMThey 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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 10, 2018 at 1:16 pm
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
May 11, 2018 at 10:54 am
Grant Fritchey - Thursday, May 10, 2018 12:26 PMTrailRunner - Thursday, May 10, 2018 11:00 AMThey 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.
May 11, 2018 at 2:18 pm
Does Unitrend have a utility program to create a native SQL Server backup file from their backup file?
May 11, 2018 at 2:39 pm
Lynn Pettis - Friday, May 11, 2018 2:18 PMDoes 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).
May 13, 2018 at 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!
May 14, 2018 at 9:18 am
sharky - Sunday, May 13, 2018 1:38 PMWithout 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.
May 14, 2018 at 9:46 am
TrailRunner - Friday, May 11, 2018 2:39 PMLynn Pettis - Friday, May 11, 2018 2:18 PMDoes 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?
June 13, 2018 at 11:18 pm
This was removed by the editor as SPAM
July 2, 2018 at 12:08 am
This was removed by the editor as SPAM
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply