August 29, 2012 at 9:18 am
How would one script out a TLOG restore using multiple files and the STOPAT option? Or would it just be like this for example?
restore log DBName from disk='D:\trn1.trn',
WITH RECOVERY
restore log DBName from disk='D:\trn2.trn',
WITH RECOVERY
restore log DBName from disk='D:\trn3.trn',
WITH RECOVERY
Or would I do something like this, I assume specifying the last backup file will include all of the earlier tlogs in the set automatically?
restore log DBName from disk = 'D:\lasttlog.trn'
WITH RECOVERY,
STOPAT = 'Aug 28, 2012 12:00:00 PM'
GO
We have a tlog backup job that runs every few minutes and I need to test restoring to a point in time using these logs. Never done anything like this before.
August 29, 2012 at 9:36 am
You need to restore each tran log file in sequence. You don't need the STOPAT on each log restore, but it doesn't hurt. Restore each log with NORECOVERY, and then recover at the end. Of course, you have to start this with a restore with NORECOVERY from a full backup, and apply all the logs since the full backup up to the point in time that you want to recover to.
Example:
-- After restore of full backup with no recovery
RESTORE LOG [MyDatabase]
FROM DISK = 'D\MyFolder\MyDatabase_log_2012_08_29_091714_4555008.trn'
WITH NORECOVERY, STOPAT = '2012-08-29 10:10:00.000'
RESTORE LOG [MyDatabase]
FROM DISK = 'D\MyFolder\MyDatabase_log_2012_08_29_093214_8683646.trn'
WITH NORECOVERY, STOPAT = '2012-08-29 10:10:00.000'
RESTORE LOG [MyDatabase]
FROM DISK = 'D\MyFolder\MyDatabase_log_2012_08_29_094714_2125114.trn'
WITH NORECOVERY, STOPAT = '2012-08-29 10:10:00.000'
RESTORE LOG [MyDatabase]
FROM DISK = 'D\MyFolder\MyDatabase_log_2012_08_29_100214_4728056.trn'
WITH NORECOVERY, STOPAT = '2012-08-29 10:10:00.000'
RESTORE DATABASE [MyDatabase] WITH RECOVERY
August 29, 2012 at 9:57 am
Ok so starting at the date and time of the full backup file, every trn in the sequence. Got it. So the same stopat time should be used after each restore statement reflecting the desired point in time to restore to?
August 29, 2012 at 10:20 am
ahthomas (8/29/2012)
Ok so starting at the date and time of the full backup file, every trn in the sequence. Got it. So the same stopat time should be used after each restore statement reflecting the desired point in time to restore to?
You really only need to use the STOPAT on tran logs created after the time you are stopping at, but it doesn't hurt to have STOPAT on all of them, and it makes it simpler to generate a script.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply