Take the 2-minute tour ×
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's 100% free, no registration required.

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

2 Answers 2

up vote 6 down vote accepted

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?

share|improve this answer
    
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 Nov 20 '13 at 20:07
    
You raise a valid point it seems. –  Mike Walsh Nov 20 '13 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.

share|improve this answer
2  
+1 for the double confirmation –  Max Vernon Nov 20 '13 at 21:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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