Recover data from encrypted log file

Question

Recover data from encrypted log file

spuyear

Old Hand

Points: 309
More actions
September 24, 2017 at 4:33 am

#325458

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!

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spuyear Old Hand Points: 309 More actions · Answer 2

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!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spuyear Old Hand Points: 309 More actions · Answer 4

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

spuyear - Sunday, September 24, 2017 7:43 AM
Is 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
See case 2:
https://www.sqlskills.com/blogs/paul/disaster-recovery-101-backing-up-the-tail-of-the-log/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spuyear Old Hand Points: 309 More actions · Answer 6

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spuyear Old Hand Points: 309 More actions · Answer 8

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.

spuyear Old Hand Points: 309 More actions · Answer 9

You method worked perfectly and we were able to restore all data. Now I just need to create a better backup plan.

Thanks!