2017-07-12

STOPAT And Date Formats

Dave Mason - SQL Server RESTORE LOG STOPAT

I recently had the need to restore a database with recovery to a point in time. I've only done this a few times, so I had to look up the syntax for stopping the last and final transaction log restore. Microsoft Docs shows us this "Basic Transact-SQL syntax":

RESTORE LOG database_name FROM WITH STOPAT =time, RECOVERY…


There's nothing I see in the documentation regarding the format for "time". But there are a couple of examples, including this one:

RESTORE LOG AdventureWorks  
FROM AdventureWorksBackups  
WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';

That string looks suspiciously like a US English date format. I suspect that wouldn't work for languages that don't recognize "Apr" as a month. And what if the date is displayed in one of the many date formats used outside of the US? Lets find out!

By my count, there are 21 formats for dates and/or times in SQL Server (assuming a four digit year). This conclusion is based on the number of different date and time styles for the CONVERT function. I tried every one of those 21 string formats with the STOPAT clause for a point in time recovery. My LANGUAGE was "us_english" and my DATEFORMAT was "mdy". Here's what I found:

StyleOutput FormatRESTORE result
100Jul 11 2017 10:29AMSuccess
10107/11/2017Date interpreted incorrectly.
1022017.07.11Date interpreted incorrectly.
10311/07/2017Date interpreted incorrectly.
10411.07.2017Date interpreted incorrectly.
10511-07-2017Date interpreted incorrectly.
10611 Jul 2017Date interpreted incorrectly.
107Jul 11, 2017Date interpreted incorrectly.
10810:29:38Invalid value specified for STOPAT parameter.
109Jul 11 2017 10:29:38:000AMSuccess
11007-11-2017Date interpreted incorrectly.
1112017/07/11Date interpreted incorrectly.
11220170711Date interpreted incorrectly.
11311 Jul 2017 10:29:38:000Success
11410:29:38:000Invalid value specified for STOPAT parameter.
1202017-07-11 10:29:38Success
1212017-07-11 10:29:38.000Success
1262017-07-11T10:29:38Success
1272017-07-11T10:29:38Success
13017 ???? 1438 10:29:38:000AMInvalid value specified for STOPAT parameter.
13117/10/1438 10:29:38:000AMInvalid value specified for STOPAT parameter.

Change the DATEFORMAT to something other than "mdy" (either directly with SET DATEFORMAT or indirectly with SET LANGUAGE) and the above results change significantly. Curiously, I've found you can pass in a DATETIME variable to the RESTORE command:

DECLARE @StopAt DATETIME = '2017-07-11 10:29:38.000'; 

RESTORE DATABASE DaveTest
FROM DISK = 'Z:\Backup\Trx Log\DaveTest 2017-07-11_103336.Tuesday.trn'
WITH RECOVERY, STOPAT = @StopAt;
GO


I didn't see anything about that in the documentation. Of course, assigning a value to a DATETIME variable with a string literal presents its own challenges (hello CONVERT and TRY_CONVERT!). So what can we conclude from all this? Not much! All I can add is the banal and unhelpful advice to "be careful". Dealing with dates and times in SQL Server can be a pain in the ass. But you already knew that, didn't you?



Thanks to Drew Furgiuele (b|t) and Andrew Pruski (b|t) for proofreading and technical input!

SHARE