[Bug] Backup-DbaDatabase | Conversion failed

[jarnoj]

Environmental information

PowerShell version:

Name                           Value
----                           -----
PSVersion                      5.1.17763.1007
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.17763.1007
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1


#### dbatools Module version:

Name    : dbatools
Path    : C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.104\dbatools.psd1
Version : 1.0.104

LCID             Name             DisplayName
----             ----             -----------
1035             fi-FI            Finnish (Finland)

SQL Server:

Microsoft SQL Server 2017 (RTM-CU17) (KB4515579) - 14.0.3238.1 (X64)   Sep 13 2019 15:49:57   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 17763: ) (Hypervisor) 

us_english

Report

I am afraid this dbatools backup related issue #5749 (and its related issues: #5751 #5748 #5744 #5742 #5308 #5024) might still exist as I am still getting same symptoms with latest dbatools on my box (having Finnish locale).

I checked the related PR that tries to fix that issue and noticed that in Get-DbaBackupHistory it still has one place (line number 660) that is missing the CONVERT fix that was applied elsewhere in that PR, so I believe this missing convert might be still causing different issues with many backup related commands.

For example Backup-DbaDatabase warns me about backup failure (although it seemed to do it):

WARNING: [16.14.05][Backup-DbaDatabase] Backup Failed | Conversion failed when converting date and/or time from character string.

And that Get-DbaDbBackupHistory fails with an unhandled exception:

Exception calling "ExecuteWithResults" with "1" argument(s): "An exception occurred while executing a Transact-SQL statement or batch."
At C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.104\allcommands.ps1:22515 char:13
+             $results = $server.ConnectionContext.ExecuteWithResults($ ...
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ExecutionFailureException

Host used

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

Errors Received

PSMessageDetails      :
Exception             : System.Management.Automation.MethodInvocationException: Exception calling "ExecuteWithResults"
                        with "1" argument(s): "An exception occurred while executing a Transact-SQL statement or batch.
                        " ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred w
                        hile executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Conv
                        ersion failed when converting date and/or time from character string.
                           at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction act
                        ion, Object execObject, DataSet fillDataSet, Boolean catchException)
                           at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlComma
                        nd, Boolean retry)
                           --- End of inner exception stack trace ---
                           at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlComma
                        nd, Boolean retry)
                           at CallSite.Target(Closure , CallSite , Object , Object )
                           --- End of inner exception stack trace ---
                           at System.Management.Automation.ExceptionHandlingOps.ConvertToMethodInvocationException(Exce
                        ption exception, Type typeToThrow, String methodName, Int32 numArgs, MemberInfo memberInfo)
                           at CallSite.Target(Closure , CallSite , Object , Object )
                           at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 a
                        rg1)
                           at System.Management.Automation.Interpreter.DynamicInstruction`3.Run(InterpretedFrame frame)
                           at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedF
                        rame frame)
                           at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedF
                        rame frame)
TargetObject          :
CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
FullyQualifiedErrorId : ExecutionFailureException
ErrorDetails          :
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at Get-DbaDbBackupHistory<Process>, C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.104
                        \allcommands.ps1: line 22515
                        at <ScriptBlock>, <No file>: line 1
PipelineIterationInfo : {}

[jarnoj]

So my assumption is that this issue might get fixed by simply changing that Get-DbaBackupHistory.ps1 line 660 to have similar datetime conversion as there is now e.g. on line 424.

So ending up to be like this:
$whereArray += "backupset.backup_finish_date >= CONVERT('$($Since.ToString("yyyy-MM-ddTHH:mm:ss"))',126)"

[stale]

This issue has been automatically marked as stale because it has not had recent activity. If you would like this issue to remain open:

• Verify that you can still reproduce the issue in the latest version of dbatools
• Comment that the issue is still reproducible and include:
  • What version of dbatools you reproduced the issue on
  • What OS and version you reproduced the issue on
  • What steps you followed to reproduce the issue

[andreasjordan]

@jarnoj Can you retest please? Because the change you suggested is in the code (even if don't find the changing commit).

[klindberg70]

We experience the same error

command:
Backup-DbaDatabase -SqlInstance DBC-025\DBC025 -Path \sql-file01\test$ -Database iverdba -Type Full -CopyOnly -CompressBackup -FileCount 3
WARNING: [10.01.19][Backup-DbaDatabase] Backup Failed | Conversion failed when converting date and/or time from character string.

PS H:> GET-WinSystemLocale
LCID Name DisplayName

---

1044 nb-NO Norwegian, Bokmål (Norway)

OS Windows 2016
PSVersion 5.1.14393.3866
dbatools 1.0.123

[andreasjordan]

Can you provide more details like output of the command with -Verbose and info from $Error[0] ($ERROR[0] | SELECT *)?

[klindberg70]

error.txt

[andreasjordan]

Thanks. The error is raised from inside of Get-DbaDbBackupHistory, but catched in Backup-DbaDatabase.
Can you just run Get-DbaDbBackupHistory to help me identifying the responsible line of code?

Get-DbaDbBackupHistory -SqlInstance 'DBC-025\DBC025' -Database 'iverdba' -LastFull -IncludeCopyOnly | Sort-Object -Property End -Descending | Select-Object -First 1

[andreasjordan]

Ok, small update - we only need the first part of the statement, but -Debug output would be nice:

Get-DbaDbBackupHistory -SqlInstance 'DBC-025\DBC025' -Database 'iverdba' -LastFull -IncludeCopyOnly -Debug

[klindberg70]

Here is the debug output, thanks!
debug.txt

[andreasjordan]

Ok, to keep you up to date:
In the sql it says:

CONVERT(datetime,'1970-01-01T00.00.00',126)

But it should be:

CONVERT(datetime,'1970-01-01T00:00:00',126)

So ":" instead of ".".

The PowerShell-Code:

$sinceSqlFilter = "AND backupset.backup_finish_date >= CONVERT(datetime,'$($Since.ToString("yyyy-MM-ddTHH:mm:ss"))',126)"

So $Since.ToString("yyyy-MM-ddTHH:mm:ss") gives you the wrong string.

Can you run this in a PowerShell and have a look at the delimiter in the time:

$Since = Get-Date
$Since.ToString("yyyy-MM-ddTHH:mm:ss")

[klindberg70]

PS SQLSERVER:> $Since = Get-Date
$Since.ToString("yyyy-MM-ddTHH:mm:ss")
2020-10-06T14.57.19

[andreasjordan]

Ok, let's try to add a culture object:

$en = New-Object -TypeName System.Globalization.CultureInfo -ArgumentList 'en-US'
$Since = Get-Date
$Since.ToString("yyyy-MM-ddTHH:mm:ss", $en)

[klindberg70]

PS SQLSERVER:> $en = New-Object -TypeName System.Globalization.CultureInfo -ArgumentList 'en-US'
$Since = Get-Date
$Since.ToString("yyyy-MM-ddTHH:mm:ss", $en)
2020-10-06T15:03:53

[andreasjordan]

Ok, I'll open a pull request with a fix soon.

But I can not get your output:

$nb = New-Object system.globalization.cultureinfo(“nb-NO”)
$Since = Get-Date
$Since.ToString("yyyy-MM-ddTHH:mm:ss", $nb)

Still gives me ":".

[niphlod]

hum, and what about

$Since = Get-Date
$Since.ToString("yyyy-MM-ddTHH:mm:ss", [System.Globalization.CultureInfo]::InvariantCulture)

[andreasjordan]

@niphlod very good idea.

And now I can reproduce the issue:

$culture = Get-Culture
$culture.DateTimeFormat.TimeSeparator = '.'
Set-Culture -CultureInfo $culture
(Get-Date).ToString("yyyy-MM-ddTHH:mm:ss")
(Get-Date).ToString("yyyy-MM-ddTHH:mm:ss", [System.Globalization.CultureInfo]::InvariantCulture)

[andreasjordan]

There are other commands that use this, so we should update them all in one pull request.

[andreasjordan]

Hi @klindberg70 , thanks for helping to dig into this issue. I would like to solve the problem not only in this command, but also in other commands that do similar things. So this might take while.

As a workaround, try (Get-Culture).DateTimeFormat.TimeSeparator = ':' in the powershell session just before the Backup-DbaDatabasecommand.

[klindberg70]

The workaround works just fine.
Thank you for your work with this, I just love dbatools