[Bug] Restore-DbaDatabase -RestoreTime date formatting error between fr/US

[WidCed]

Report

Host used

• powershell.exe
• ISE
• VS Code
• Other (please specify)

Errors Received

writeErrorStream      : True
PSMessageDetails      :
Exception             : System.Exception: System.Data.SqlClient.SqlError: Valeur non valide spécifiée pour le paramètre STOPAT. ---> System.Exception: System.Data.SqlClient.SqlError: Valeur non valide spécifiée pour le paramètre STOPAT. ---> System.Management.Automation.MethodInvocationException: Exception lors de l'appel de «SqlRestore» avec «1» argument(s):
                        «Restore failed for Server 'SQL-DBA-01\SQL1'. » ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Restore failed for Server 'SQL-DBA-01\SQL1'.  ---> Microsoft.SqlServer.Management.Smo.SmoException: System.Data.SqlClient.SqlError: Valeur non valide spécifiée pour le paramètre STOPAT.
                           à Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages, Boolean retry)
                           à Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
                           à Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
                           --- Fin de la trace de la pile d'exception interne ---
                           à Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
                           à CallSite.Target(Closure , CallSite , Object , Object )
                           --- Fin de la trace de la pile d'exception interne ---
                           à System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
                           à System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
                           à System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
                           à System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
                           --- Fin de la trace de la pile d'exception interne ---
                           --- Fin de la trace de la pile d'exception interne ---
TargetObject          : SQL-DBA-01\SQL1
CategoryInfo          : NotSpecified: (SQL-DBA-01\SQL1:PSObject) [Write-Error], Exception
FullyQualifiedErrorId : dbatools_Restore-DbaDatabase,Stop-Function
ErrorDetails          : System.Data.SqlClient.SqlError: Valeur non valide spécifiée pour le paramètre STOPAT.
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : à Stop-Function, C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.152\allcommands.ps1 : ligne 89176
                        à Restore-DbaDatabase<End>, C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.152\allcommands.ps1 : ligne 65537
                        à <ScriptBlock>, <Aucun fichier> : ligne 1
PipelineIterationInfo : {0, 1}

Steps to Reproduce

Restore-DbaDatabase -SqlInstance $SqlInstance -Path $BackupPath -DatabaseName $DatabaseName -WithReplace -DestinationDataDirectory $DestDataDir -DestinationLogDirectory $DestLogDir -MaintenanceSolutionBackup -RestoreTime "2021-05-26 14:00"

Expected Behavior

RESTORE DATABASE [redacted] FROM DISK = N'redacted' WITH FILE = 1, MOVE N'redacted' TO N'redacted', MOVE N'redacted' TO N'redacted', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10,
STOPAT = N'26/05/2021 14:00:00'

Actual Behavior

RESTORE DATABASE [redacted] FROM DISK = N'\redacted' WITH FILE = 1, MOVE N'redacted' TO N'redacted', MOVE N'redacted' TO N'redacted', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10,
STOPAT = N'05/26/2021 14:00:00'

Environmental information

🚨🚨🚨🚨🚨🚨🚨🚨🚨🚨
Please provide the output of the below script

PowerShell Version        : 5.1.17763.1852
dbatools latest installed : 1.0.152
Culture of OS             : fr-FR

SQL Server:

Microsoft SQL Server 2014 (SP2-GDR) (KB4505217) - 12.0.5223.6 (X64)   May 26 2019 20:36:50   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) 

Français

[niphlod]

does this happen if you pass

$restoretime = (Get-Date "2021-05-26 14:00:00")
write-host "restoretime at $(Get-Date -Date $restoretime -f s)"
.... -RestoreTime $restoretime

?

i.e. a full-blown datetime rather than a string

[WidCed]

Yes I get the same behaviour when I pass a datetime to the command.

Maybe it's the same issue as #6483 ?

[andreasjordan]

ToPointInTime of the restore SMO is a string. I think we had issues like that and fixed a lot of them. Try to find the change...

[andreasjordan]

#6911

[andreasjordan]

Line 251 of Invoke-DbaAdvancedRestore:
$restore.ToPointInTime = $RestoreTime
Maybe change to something like:
$restore.ToPointInTime = $RestoreTime.Tostring("yyyy-MM-dd HH:mm:ss.fff", [System.Globalization.CultureInfo]::InvariantCulture)
But I don't know what format is needed.

[niphlod]

ouch. ISOish (minus the localization) should work. yyyy-MM-ddTHH:mm:ss.fff is the "less error prone" of the whole bunch.

[andreasjordan]

Have a look at #6452 - some Culture settings change ":" to "." so we added this.

[niphlod]

yup, that's surely a bit to add. I was making a point towards making the format string insert the "T" between date and time parts.

you posted
$RestoreTime.Tostring("yyyy-MM-dd HH:mm:ss.fff", [System.Globalization.CultureInfo]::InvariantCulture)

I posted

yyyy-MM-ddTHH:mm:ss.fff

What I was trying to say is use

$RestoreTime.ToString("yyyy-MM-ddTHH:mm:ss.fff", [System.Globalization.CultureInfo]::InvariantCulture)

[andreasjordan]

And I have totally overlook that. Not sure about the "T" - we have to try. Do you have time to test this?

[niphlod]

T is on #6452 too. SQL handles it fine.

[niphlod]

someone already did extensive tests
https://itsalljustelectrons.blogspot.com/2017/07/STOPAT-And-Date-Formats.html
@drewfurgiuele is cited on the bottom.

tl:dr: there's no clear indication on what format is the gold standard, but ISOish with T ends up with success most of the times

[andreasjordan]

@WidCed Are you able to use this branch and test the change?
https://github.com/sqlcollaborative/dbatools/tree/7377_RestoreDbaDatabase_RestoreTime

[WidCed]

Hi,

It works:

$RestoreTime = Get-Date("28/05/2021 16:00")

Restore-DbaDatabase -SqlInstance [redacted] -Database redacted-Path redacted -MaintenanceSolution -WithReplace -DestinationDataDirectory redacted -DestinationLogDirectory redacted -RestoreTime $RestoreTime

Output:

RESTORE LOG [redacted] FROM  DISK = N'\redacted' WITH  FILE = 1,  MOVE redacted' TO N'redacted',  MOVE redacted' TO N'redacted',  NOUNLOAD,  STATS = 10,  STOPAT = N'2021-05-28T16:00:00.000'

Thank you very much !