Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 Deleted Data

Author  Topic 

ann
Posting Yak Master

220 Posts

Posted - 2007-04-18 : 10:14:54
Is there anyway to retreive deleted data (rows) of a table? Unfortunately the backups are corrupt so we can't do a restore.
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 10:35:54
In that case, no.
Unless you still are in a transaction?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

ann
Posting Yak Master

220 Posts

Posted - 2007-04-18 : 10:37:39
No, not in transaction - really have no idea when the data was deleted - looks like a few days ago.
I thought I read somewhere sometime that some data could be retrieved from the LDF file - but I'm not well versed in SQL to know if this is true?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 10:40:41
How long time has it been since you last did a backup?
Maybe, it's a longshot.

1) Backup log
2) Try to do a point-in time restore.

But that might not help since you don't know where in time the records were present.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

ann
Posting Yak Master

220 Posts

Posted - 2007-04-18 : 10:46:59
Backups are done daily - the problem is that the tool used to retrieve the backups once they've been created by SQL job, is only returning partial data - maybe 5% - and not current data - data that was entered when the db was created.
I'm assuming that the SQL job backup is constantly overwriting itself, so I don't have anything to go back on. If I could even go back 2 weeks would be better than nothing
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 10:51:13
You might stick around a few hours more, until Kristen and Tara kicks in?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

ann
Posting Yak Master

220 Posts

Posted - 2007-04-18 : 10:54:02
yes, I will stick around here - and anywhere else where I might find some help - I really don't know what else to do.
Thanks for trying to help though, I do appreciate it
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-04-18 : 11:27:23
Do you have access to the backup files? What are the results from doing a RESTORE HEADERONLY? Does it only list a single backup, or multiple backups in the backup set? It may look like there's only a single backup file but multiple backups are appended into the same file unless the backup command is using WITH INIT.

Thanks

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
Go to Top of Page

ann
Posting Yak Master

220 Posts

Posted - 2007-04-18 : 11:33:32
I'm not familiar with restore headeronly? The only way I know how to do a restore is via Tasks, Restore Database. Can you explain please?
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-04-18 : 11:52:04
Go to Books Online and lookup RESTORE HEADERONLY for an explanation and examples of how to use it through a query window. It displays the complete contents of a backup set.

Or does anyone watching the thread know how to do this with Enterprise Manager? (I'm not a UI using guy...)

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-04-18 : 11:52:52
Alternatively, and this may be your best bet, call Product Support and they can walk you through this more easily than we can over a forum thread.

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
Go to Top of Page

ann
Posting Yak Master

220 Posts

Posted - 2007-04-18 : 11:56:26
Thank you. I think I will try product support
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-04-19 : 03:52:05
"I'm not familiar with restore headeronly?"

Probably a bit late but see also:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example

"If I could even go back 2 weeks would be better than nothing"

perhaps better still, IF you can get some sort of backup restore running, would be to Restore to a New, Temporary, database, and then selectively copy the deleted data back to the Main database.

"1) Backup log
2) Try to do a point-in time restore.
"

That would be good, but the "point-in-time" restore is going to need a Restore from a Full Backup first, then all TLog backup(s) since, so if the OP has a problem getting any of the Full backups restored I doubt that will be a runner .

Kristen
Go to Top of Page
   

- Advertisement -