Anyway to undelete records?

A mistake was made and now I need to know if there is a way to restore deleted records from a database?

Any tools?  Suggestions?
LVL 2
Tekati68Information Technology DirectorAsked:
Who is Participating?
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.

Tekati68Information Technology DirectorAuthor Commented:
Little more detail.

I have a MS SQL 2005 Express edition installed on a client computer.

Records have been recorded in 1 table all day probably 2000 records or so.

The application was supposed to copy the records over to the server and verify they were copied properly then delete them from the client machine.

Well 1 out of 3 things worked correctly.

Can you guess which one?

Yep the delete records from client computer!

Any help would be greatly appreciated!
0
matth3wqCommented:
is the client computer XP and have you tried a simple system restore. iis there any backup out there? .bak file (backup) or any of the database files
or
you could try to fiind a data recovery program
0
giltjrCommented:
Did you take a back up of the table before running the program?

Does the Express edition do journalling?  Could you recover the records from the journal?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Tekati68Information Technology DirectorAuthor Commented:
It was done from this morning until now there is no backup.  The database log file would be my only guess but I was hoping someone with some knowledge about such things could help on this one.
0
giltjrCommented:
I have worked with a couple different database's and backups and journals are the only ways to recover records in any of them.

0
wisc77Commented:
depending on the type of transaction logs you have you car restore right upto the point the data was deleted from.

here is some info on how to do it.

http://www.builderau.com.au/program/sqlserver/soa/Restore-your-SQL-Server-database-using-transaction-logs/0,339028455,339282046,00.htm

see
Transaction log backups
RESTORE LOG NewDatabase
FROM DISK = ''D: \BackupFiles\ TestDatabase_TransactionLogBackup4.trn'
WITH RECOVERY

and
Restoring to a point in time

RESTORE LOG NewDatabase
FROM DISK = ''D: \BackupFiles\ TestDatabase_TransactionLogBackup4.trn'
WITH STOPAT = N'6/28/2007 4:01:45 PM', RECOVERY



0
Tekati68Information Technology DirectorAuthor Commented:
These will only work with a backup of the database or transaction log backups.

This database is set to Simple Recovery Model and has no backups.

ApexSQL Log would work just fine but I would have a very hard time spending 1500.00 for a one time restore.

There has to be another way?  Is there a service out there some where that would charge a fee to restore the records?

Anyone have any other solutions then ApexSQL?
0
St3veMaxCommented:
Unless you have Shadow Copy running; you dont really have any other options.

The transaction log is exactly that; Transactional. If you had a backup; you could restore that and replay the transaction log upto the point where the delete command is issued.

Probably a bit late now; but I would consider implementing backups in the future.

HTH
0
giltjrCommented:
Based on what you have  said the only way is to "process" the MS SQL server log and recover the records from there.

You could read the logs and try and make sense of how they are formatted and extract the information yourself.  You could search MS's site to see if they document the format of the logs, which would make it easier to process them.

As for the $1,500 to get ApexSQL log.  It may sound expensive for a one time restore.  However, if you have it you never know when you will need it.  You also have to take into account the cost of a possible one time service or the cost of your time attempting to do it yourself.  Let's say you make about $40K a year, that means you most likely costs the company about $30/hour once you figure in everything.  The $1,500 product is about 50 hours of your time.  If it takes you 80 hours to recover the records by trying to figure out the log youself, you are more expensive than the product.  If what you do is not easily reproducible, then the next time you need to do it, it just adds to the cost.  And believe, there will be a next time.

If you ask me $1,500 for a product to recover records is not all that bad.  But I guess it depends on the size of your company and your budget.  Generally the software we look at has a couple more zeros on the end.
0
giltjrCommented:
I am not sure it is supports reading MS SQL logs, but MS does provide a log parser tool.  It may support reading the MS SQL logs:

     http://www.microsoft.com/technet/scriptcenter/tools/logparser/default.mspx
0
Tekati68Information Technology DirectorAuthor Commented:
There just appears to be no good way to do this.  

If anyone happens to own a copy of ApexSQL Log and would like to make a few bucks restoring the records in my database let me know.

I could see paying a few hundred dollars but not 1500.00 for this one time repair.  

We simply don't have that type of budget unfortunately.
0
Tekati68Information Technology DirectorAuthor Commented:
Well I broke down and bought a copy of Apex's new Recover software to do the work.  I will say it worked like a charm and if anyone else has this issue I would highly recommend purchasing the software.

0

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