SQL 2012 How to Restore from Current LDF log file and an old BAK backup file

My customer got a total hard drive failure.

After sending it to drive recovery specialist we were able to recover the LDF log file (MyDB_0.LDF).
But the MDF file was completely destroyed (MyDB.MDF).
They have a good full backup from a month ago.

I tried this technique (to create a Tail Log Backup) by Paul Randal (Case #2) as follows:
1) Installed SQL Server 2012 on a new PC
2) Created a new database of same name (MyDB) - with same MDF and LDF file names as original
3) Took the new database offline
4) deleted the MDF and LDF files of the new database
5) put "MyDB_0.LDF" in the place of the LDF file I just deleted
6) put the database back on-line
7) after hitting F5 to refresh databases - it shows "MyDB (Recovery Pending)"
8) tried to do Tail Log Backup with this command

And I get this error...

Msg 3447, Level 16, State 1, Line 3
Could not activate or scan all of the log files for database 'MyDB'.

The sad thing is I know we can get this data back using ApexSQL-Log.  
I can see all the transactions since the last full backup in this program - so the log file is not damaged.  
But my client doesn't want to pay the $2000 fee for this software.

There has to be a way to restore this data, without having to purchase a third party tool.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPOwnerCommented:
hopefully a DB guru will chime in but HOW much is the data worth? Greater than $2K, have you returned to operation or are you still in limbo.. how much is the downtime costing? Sometimes you just bite the bullet and soldier on
enigmasolutionsAuthor Commented:
it is looking that way...
ste5anSenior DeveloperCommented:
The database was in full recovery mode? When was the last log backup made. When this was between the last full backup and the event, then the log is imho pretty useless, as I may not contain all transactions. The older ones maybe already removed. Thus using the log tail would create an inconsistent logical state.

The error itself indicates that you have a more complex file layout than only one mdf and one ldf..
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Vitor MontalvãoMSSQL Senior EngineerCommented:
I tried this technique (to create a Tail Log Backup) by Paul Randal (Case #2) as follows:
This technique only works with the original MDF and not from a backup one.

What you need to do is to restore back to a point in time (should be the last known time that your database was healthy).
For that you need the last FULL backup + last DIFF backup + latest TLOG backups. Do you have all those backups?
enigmasolutionsAuthor Commented:
I solved the problem using Apex SQL LOG.

In case anyone is interested I also posted a question on MSDN here


Bottom line - Apex SQL LOG was the only solution.
enigmasolutionsAuthor Commented:
For the record you can restore from an LDF file - see my post above.
enigmasolutionsAuthor Commented:
Actually you also need an old full backup.  Anyway per my post you can try it and you will find it works in a test environment.

Quite a different story when both the LDF and MDF are somewhat corrupt.  In this case Apex SQL Log was the only solution.
enigmasolutionsAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for enigmasolutions's comment #a40826178

for the following reason:

I selected my answer because it was the only correct answer
Vitor MontalvãoMSSQL Senior EngineerCommented:
For the record you can restore from an LDF file - see my post above.
Yes you can but you need to be very lucky for the LDF being the actual chain log for the database.
I'm glad you could solve your problem with ApexLog.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
enigmasolutionsAuthor Commented:
thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.