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.........
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.
@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?
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/
14 People are following this question.
