January 13, 2012 at 1:56 am
I'm aware of how a full recovery mode database can be restored using the latest backup and the (chain of) subsequent transaction log backups, to get to a known point - i.e the time at which any one of those tlog backups occurred. This would be usefull following, e.g. a system crash. However, I've been told that is also possible to recover to a point in time midway BETWEEN tlog backups. Is this true? If so, how would you go about it?
Consider the following scenario: a rogue malicious (but valid) transaction hits the database at 1pm. Although further, valid, updates take place after that, the database has been compromised by the rogue. The existence of the rogue is only detected, four hours later, at 5pm and it is deemed necessary to revert the database to its state prior to the rogue transaction. A full backup exists from 8am, and a full chain of tlog backups, taken every 2 hours (so at 10am, midday, 2pm and 4pm) also exists. But I need to restore the database to the state that it was in at 12:59. How do I go about getting the database back to this point in time?
Many thanks.
January 13, 2012 at 2:16 am
Hi Andy,
you should first restore your full backup and after this the log backups until 12 am. The important part is that you should restore the log backup from 2 pm with the stopat parameter. The following script shows such a demo scenario:
RESTORE DATABASE RestoreDemoDb
FROM DISK = 'D:\SQL Server\Demos\BackupRestoreDemo\RestoreDemoDb.bak'
WITH NORECOVERY;
RESTORE LOG RestoreDemoDb
FROM DISK = 'D:\SQL Server\Demos\BackupRestoreDemo\RestoreDemoDb_1.trn'
WITH NORECOVERY;
RESTORE LOG RestoreDemoDb
FROM DISK = 'D:\SQL Server\Demos\BackupRestoreDemo\RestoreDemoDb_2.trn'
WITH NORECOVERY;
RESTORE LOG RestoreDemoDb
FROM DISK = 'D:\SQL Server\Demos\BackupRestoreDemo\RestoreDemoDb_3.trn'
WITH NORECOVERY, STOPAT = '2011-02-23 09:42:19.560';
I hope it helps.
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
January 13, 2012 at 2:25 am
You might have a misunderstanding of what the Log file contains. From the way you asked your question I think you believe the Log backup contains the state of the database as it was when each Log backup was taken, so you could only restore to the times that the Log backups were taken.
Although a over-simplification of the Log, what it basically contains are all of the transactions that happened between Backups - have a look at the Stairways series http://www.sqlservercentral.com/stairway/73776/ and Gail's article http://www.sqlservercentral.com/articles/Administration/64582/. Therefore you can specify a point in time within a Log Backup for it to stop restoring.
http://msdn.microsoft.com/en-us/library/ms190982.aspx shows how to restore to a point in time using the interface, which can be used to generate the SQL code as an example to study.
January 13, 2012 at 2:26 am
In short, restore the following
8am full backup WITH NORECOVERY
10am log backup WITH NORECOVERY
midday log backup WITH NORECOVERY
2pm log backup WITH STOPAT '12:59:59.997', RECOVERY
One database restored to how it was just before 1pm.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2012 at 3:08 am
Thanks for you help all: it was the absence of the STOPAT parameter in my knowledge that was the key!
However, this leads on to the next question - again probably triggered by by lack of knowledge.
Is there any way to eliminate the rogue transaction and then reapply all transactions SUBSEQUENT to it. I.e. the ability to get the database back to the state it is in NOW, but without anything impacted by the rogue? As I understand it, the TLOG contains before and after images of affected rows, so won't be of any help.
Thx again
January 13, 2012 at 3:11 am
No. Not automatically.
You could restore another copy of the DB, roll the logs all the way forward and then use something like SQLDataCompare to see what's different and decide what you're going to sync over.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply