July 11, 2005 at 5:47 pm
I accidentally deleted data from a table using a DELETE statement. The only backups I have were made after the delete. Actually, I have older backups, but they are several weeks old. I'm trying to restore the data using the transaction log, which contains all the transactions since the creation of the database, but I can't see how to do it.
I tried to use the 'restore to a point in time' method, but it seems I need a backup from before the deletion.
I also tried to use a log analyzer to generate the undo script, but the log file is 6GB large and the tools I tried don't seem to handle such large files well.
Any suggestions?
July 11, 2005 at 6:35 pm
Which tool are you using? I'm sure Lumigent or the other firm's tech support could work with you to recover the data.
July 11, 2005 at 7:21 pm
I tried SQL Log Rescue from red-gate software and ApexSQL Log, from ApexSQL Software. SQL Log Rescure took 2 hours to parse the log, which is ok, then took 3 hours to actually display the transaction list. As soon as I changed the filter, it froze again for 3 hours.
I'm still trying with ApexSQL Log. It's been filtering the data for 5 hours now... Yesterday it ran during all night and wasn't finished in the morning. Someone shut the machine down so I had to start over.
Lumigent's trial version can only be used on Northwind and I won't buy a tool unless I'm sure it will work.
I hoped there was some way to recover the data built in SQL Server.
July 12, 2005 at 11:05 am
Antoine,
In order to restore to a point-in-time, you must have a full database backup from before the deletions as well as an unbroken chain of log backups taken since the full backup. It sounds like the log was never backed up until after the deletions. If one of your old backups is a full, you may be able to restore.
Greg
Greg
July 12, 2005 at 11:40 am
I have a full backup from before the deletions, but I don't have log backups. I only have the full log, which was never truncated.
July 12, 2005 at 12:29 pm
Then back up the log now, if you haven't already. Restore the full backup using WITH NORECOVERY, then restore the log backup using WITH RECOVERY and designating the STOPAT time.
If you're only interested in recovering a single table without messing with the rest of the database, you should probably restore to a new database, then copy the data from the recovered table to the table in the original database. See 'How to restore to a new location' in Books Online.
Greg
Greg
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply