1

I have a database in standby mode.

-- STEP 1
-- Restore path F1 > T7
RESTORE DATABASE [TestRestore] FROM DISK = N'C:\Temp\F1.BAK' WITH FILE = 1, 
NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
GO

-- STEP 1
RESTORE LOG [TestRestore] FROM DISK = N'C:\Temp\T7.TRN' WITH FILE = 1, 
STANDBY = N'C:\Temp\TestRestore_RollbackUndoFile.tuf', NOUNLOAD, STATS = 5
GO

And now I'm gonna do log shipping. My database is accessible(read only)so it will be easy to determine specific point in time where I need to stop at.

-- STEP 3
-- Restore T8
USE [master]
RESTORE LOG [TestRestore] FROM DISK = N'C:\Temp\T8.TRN' WITH FILE = 1, 
STANDBY = N'C:\Temp\TestRestore_RollbackUndoFile.tuf', 
NOUNLOAD, STATS = 10, STOPAT = N'2013-10-25T19:55:26'
GO

-- STEP 4
-- ckecking
SELECT * FROM TestRestore.dbo.TranRecord
GO

I can perform the same transaction log restore repeatedly until I arrive at the required point-in-time by incrementing the time specified in the STOPAT clause.

But, for example, do I need to repat the whole restore again in case I've realized that I've restored too far ahead ?(I mean delete the current restored db and do restore F1 > T7 > T8 again and stop at exactly where I need). Or it is possible to undo just the last log shipping ?(in my example it's STEP 3).

Msdn gives it pretty controversial connotation.

Specifies a standby file that allows the recovery effects to be undone.

1

You must restart the restore sequence.

I believe SQL Server could theoretically allow moving backwards in time because the logs contain both undo and redo information. But this is either not implemented or it is not possible for some reason.

  • Thanks fro this info. I appreciate it. At least now I know. Msdn gives it pretty controversial connotation. – isxaker Aug 26 '18 at 17:48
  • docs.microsoft.com/en-us/sql/t-sql/statements/… Here you can see what the standby file contains. RESTORE WITH STANDBY essentially recovers the database fully but it copies unrecovered pages to the standby file. If you continue restoring those pages are copied back to undo the recovery that had taken place. So that file can only be used to move back in time to a very specific point. – usr Aug 26 '18 at 17:53
  • move back I misunderstood it. In other words only one direction is possible, Right ?(when a restoring starts we are always going further - from past to future - even if it's standby restore) – isxaker Aug 26 '18 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.