 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / July 2008Data missing from 10 tables after restart
Data appears to be missing in over 10 SQL tables in a database on our SQL 2005 server. Here are the facts: 1. The data missing is from 3/31/2008 to 6/9/2008 2. The data was in the table on 6/9/2008 as we have reports that show data for this period 3. The data disappeared after a restart of the server on 6/10/2008 4. The database had a date and time stamp of 3/29/2008, but all other databases were dated 6/10/2008 after the restart 5. The database has been restored from two different backups and still shows data missing even though the reports show that the data was in the database when the backups were made 6. The database is using simple logging mode 7. The backups were made using our SAN software backup util that appears to backup the database file and the log file 8. No error messages were raised after the restart or when attaching the restored database files Can anyone explain what happened to the data and if there is anyway to recover it. DBCC Checkdb shows the database to be consistant. What happened to the data is that someone or some thing deleted it. Plain and simple. With simple mode you cannot review the transaction logs to recover the data. Seems like your ownly solution would be to use a comparison tool such as Diff from ApexSQL or SQLCompare from Red-Gate to find the data in either backups or restored copies of the database and migrate it to the production copy.  SignatureKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net > Data appears to be missing in over 10 SQL tables in a database on our SQL > 2005 server. Here are the facts: [quoted text clipped - 19 lines] > Can anyone explain what happened to the data and if there is anyway to > recover it. DBCC Checkdb shows the database to be consistant. No they didn't the data was there one day and gone the next the backups made before the data disappeared when restored do not show the data either. > What happened to the data is that someone or some thing deleted it. Plain > and simple. [quoted text clipped - 28 lines] > > Can anyone explain what happened to the data and if there is anyway to > > recover it. DBCC Checkdb shows the database to be consistant. Sorry, I just can't accept that statement at face value. Nothing personal meant by that either, btw. Either: 1) you are incorrect in your statement that the data was there at the point in time you say it was (despite the fact that is wasn't included in previous backups) 2) the data was there and truly was deleted and you are simply not aware of that 3) you have stumbled across a massively damaging bug in the engine (since you stated that dbcc checkdb showed no problems), in which case it is definitely time to get on the horn with Microsoft. I have no idea how they could investigate this without proof of exactly when the data went missing and what may have caused it though. My vote is on 1 or 2 or perhaps some other scenario that is not known or suspected at this time. Perhaps the missing data resided on separate file groups that were removed from the system or some such. SignatureKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net > No they didn't the data was there one day and gone the next the backups > made [quoted text clipped - 39 lines] >> > Can anyone explain what happened to the data and if there is anyway to >> > recover it. DBCC Checkdb shows the database to be consistant. David, TheSQLGuru is correct, someone or someTHING deleted it. The question is what things might happen. Since you said the database at restart had a timestamp of 3/29/2008, could you do the following select to make sure that no restore was run unawares: SELECT * FROM msdb.dbo.restorehistory WHERE destination_database_name = 'yourdatabasename' ORDER BY restore_date DESC Or (getting crazy here) could a copy of the database files (mdf & ldf) from March have been copied over the files you were using while the SQL Server was shut down? (FWIW: Way back on SQL Server 4.2 I had a database corruption problem that lost me some data in a couple of tables, but nothing like that since at least SQL Server 7.0. But 10 tables, all losing the same date range does not at all sound like a corruption problem.) RLF > No they didn't the data was there one day and gone the next the backups > made [quoted text clipped - 39 lines] >> > Can anyone explain what happened to the data and if there is anyway to >> > recover it. DBCC Checkdb shows the database to be consistant. Would anyone that might acutally be help reponse to this post. I don't need a lecture on things that didn't happen. > David, > [quoted text clipped - 61 lines] > >> > Can anyone explain what happened to the data and if there is anyway to > >> > recover it. DBCC Checkdb shows the database to be consistant. You WERE offered other explanations: possible restore you are unaware of (which could include some action by a SAN admin at the database file level) and file-group dropping, or an INCREDIBLY unlikely bug in the engine. Why don't you give Microsoft a call and see what they say to your situation? My money says they will state unequivocally that your data did not disappear without outside-the-engine influence. Another possibility came to mind: someone dropped the database and reattached it from older files. Also, did you scan the error logs for anything related to the problem database? SignatureKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net > Would anyone that might acutally be help reponse to this post. I don't > need [quoted text clipped - 79 lines] >> >> > to >> >> > recover it. DBCC Checkdb shows the database to be consistant. David, When a doctor is trying to work through your symptoms, how is he supposed to know what happened or did not happen without asking questions, using the stethoscope, taking X-Rays, etc? Nobody here is trying to say you are either clueless or lying, but we are trying to help you dig through possibilities. (Especially since we cannot login to your server and domain and examine everything ourselves.) So, what mysteriously caused all data in tables from 3/31/2008 to 6/9/2008 to disappear? Got no idea. RLF > Would anyone that might acutally be help reponse to this post. I don't > need [quoted text clipped - 79 lines] >> >> > to >> >> > recover it. DBCC Checkdb shows the database to be consistant. I don't see a lecture anywhere. I see helpful suggestions trying to offer things for you to look into to discover a cause. Since there isn't a magic button labeled "delete all data in all tables, where some column has a value between date 1 and date 2", and since we can't investigate for ourselves, I'm not sure what else you might expect. Complaining about the help certainly isn't going to help your situation. On 6/12/08 2:06 PM, in article 1E30E04C-FADA-4358-916D-314A74828CBB@microsoft.com, "davids@thorntonsinc.com" <davidsthorntonsinccom@discussions.microsoft.com> wrote: > Would anyone that might acutally be help reponse to this post. I don't need > a lecture on things that didn't happen. Well then let’s try this again. 1. No one but me has ever restored or attached a database no one in our company knows how to it or has access to it and our IT department is the size of a peanut. 2. The data existing before a backup was made 3. The data was gone after a restart 4. The backup was restored and the data was still gone These are NOT guesses or estimation but hard facts. I don’t want to believe you don’t want to believe it, but it is true. I’ve worked here for seven years and created the database seven years ago and this is the first time this has ever happened to me. Tell me that this isn’t what happened is useless. Take the scenario and run with it. If you don’t have answer then simply say there is not answer and call MS and be done with it. My firm belief is that the database had some kind of corruption back in March that set all of this in motion and that no one will be able to fix it. I figured before giving up completely I would post it here to see if anyone has experienced the issue in the past. I’ve worked in the computer industry for 20 years and MS products for 12 years so I’m not new to SQL Server I’ve been a dedicated fan since SQL 7.0 and would recommend the products anyone looking to buy. > I don't see a lecture anywhere. I see helpful suggestions trying to offer > things for you to look into to discover a cause. Since there isn't a magic [quoted text clipped - 13 lines] > > a lecture on things that didn't happen. > > useless. Take the scenario and run with it. If you don¹t have answer then > simply say there is not answer and call MS and be done with it. If that's the only answer you will accept, then call MS and be done with it. Since calling MS will most certainly cost you money, and will not resolve the problem in minutes, we were trying to point out things to investigate so that maybe that call would not be necessary. If you just want to continue to tell us that none of our theories are possible, what more do you want? <shrug> A I’ve worked in the computer industry for > 20 years and MS products for 12 years so I’m not new to SQL Server I’ve been > a dedicated fan since SQL 7.0 and would recommend the products anyone looking > to buy. With the attitude you show, I am highly skeptical of the above statement. You clearly refuse to accept helpful ideas to you for free about YOUR PROBLEM, get a clue, lose the attitude and be grateful for their time and expertise. > 4. The database had a date and time stamp of 3/29/2008, but all other > databases were dated 6/10/2008 after the restart I think this is an important clue. Database corruption won't change file file date. I suggest you verify the correct storage was presented to the host after the restart. Perhaps the current file is still out there somewhere in SAN-land. > 7. The backups were made using our SAN software backup util that appears > to > backup the database file and the log file Do you have any SQL backups or only backups at the SAN level? SignatureHope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ > Data appears to be missing in over 10 SQL tables in a database on our SQL > 2005 server. Here are the facts: [quoted text clipped - 19 lines] > Can anyone explain what happened to the data and if there is anyway to > recover it. DBCC Checkdb shows the database to be consistant. I've checked the restore log no restores have been performed on the database prior to this one in the past two years. I got the files directly from the Sys admin and reattached them from two separate backups with the same affect. We have no backups made directly by MSSQL all backups are handled by the sys admin using the SAN and Backup Exec with the Backup Exec SQL Agent. The same storage system that this database is using is the same one that all of the other databases used that came up properly this still wouldn’t explain why the database is missing the data after a restore. The database has been restored to two different servers both running SQL Server with the same affect. One again to clarify: Data in the database verified on reports Database backup Same Data still appears on reports Restart occurs Data gone 2 different restores performed to serverX and 2 different restores performed to serverY and data still gone Even with moving storage, SAN gone south, and everyone here having the intelligence of a gelatinous cube the restore should still have the data. We have done restores in the past to other databases without issue so that leads me to believe that the backup/restore part of the equation works or at least it has in the past. > > 4. The database had a date and time stamp of 3/29/2008, but all other > > databases were dated 6/10/2008 after the restart [quoted text clipped - 33 lines] > > Can anyone explain what happened to the data and if there is anyway to > > recover it. DBCC Checkdb shows the database to be consistant. On Jun 13, 2:46 am, dav...@thorntonsinc.com <davidsthorntonsinc...@discussions.microsoft.com> wrote: > I've checked the restore log no restores have been performed on the database > prior to this one in the past two years. [quoted text clipped - 23 lines] > me to believe that the backup/restore part of the equation works or at least > it has in the past. So, to summarize once again: - your sysadmin hands you a MDF and LDF file and says "this is the latest backup" - files are dated 3/29/2008 - you attach the files and have no data later than 3/29/2008 Isn't it PAINFULLY obvious what's happening here? Seriously? I don't know how to help you recover this "lost" data, but I'm 100% confident that SQL Server didn't eat it. Personally, I NEVER, EVER rely on a "sysadmin" to be responsible for my database backups. I do my own backups, database and logs, directly to disk. The "sysadmin" or tape jockey grabs those disk files and does what he will with them. I always have recent backups available to me on disk, copied to multiple locations, so that I know exactly what I have to work with in case of emergency. No data is in the these files data 3/31 in a file dated 3/29. I'm not a DBA I'm a developer. I don't care how you maintain your backups. "Tracy McKibben (http://www.realsqlguy.co" wrote: > On Jun 13, 2:46 am, dav...@thorntonsinc.com > <davidsthorntonsinc...@discussions.microsoft.com> wrote: [quoted text clipped - 43 lines] > multiple locations, so that I know exactly what I have to work with in > case of emergency. > No data is in the these files data 3/31 in a file dated 3/29. If a file was last modified on 3/29, why would you expect it to contain data from 3/31? Does your paycheck dated 6/15 include payment for hours you worked on 6/21? While the MDF file doesn't change for every single data modification, the timestamp would certainly change if it was detached properly, copied, backed up/restored, etc. > I don't care how you maintain your backups. You certainly aren't speaking to the people who are tying to help you, like a person who actually wants help. :-( What Tracy is trying to explain to you is that if you use proper SQL backups instead of letting the SAN guys do it, you would have a better chance at recovery the next time this happens... or more likely, it won't happen at all. In the meantime, I am fairly confident you are pooched, unless you find another copy of that database somewhere. Have you called MS yet? 1. I have NOT asked for a suggested back up strategy so offering one is of no help. 2. Stating that it is obvious what happened does help either 3. My question is if you have nothing to offer that will deal with the current situation why post at all. I know the company and they are not going to hire a DBA or pay for the better back up plan I’ve asked they have said no. I could just say you get what you pay for, but I like give anything my best effort. 4. I didn’t start acting like an a.s until I was treated like a total idiot. 5. I know where the backups come from and I know the dates. Let’s clear this up. A restore from 5/28 returns MDF and LDF with a 3/29 timestamp on them. These database files contain data from 3/31 which also makes no sense. When the restart occurred this is the same date it put on the files. All of the restores have returned exactly the same data. I don’t expect anyone to help because no one takes this at face value they keep pointing their fingers at a delete or backup/restores that did not occur. I’ve pretty much given up on recovering the data I would like to be to explain what happened, but that seems to be impossible as well. > > No data is in the these files data 3/31 in a file dated 3/29. > [quoted text clipped - 18 lines] > In the meantime, I am fairly confident you are pooched, unless you find > another copy of that database somewhere. Have you called MS yet? Yep, I guess so. <shrug> Hope you don't approach all problems this way. On 6/13/08 1:43 PM, in article 048CF447-E0A7-4BD6-BC99-3798E2D0C3A8@microsoft.com, "davids@thorntonsinc.com" <davidsthorntonsinccom@discussions.microsoft.com> wrote: > 1. I have NOT asked for a suggested back up strategy so offering one is of > no help. [quoted text clipped - 17 lines] > recovering the data I would like to be to explain what happened, but that > seems to be impossible as well. Thanks I find the "V" in MVP infinitely valuable, nothing personal meant by that either, btw. > Yep, I guess so. <shrug> Hope you don't approach all problems this way. > [quoted text clipped - 24 lines] > > recovering the data I would like to be to explain what happened, but that > > seems to be impossible as well. > Thanks I find the "V" in MVP infinitely valuable, nothing personal > meant by that either, btw. Hey, the question was asked did you call Microsoft? Well did you? Once again advice is being given, but you obviously are clue resistant. Please lose the attitude and appreciate the time that the people who have responded to you, in an attempt to assist YOU. This is YOUR PROBLEM not the MVP's of this group, how much are you paying anyone for THEIR time? Again, go sit in the corner and when you finally lose the attitude come back and apologize to the group for being an a.shole. > Thanks I find the "V" in MVP infinitely valuable, nothing personal > meant by that either, btw. [quoted text clipped - 27 lines] > > > recovering the data I would like to be to explain what happened, but that > > > seems to be impossible as well. davids, I have recently experienced a similar problem. Scenario: Data missing only between 6/29/2008 and 7/10/2008. 7/10/2008 server rebooted to take windows updates. Client software generating data has to be connected to server or it will get an error message. No calls from users in a 24/7 department Restore full backup from 7/8/2008 No data in DB. DB has data after 7/10/2008 and shows in backup. Unique ID's for records are sequential meaning no "missing" records Called software vendor. He is puzzled. "SQL data does not disappear, client software does not work if it is not writing to DB" My question: Is there a "cache" in SQL that could have these records Any and all help appreciated, Jamie > Thanks I find the "V" in MVP infinitely valuable, nothing personal > meant by that either, btw. [quoted text clipped - 27 lines] > > > recovering the data I would like to be to explain what happened, but that > > > seems to be impossible as well. >Data appears to be missing in over 10 SQL tables in a database on our SQL >2005 server. Here are the facts: >3. The data disappeared after a restart of the server on 6/10/2008 >5. The database has been restored from two different backups and still shows >data missing even though the reports show that the data was in the database >when the backups were made >7. The backups were made using our SAN software backup util that appears to >backup the database file and the log file To me this suggests SAN problems. It sounds like the SAN kept an intact copy of the database as of some time in March, and kept backing that copy up, while another copy of the database files were the ones actually being updated. Come the restart, the SAN put the old version back into use. The files used for the intervening months might be lost, or might still be out there on the SAN somewhere. Roy Harvey Beacon Falls, CT |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.