October 2, 2007 at 11:52 am
I have a database that is in full recovery mode, that needs to be recovered up to a point in time yesterday (10/1). I haven't confirmed if there is an existing full backup that was made prior to when it needs to be recovered to. Since it is in full recovery mode, all transactions should be in the log (assuming no trans log backups have been made).
Is there anything I can do to get the database back to a point in time, assuming that there are no full backups made prior to that point?
October 2, 2007 at 12:53 pm
I am not an expert but believe from what i have learned is that a recovery should start with the full backup. I hope the pro's visiting this forum should have some approach for your issue .
...Kumar
October 2, 2007 at 7:20 pm
You've NEVER done a Full Backup ?? EVER ?? In that case, you are out of luck. If you have a full backup from some period of time, then you will also need ALL the transaction logs since then, either on backup or still in the log.
There are some 3rd party tools such as Lumigent's Log Explorer that can be used to read the log file and generate SQL scripts of what has occured, then perhaps those steps could be undone. I don't know if their tool would work if it wasn't already in place.
October 3, 2007 at 8:59 pm
A log reader will let you "undo" transactions, basically by adding reversing transactions and let you recover back to some point in time. You'll have to "undo" everything back to that point, so think about it.
Otherwise, you need a full backup as homebrew has suggested.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 4, 2007 at 8:44 am
One more thing to bear in mind is that a log reader's undo functionality will only work as long as the reason you're going back in time is not physical database corruption. If you have corruption then the undo transaction generated will also hit the corruption and fail.
Also - as far as I know there isn't a log reader on the market that fully copes with all possible SQL Server 2005 operations in its undo functionality.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 4, 2007 at 3:55 pm
The question is clear, but...
Don't you really have the last full backup taken???
If your answer is NO then you have to think seriously about your recovery strategy, since you can't recover almost anything if there is not an starting base backup under full recovery mode.
In a critical case you might use a third party tool, but I think you should not rely on this.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply