Recover deleted files from log filerecover deleted files from MS SQL Server 2008 R2 log file

Is there any way to recover deleted records of a table from log file? Database is MS SQL Server 2008 R2. Database recovery model is 'Simple'. Found some tools like ApexSQL but the price is not at all affordable.
mvcdeveloperdxbAsked:
Who is Participating?

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

x
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.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
The issue with Simple Recovery model is that transaction records are marked for reuse after being committed. So even if you buy something expensive, your chances are below significance, unless you immediately stopped MSSQL from running.
In Full Recovery, the log entries would be maintained until backed up explicitely, and hence changes can be rolled back.

The only reliable way for you is to take a backup, restore it as a different DB, and extract data as necessary.

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
mvcdeveloperdxbAuthor Commented:
Very little transaction has been done after the incident. I have took a backup and restored it as different db. Is there any specific procedures to get back my deleted data? Spending 2000 dollars for ApexSQL is not feasible for my client.
mvcdeveloperdxbAuthor Commented:
After restoring the db, i ran the following query to find out deleted rows

SELECT
    [Current LSN],
    [Transaction ID],
    [Operation],
    [Context],
    [AllocUnitName]
FROM fn_dbLog(NULL, NULL)
WHERE [Operation] = 'LOP_DELETE_ROWS'

But the result was zero rows. What does it mean? No deleted records? But with the trial version os ApexSQL i have found several deleted rows with the same log file
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
The above query requires an untruncated transaction log, which you do not have because of Simple Recovery. You need to access the data tables directly, and compare with the rows in your current DB for missing ones. Depending on what data has been removed, this can be very tedious, of course.
Scott PletcherSenior DBACommented:
Do you have a backup of the db before the rows were deleted?  Then you could restore that db and compare the data in the two tables.

If not, it will extremely difficult to recover the rows.  Text data would be easiest to identity, and you can do that by looking at the physical disk space where the table was stored.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Spending 2000 dollars for ApexSQL is not feasible for my client.
Means that the data he lost worths less than $2000. If worths more for sure he will say that's cheap.

Database recovery model is 'Simple'.
Again, means that client's data is worthless. If it was important it wouldn't set the recover model to Simple but Full. Saving money on this kind of stuffs is don't give the real importance on data.

Is there any way to recover deleted records of a table from log file?
So, putting what was said above, there's always a way to recover those data by inserting them again. This activity will cust less than $2000?
mvcdeveloperdxbAuthor Commented:
Finally client decided to insert the data again. thanks for your tips
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 2008

From novice to tech pro — start learning today.