question

Kev Riley avatar image
Kev Riley asked

Restoring to point-in-time when DST overlaps

Imagine I have a database that has scheduled log backups happening every 10 minutes, starting on the hour. In October (in the UK) when daylight saving time ends (we go from British Summer Time, back to Greenwich Mean Time), the time gets 'reset' at 2am to 1am, so essentially we live that hour again. Scheduled SQL agent jobs won't run again in the repeated hour, (see http://www.sqlskills.com/blogs/paul/how-does-daylight-savings-time-affect-disaster-recovery/ for details), but if I had some other way of running the backup jobs (an underpaid minion for example), then I would have the following - 1:00am BST Log backup #1 - 1:10am BST Log backup #2 - 1:20am BST Log backup #3 - 1:30am BST Log backup #4 - 1:40am BST Log backup #5 - 1:50am BST Log backup #6 - 1:00am GMT Log backup #7 - 1:10am GMT Log backup #8 - 1:20am GMT Log backup #9 - 1:30am GMT Log backup #10 - etc.... What would happen if I issued a restore command with STOPAT 1:15am? Which 1:15am would SQL choose? the one in backup #3, or #9 ----- **Edit** : badly phrased question as pointed out by @KenJ below... So if the log backup ran every 2 hours (say 1am, 3am, 5am etc), then there would be 2 periods of 1am-2am in the 3am log backup file. Now what happens? I'm going to have to try this out.........
backup-restorelog-backupdisaster-recovery
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If this question is for more than a curious "What if" then I'd suggest trying it out on a test server. You can easily manipulate the date and see what happens and how to work with the results.
0 Likes 0 ·
which transaction log backup are you issuing the command for? restore database bob from disk='log backup #3' with stopat 1:15am OR restore database bob from disk='log backup #9' with stopat 1:15am can you issue a restore for multiple log files in the same command?
0 Likes 0 ·
@fatherjack - this is a curiosity question - but I'm nowhere near a server I can play on. Just wondered if anyone had come across this before? @KenJ - you're right really, in that you would only restore with STOPAT the one log file that had the 'correct' time in it. So... what would happen if log backups only happened every 2 hours, and therefore had 2 1:15am within them?
0 Likes 0 ·

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
You can use the STOPATMARK to designate the Log Sequence Number (LSN) that you want to restore to rather than using the STOPAT command which requires a time. It's pretty straight-forward and [documented here][1]. But, of course, the hard part is determining the exact LSN value that you want. There is an undocumented function sys.fn_dblog that will let you do it. But that comes with all the usual caveats. [The undocumented feature is written about here][2]. [1]: http://msdn.microsoft.com/en-us/library/ms191459.aspx [2]: http://www.sqlservercentral.com/articles/Transaction+Logs/71415/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.