I have no Backups at all.
And I dropped a important table completely.
Is there anyway to roll back this command.
I am guessing that the transaction log keeps this inforation and its still there. I think with other 3rd party tools you can recover to a point in time without backups so there must be a way we can do it without 3rd party tools , cant it?
Your 'best' option at this point is to immediately download an eval copy of one of the Third party log tools. Many are fully functioning for 14-30 days.
But since you indicated you have no backups, if by some bad karma your database is in 'Simple' mode and you may be SOL.
|||Is there anyother way without 3rd party tools.its a Full Recovery Model.
what is meant by > you may be SOL. <
|||
Download Lumnigents' Log Exlorer, or ApexSQL's SQLLog -or one of the several other good log tools.
It is just not worth any time and effort to try this without their help.
*SOL = "Sorry, Out of Luck"
|||what is u r backup policy ... if u have full backup and subsequent transaction log backup it is possible to restore to the time just before u deleted the table... Read about STOP AT option in Restore Database...
suppose
(a) 06 Mar 07 you deleted the table at 1000 AM...
(b) U have full backup of 05 Mar 07 20:00 hrs (8 PM)
(c) your database is in Full recovery model
(d) you have no differential backup
(e) you have no TL backup
06 Mar 07 15:00 hrs you found that the data has been delete by mistake and u need to get the databack
(a) first and foremost thing is to take the TL backup
(b) restore the full backup of 05 Mar 07 20:00 hrs as a new database with No REcovery option
(c) Restore the TL with STOPAT 09:55 hrs with Recovery option
this is just a scenario which u can relate and try to get the data back
Madhu
|||I am aware of the stop at option and the scenario you mentioned.But i am thinking of the worst-worst case scenario. What if you have no backups at all.
you deleted a table.
now u need to recover that table. How do you go about doing that without any backups.
If the history is kept in the transactional logs then it seems correct to assume that you can recover a table dropped 5 minutes before from the transactional logs.
Can we achive such a thing without using third party tools. worst case scenario.
Thanks everyone..|||
In simple terms, as I've tried to communicate to you a couple of times -NO!
If you need to do this, get a third party tool.
|||Just out of interest, I've noticed that SQL 2005's RESTORE DATABASE command now has a STOPAT option, however BOL doesn't give much information away as to how/under what circumstances this option can be used.
I gave it quick a test earlier on today but couldn't get it to work. Does anyone have any experience of using it? I was wondering if it could possibly be of use to the original poster of this thread (i.e. by taking a full backup now, and if the log hasn't been backed-up, then is it possible to restore to a point in time earlier than the full backup? - my guess is 'probably not', although in theory surely this should be possible by examining the log and rolling back / undoing all uncommitted and comitted transactions to the point in time specified?).
Thanks
Chris
It seems to me that if you make a 'Full Backup' at this juncture, ALL committed activities would be assumed to be in the database, and the transaction log would be truncated. Then not only would the table to permanently 'gone', but any record of it would no longer be in the transaction log.
The missing element is the previous 'Backup' -it does not exist, therefore, the only vestiges of the missing table (if other than page logging) 'might' be in the existing transaction log. Lunmigent's Log explorer would help find out IF there is any chance of recovering the data.
|||Just one thing, though, taking a full database backup does not truncate the transaction log - this is one of the reasons why I was wondering whether the STOPAT option in the RESTORE DATABASE command would be of use. Agreed that a log-reader tool would be a good tool to invest in / trial if not.
Chris
No comments:
Post a Comment