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?
  • It's local to the server, based on the transaction log, so your second option.
    – user1240
    Commented Nov 20, 2013 at 19:56

2 Answers 2


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?

  • Thanks for providing a verified answer! (I think not using UTC is a small design flaw in SQL Server because it is more error prone. But that's off-topic here.)
    – usr
    Commented Nov 20, 2013 at 20:07
  • You raise a valid point it seems.
    – Mike Walsh
    Commented Nov 20, 2013 at 20:08

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 fn_dump_dblog.

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.

The 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 :

enter image description here

Thanks for posting a good question.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.