?
Solved

SQL2005 lost 5000 records ? Possible to recover (file to download)

Posted on 2010-01-02
23
Medium Priority
?
338 Views
Last Modified: 2012-08-13
Hello,

I have a customer who has lost about 5000 records (not by a human error) in the database werkbon table werkbon.

In table werkbon the lost records are from
werkbonid : 9203 22/12/2008  to werkbonid : 14006 14/08/2009

http://rapidshare.com/files/329197319/backup_20091229.zip

I have tried already six different programs to recover the records, but no program does find the lost records.
The file is with the SQL2005 database and the complete LDF-file.

Thanks in advance

Johan Pyfferoen
Belgium

0
Comment
Question by:pyfferoen
  • 13
  • 10
23 Comments
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26161694
Do you have an older backup from when the files were there?

I also don't understand how the records were just "lost" without human error. Unless there was a case of something on the server causing database corruption, data doesn't just fall out of a database. Someone would have to intentionally delete the data or there would have to be a bug in the application code that deleted more rows than desired.

I am attempting to download your file from rapidshare right now but the free servers are "overloaded" and I am not about to pay for an account there. Once I get the file, scan it for virus/spyware and see just what it is, I might have more but the short answer is:

If a transaction has already been committed for the delete (most likely the case here) then it is done. The only way to recover is to restore an older backup of the database, perhaps side by side on the same server with a new name and find the missing data and insert it into the previously existing database.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26161755
I am giving up on the download from the free rapidshare. can't ever get to download it right now. What is in the file?  A Backup? The databse .mdf or .ldf file?

Here are the options I see you can go to in most situations:

IF Your Database Is In Simple Recovery Mode (Right click on DB in Management Studio, Properties, Options)
1.) Restore to a previous backup and compare the tables and get the data that exists in the backup but not in the database that had the deletes.

IF Your Database Is In Full Recovery Mode

1.) Take a Transaction log backup now if you haven't. Also take a full backup just to be safe.

2.) If you have been doing transaction log backups all along, great you'll need those.

3.) Restore your last full backup with NORECOVERY specified.
4.) Apply the transaction log backups in sequence specifying NORECOVERY on each one using the STOPAT comamnd to specify the time to recover to.
5.)Consider using STANDBY (http://msdn.microsoft.com/en-us/library/ms191468.aspx) if you don't know the specific point in time.

The below links will help.

http://msdn.microsoft.com/en-us/library/ms190982.aspx
http://msdn.microsoft.com/en-us/library/ms179451.aspx
http://msdn.microsoft.com/en-us/library/ms178143.aspx
0
 

Author Comment

by:pyfferoen
ID: 26161791
Hello Mike,

We have an older backup from before the records are lost. I don't find a delete statement in the log-file that deletes those 5000 records.

The file is both the mdf and ldf. There are no other backup's, because there was a problem with the maintenance plan on the database.

I have already tried with that backup from 2008 and then with the whole transaction log, but it sees that already fields where changed and so it won't do that restore. I have also tried with your suggestions yet.


The file can also be found on : (as a fast download)
//vtdvweb.7host.com/backup_20091229.zip


Thanks in advance
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26161815
Downloading that now. 5 minutes remaining :) I trust that there is no sensitive data in this zip?

When you say you don't see the delete in the log-file. What do you mean? Which log file? The .LDF? How are you looking for the delete, the .LDF file is a complex structure that needs certain tools to read detailed data from it. Or do you mean the error log? You wouldn't see deletes there.

So if the .MDF and .LDF are just from a detaching of the database (which they need to be to be of any use since they are always being written to, so if SQL was running when you copied those into the zip file they won't be useful because they are likely out of sync).

If there is no backup and the database was not in Full Recovery mode the options are definitely limited but I will have a look at the file anyway, I won't be able to get data back but I can at least see what recovery model you are in.
0
 

Author Comment

by:pyfferoen
ID: 26161830
Hello Mike,

I detached the files, before I made a copy and I tried all kinds of software.
A good one is  APEX SQL Log, that looks in that ldf file.
That database is in full recovery mode.

I downloaded that file in 10 seconds from that website.

There is no sensitive data in it.It are just workorders.

Johan
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26161843
Ok. Yeah with a log reader you can see info. When is the last time there has been a log backup of this database and about when do you think the data was lost?
0
 

Author Comment

by:pyfferoen
ID: 26161850
In table werkbon the lost records are from
werkbonid : 9203 22/12/2008  
to werkbonid : 14006 14/08/2009

There where only full backups (last one was november 2008)  taken and the log is the complete log from all the data.

0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26161854
ok. but you don't know at what point the deletes may have occurred?
0
 

Author Comment

by:pyfferoen
ID: 26161868
Not exactly.

They have noticed it recently, because they made reports now for the end of the year.

I have then searched in that log with APEX Log reader for the deletion of records that where in between the lost records, but I don't see a record that deletes so much records. I can set some records back, but these where the records that where deleted on purpose.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26161888
Well if the log file has never been backed up to disk or backed up with truncate_only or no_log, the data would be there and the delete would have been logged. All operaions are logged to some extent and deletes are always fully logged.

I would actually say, judging by the size of the transaction log that it has likely either been backed up to disk or potentially backedup with truncate only or no log due to log file growth in the past. Or it has switched recovery models.

If there is missing data but it is not in the log then either it was never there to begin with or the log has been forced to be truncated by one of the above methods, unfortunately. If that is the case we are really back to the only option being restore full backups and try and add the missing data but you have had some backups not run and have had some bad attempts.

I will look further still.
0
 

Author Comment

by:pyfferoen
ID: 26161894
I think also that the data is in there. I am also looking further.

Thanks in advance.

If you can solve this, I will pay for this.

0
 

Author Comment

by:pyfferoen
ID: 26161943
I have just found with an update from Apex Log that the records were deleted on
2009-11-06 12:52 (lots of them)

Now I have to know how to undelete them
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26161955
ok perfect. I was just downloading a log reader but I will stop. We should be able to do that recovery process I talked about. But we'll want to restore it to a new database because we don't want to go back to that point in time.

Basically we will take a log backup now, take a full backup now. Restore into a NEW database, first restore the full backup with no recovery. Then restore the one log file backup with recovery and stopat 2009-11-06 12:51 specified.

I'll give it a shot here and see what we see.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26162013
I was attempting to do the point in time restore and got this:

Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 3988000000116000001, which is too early to apply to the database. A more recent log backup that includes LSN 3988000000120100001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Did you take a log backup before sending me the MDF/LDF files?
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26162018
Actually let me change the order of my backup first. Hang on.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26162066
Duh.. i'm being a bit of a moron here. I need to have an older full backup. Since your log file contains all data still according to what you say any backup prior to 11/26 should work.. You can send it to me the same way to play with or just try it yourself:

Restore that backup to a new database... something like this

-- restore the full
-- no recovery specified

RESTORE DATABASE [test1] FROM  
DISK = N'C:\WerkBon_Full.bak'
WITH  FILE = 1,  
MOVE N'werkbon_Data'
TO N'C:\Documents and Settings\mike.walsh\Desktop\WerkBon_Full.MDF',  
MOVE N'werkbon_Log'
TO N'C:\Documents and Settings\mike.walsh\Desktop\WerkBon_Full_log.LDF',  
NORECOVERY,  NOUNLOAD,  STATS = 10
GO

(can't be a recent full backup (though I always take one just to be safe).. Has to be before the date of delete)

Then apply the transaction log backup you just took right now which has the data in it from the failure time. Specify stopat and with recovery..
--restore the log backup
-- recovery to the time before the deletes
-- experiment with times
RESTORE LOG [test1] FROM  DISK = N'C:\Werkbon_log.trn'
WITH  FILE = 1,  NOUNLOAD,  STATS = 10,  STOPAT = N'2009-01-06T12:51:00'
,RECOVERY
GO

You should then be able to see which rows exist in this test database and not the existing database and create some insert statements.
0
 

Author Comment

by:pyfferoen
ID: 26162072
I have installed Quest Litespeed Log reader and there I can see that transaction, and I try to redo it.

I have tried that script that it generates, but it does not much.
undo-script-delete-5000-records.sql
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26162084
Well that script is an attempt to "redo" the deletes which are already done. You need to roll that work back.
0
 

Author Comment

by:pyfferoen
ID: 26162097
This is a backup from 2008 november
backupEeklo20081112.zip
0
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 2000 total points
ID: 26162133
Alright, so you have to restore that and then apply the log backup that you just took today while chatting with me..

-- restore the full
-- no recovery specified

RESTORE DATABASE [test1] FROM  
DISK = N'C:\Documents and settings\mike.walsh\desktop\backupEeklo20081112'
WITH  FILE = 1,  
MOVE N'werkbon_Data'
TO N'C:\Documents and Settings\mike.walsh\Desktop\WerkBon_Full.MDF',  
MOVE N'werkbon_Log'
TO N'C:\Documents and Settings\mike.walsh\Desktop\WerkBon_Full_log.LDF',  
NORECOVERY,  NOUNLOAD,  STATS = 10
GO

--restore the log backup
-- recovery to the time before the deletes
-- experiment with times
RESTORE LOG [test1] FROM  DISK = N'C:\Werkbon_log.trn'
WITH  FILE = 1,  NOUNLOAD,  STATS = 10,  STOPAT = N'2009-01-06T12:51:00'
,RECOVERY
GO


-- then the below select shows you the rows
-- in one database that aren't in the other.
-- you'll notice there are only 254 so
-- you might want to try an earlier stopat, etc.
select * from test1.dbo.werkbon
where werkbon_id not in(select werkbon_id from werkbon.dbo.werkbon)


I would try the above.. Change paths and file/db names as appropriate. Also look at the log reader undo or rollback feature in the Quest Log Reader tool..

Just keep the following in mind:

1.) Restore to a NEW database, don't overwrite your existing DB
2.) Try and stop at the time that has the data there before the first of the deletes started on that day
3.) you'll then have to generate some scripts to insert the missing rows.. looking at the above SQL statement you should be able to figure one out.. Do an insert select from the test database where it doesn't yet exist.
4.) this is just this one table, any data quality issues that may arise because of other deletes aren't fixed here, you'll have to go back to other tables as well.
5.) You may undo deletes that were true deletes

But this at least gets some of the data recovered and you'll know make sure the maintenance job for backups is working properly each day ;-)
0
 

Author Comment

by:pyfferoen
ID: 26162180
I will try it with that Quest software, because about 9000 records (both tables together) are involved.

Thanks for you help.

Johan
0
 

Author Comment

by:pyfferoen
ID: 26162333
I was able to find the command that deleted the 9000 records with the software from Quest Litespeed.
With this software I made a restore script out of that delete command.
I had to manually remove the links between the table, before I run that script.
Afterwards I had to reset the links between the tables.

I am a happy man !
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26162357
Great :-)
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question