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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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
Begin Time and
End Time and you can use that from the T-Log backup to determine at what point-in-time you want to recover.
Begin Time and
End Time are both from the server that the backup was taken.
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.