SQL Server can perform point-in-time restores using the STOPAT argument. In what time zone is that date and time value?
- Is it UTC?
- Is it local for the server the backup was taken?
- Is it local for the server the backup is restored?
Short Answer: From when the backup was taken
I actually wasn't sure of the answer, so I just made a database, put it in full recovery model, took a full backup, did some work (create a couple tables named after the time I created them) and then started restores. Restored the full and then attempted to apply the log backups.
When I did that I had to specify the time zone from when the changes were made - from when the backup was taken. If I tried to use the new time zone's setting, it errored - bad timing.
So the answer to your question in my experience with SQL Server 2012 and 2008R2 - appears to be "The local time from when the backup was taken"
This backs up my expectation before testing. The way the log records are written and the way the backups are taken - that makes sense.
That said - I can't imagine a ton of situations where the time zone is changing with the need to worry about point in time recovery?
Agree with Mike that its a good question and +1 for that.
Mike has answered your question. Out of curiosity, I tried to confirmed that using an undocumented (but widely used) function
This will have a
I have servers in NY, HK and LD and tried on them and it confirms it.
Below is a screenshot that explains it :
Thanks for posting a good question.