SQL Server Performance Forum – Threads Archive
Can you manually roll back or forward from a log?
Is it possible to either take a live database and transaction log and manually roll back the transactions to a specific point? And can you take an old copy of a database (in full backup mode) and use a current transaction log to roll forwards transactions that took place after the backup was taken to a specified point in time (before the most recent transaction in the log)? It seems logical that this may be possible, but I can’t find any information on how this might be done. Thanks MaxI think you need a 3rd party tool. Take a look ours sponsors.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
Yes, it’s called Point In Time Recovery; it only works if the DB was in Full Recovery mode. see BOL Recovering to a Point In Time or http://www.databasejournal.com/features/mssql/article.php/3530616 or google "SQL Server point in time recovery" ————————–
Yes, I am a geek. I know. I can’t help it.
Agree Merrill if they need to back to last or some transaction log. I was thinking in roll back some transaction. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
The database is fully backed up on a daily basis (log file and database file), but with no incremental backups inbetween. Basically what I’d like to do is restore a table to the state it was in before a command was executed. The database file and log file are intact and I have access to an older (full) copy of the database. I was wondering if it would be possible to roll forwards the transactions in the live log file that have occured since a particular backup was taken, up to a point in time that I know is just before the command in question was executed? Or alternatively, is it possible to take the live database and roll back the transactions in the live log file back to the point in time before the command was executed? The backup was taken using a 3rd party application which I don’t believe includes this functionality.
Max
Check: http://www.sql-server-performance.com/da_sql_rescue_spotlight.asp Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
I am not an expert in this, but it does seem like the regular point-in-time recovery should enable you to do what you ask. That’s the whole point of it <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />. For example (don’t do this without researching it!) I imagine you could stop work with the database, and then use point in time recovery with the last full backup and the current transaction log to restore to any previous state at any time. The whole purpose of the full recovery model is that it does this.<br /><br />Last night’s backup + today’s log file, restored to point in time = database state at that time.<br /><br />Or have I missed something vital in your question?<br /><br />————————–<br />Yes, I am a geek. I know. I can’t help it.
Hi,
Maybe this third party tool can help : LOG EXPLORER
http://www.lumigent.com AKTHAR DILMOHAMUD
65 BENARES ST
PORT LOUIS
MAURITIUS
Thanks for the article on point-in-time recovery. The problem is that the database in question is backed up by a 3rd party tool which does not appear to offer this facility. I will have a look around at 3rd party recovery tools to see if any can work from a live transaction log file.
]]>