September 24, 2017 at 4:33 am
I have a 2008 R2 database encrypted using TDE that crashed. The MDF was lost, but the LDF survived.
I was able to restore an old backup copy of the database. The data updated since the last backup should/may be on the LDF that survived the crash. Is there a way to get the data out of the LDF so it can be reloaded? Or is there a way to reattach the LDF so the restored database can read it?
I tried stopping the services and replacing the current LDF with the one that survived, but the database would not reopen. Log file included the warning about mismatched files.
Any help is appreciated!
September 24, 2017 at 6:21 am
Depends. It's the same as for a DB without TDE.
The DB needs to have been in full recovery model, you must have all the log backups since the full that you're restoring from.
If that is the case, you can hack-attach the DB to a SQL instance (it must have TDE certificate) and take a log backup WITH NOTRUNCATE. Then, restore the full backup WITH NORECOVERY, restore any log backups taken since the full backup followed by the one you took from the hack-attached DB. Restore the last one WITH RECOVERY and you should be up an running with no data loss.
See case 2:
https://www.sqlskills.com/blogs/paul/disaster-recovery-101-backing-up-the-tail-of-the-log/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 24, 2017 at 6:55 am
Thanks for the detail. There are no log backups from the database. The only log file I have is the LDF from right before the system crashed. Would this process still work?
Thanks!
September 24, 2017 at 7:09 am
If there are no log backups between the full and the time it crashed, then you, by definition, have all of them, since there are 0.
The full recovery requirement stands.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 24, 2017 at 7:43 am
Thanks again. Last question before I proceed. When you say hack attach the database, you mean the following, correct?
- Make a backup of restored database that is missing data (just in case)
- Put restored db in full recovery mode
- Detach restored database
- Replace LDF of restored database with the one from right before the crash
- Reattach the database
Is this the correct method?
September 24, 2017 at 7:58 am
spuyear - Sunday, September 24, 2017 7:43 AMIs this the correct method?
No, that will not work. You can't replace database files with older versions and expect it to workl.
GilaMonster - Sunday, September 24, 2017 6:21 AM
The details are in the blog post I linked
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 24, 2017 at 3:18 pm
Thanks again. I am trying to backup the tail end of the log file using the following:
BACKUP LOG [database_name] TO DISK = N'C:\Temp\database_name_log.bck'; WITH INIT, NO_TRUNCATE;
GO
I keep getting syntax errors. Any ideas?
September 24, 2017 at 4:55 pm
There's a semicolon in the middle of that statement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 25, 2017 at 9:02 am
Yikes, missed that. Now my database is in Suspect status. I tried to bring it online with no success. I get the same error as I got before I truncated the tail end of the log.
September 26, 2017 at 4:19 pm
You method worked perfectly and we were able to restore all data. Now I just need to create a better backup plan.
Thanks!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply