search

dataplat / dbatools

🚀 SQL Server automation and instance migrations have never been safer, faster or freer
https://dbatools.io
MIT License
2.39k stars 787 forks source link

test-dbalastbackup dates of backup files outputs Dataplat.Dbatools.Utility.DbaDateTime[] #9337

Closed Sirwill1968 closed 1 month ago

Sirwill1968 commented 2 months ago

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

I do weekly backup restore tests using test-dbalastbackup and write the data using write-dbadbtabledata to a DB Last week I updated DBATOOLS to 2.1.14 on April 12th. My restore tests last week failed, and a test showed it was something changed and I had it make a new table. Well this weekend my report ran and I saw what apparently changed. The backup file dates now shows this in the table the scripts made / loaded. Dataplat.Dbatools.Utility.DbaDateTime[] Instead of the list of file dates. I do also ignore log backups, and do just the full/diff files.

Steps to Reproduce

# provide your command(s) executed pertaining to dbatools
# please include variable values (redacted or fake if needed) for reference

Test-DbaLastBackup -SqlInstance Server1 -Destination DWSQL -IgnoreLogBackup |Write-DbaDbTableData -SqlInstance DWSQL -Database Backups -Table Testdata

Please confirm that you are running the most recent version of dbatools

2.1.14

Other details or mentions

No response

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

Name Value

PSVersion 5.1.14393.6343
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14393.6343
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

Microsoft SQL Server 2022 (RTM-CU12) (KB5033663) - 16.0.4115.5 (X64) Mar 4 2024 08:56:10 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2022 Standard 10.0 (Build 20348: ) (Hypervisor) Microsoft SQL Server 2022 (RTM-CU12) (KB5033663) - 16.0.4115.5 (X64) Mar 4 2024 08:56:10 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2022 Standard 10.0 (Build 20348: ) (Hypervisor) Microsoft SQL Server 2022 (RTM-CU12) (KB5033663) - 16.0.4115.5 (X64) Mar 4 2024 08:56:10 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2022 Standard 10.0 (Build 20348: ) (Hypervisor) Microsoft SQL Server 2019 (RTM-CU23) (KB5030333) - 15.0.4335.1 (X64) Sep 21 2023 17:28:44 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor) Microsoft SQL Server 2019 (RTM-CU18) (KB5017593) - 15.0.4261.1 (X64) Sep 12 2022 15:07:06 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor) Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2022 Standard 10.0 (Build 20348: ) (Hypervisor) Microsoft SQL Server 2019 (RTM-CU26) (KB5035123) - 15.0.4365.2 (X64) Mar 29 2024 23:02:47 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor) Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2022 Standard 10.0 (Build 20348: ) (Hypervisor) Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2022 Standard 10.0 (Build 20348: ) (Hypervisor) Microsoft SQL Server 2019 (RTM-CU26) (KB5035123) - 15.0.4365.2 (X64) Mar 29 2024 23:02:47 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor) Microsoft SQL Server 2019 (RTM-CU26) (KB5035123) - 15.0.4365.2 (X64) Mar 29 2024 23:02:47 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor) Microsoft SQL Server 2022 (RTM-CU12-GDR) (KB5036343) - 16.0.4120.1 (X64) Mar 18 2024 12:02:14 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2022 Standard 10.0 (Build 20348: ) (Hypervisor) Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2022 Standard 10.0 (Build 20348: ) (Hypervisor) Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Mar 18 2018 09:11:49 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor) Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64) Jan 12 2022 22:30:08 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor) Microsoft SQL Server 2019 (RTM-CU26) (KB5035123) - 15.0.4365.2 (X64) Mar 29 2024 23:02:47 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor) Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor) Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64) Jan 12 2022 22:30:08 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor) Microsoft SQL Server 2019 (RTM-GDR) (KB5014356) - 15.0.2095.3 (X64) Apr 29 2022 18:00:13 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor) Microsoft SQL Server 2019 (RTM-CU26) (KB5035123) - 15.0.4365.2 (X64) Mar 29 2024 23:02:47 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor) Microsoft SQL Server 2016 (SP3) (KB5003279) - 13.0.6300.2 (X64) Aug 7 2021 01:20:37 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor) Microsoft SQL Server 2019 (RTM-CU26) (KB5035123) - 15.0.4365.2 (X64) Mar 29 2024 23:02:47 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor) Microsoft SQL Server 2019 (RTM-CU24) (KB5031908) - 15.0.4345.5 (X64) Dec 4 2023 14:44:16 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor) Microsoft SQL Server 2019 (RTM-CU26) (KB5035123) - 15.0.4365.2 (X64) Mar 29 2024 23:02:47 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor) Microsoft SQL Server 2019 (RTM-CU26) (KB5035123) - 15.0.4365.2 (X64) Mar 29 2024 23:02:47 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor) Microsoft SQL Server 2016 (SP3) (KB5003279) - 13.0.6300.2 (X64) Aug 7 2021 01:20:37 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 (Build 9600: ) (Hypervisor) Microsoft SQL Server 2016 (SP3) (KB5003279) - 13.0.6300.2 (X64) Aug 7 2021 01:20:37 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 (Build 9600: ) (Hypervisor)

.NET Framework Version

.NET Framework 4.7.4051.0

geomaxson commented 2 months ago

I can confirm same issue upon update, however noting the place it is not reading the date now is during the use of ConvertTo-DbaDataTable, as it works but the output does NOT have the date as previously. Then the Write-DbaDataTable is what fails.

potatoqualitee commented 2 months ago

Whoa, I did have to update the SqlClient library but none of these failures are hitting our tests. @FriedrichWeinmann would you have any idea?

geomaxson commented 2 months ago

Forgive my ignorance, but I am not finding where I even have a SqlClient library. I looked in the file locations for all installed modules, and ran get-modules -listavailable. Found SQLServer and SQLPS but not a library with those like dbatools has.

Sirwill1968 commented 1 month ago

I installed PS 7 latest, installed the powershell modules for DBATOOLS, it's Library is installed, and SQLServer powershell module. Using this with the latest of everything I still get "Dataplat.Dbatools.Utility.DbaDateTime[]" where the list of file date and times went before. 



``` Version              Name                                Repository           Description
-------              ----                                ----------           -----------
2.1.14               dbatools                            PSGallery            The community module that enables SQL Server Pros to automate database development and server administration
2024.4.12            dbatools.library                    PSGallery            The library that powers dbatools, the community module for SQL Server Pros
22.2.0               SqlServer                           PSGallery            This module allows SQL Server developers, administrators and business intelligence professionals to automate d…
potatoqualitee commented 1 month ago

@jpomfret was this your change or did the library break it? if yours, could you PR to handle it? 🙏🏼

jpomfret commented 1 month ago

I'll take a look @potatoqualitee - is possible 🤔

Sirwill1968 commented 1 month ago

This is weird. I did some testing to see about what pulls dates and what gives that datetime text. This code I copied right out of test-dbalastbackup line 339 and subbing in some real values.
image If I run this I get valid dates image But if I write that to a table I get the datetime text in the DB
image image It is like when I pipe it to write-table it doesn't pipe $lastbackup.start but rather just $lastbackup
If I output to format-table with all the columns specified the file dates look like this {2024-05-04 01:47:08.000, 2024-05-09 01:23:09.000} But that isn't what goes into the write-dbadbtabledata - to the DB table. I noticed that output to format-table or just normal output puts {} around it, but those are stripped out when writing to the DB table. I'm clearly not that versed in PS but am learning more. Does this pass each of the start times into dbadatetime or try to send them all? (line 593) BackupDates = dbadatetime[]

jpomfret commented 1 month ago

@potatoqualitee - I do think this is related to #9025 but I think what we need is to add some special handling for converting the array of dbadatatimes to strings or something within ConvertTo-DbaDataTable.

https://github.com/dataplat/dbatools/blob/development/public/ConvertTo-DbaDataTable.ps1#L140

Have a few things to do today but will try and get back to this later, shouldn't be too bad to convert I hope ;)

potatoqualitee commented 1 month ago

Once tests pass can anyone check this PR, see if it works for you (if you know how to do that?) if not, ill just merge it cuz it LGTM and tests will have passed.

jpomfret commented 1 month ago

Hey @potatoqualitee - testing this I get errors with the following code:

$cred = Get-Credential sqladmin
$inst = Connect-DbaInstance -SqlInstance localhost -SqlCredential $cred
$Results = Test-DbaLastBackup -SqlInstance $inst -Destination $inst -EnableException 
ConvertTo-DbaDataTable -InputObject $Results
╭─ pwsh  dbatools    development ≡  羽0ms⠀                                           72   18,09:08 
╰─ff ConvertTo-DbaDataTable -InputObject $Results
InvalidArgument: C:\GitHub\dbatools\public\ConvertTo-DbaDataTable.ps1:171
Line |
 171 |                  $value = [System.DateTime]$value.DateTime
     |                  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Cannot convert null to type "System.DateTime".
Convert-SpecialType: C:\GitHub\dbatools\public\ConvertTo-DbaDataTable.ps1:352
Line |
 352 |  … lue $property.value -Type $specialColumnsType[$property.Name] -SizeTy …
     |                              ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Cannot validate argument on parameter 'Type'. The argument "DateTime" does not belong to the set "Timespan,Size"
     | specified by the ValidateSet attribute. Supply an argument that is in the set and then try the command again.
InvalidArgument: C:\GitHub\dbatools\public\ConvertTo-DbaDataTable.ps1:171

I've started to fix these on a new branch but I haven't figured this bit out yet - will come back to it tomorrow. https://github.com/dataplat/dbatools/compare/development...convertTable

╰─ff ConvertTo-DbaDataTable -InputObject $Results
SetValueInvocationException: C:\GitHub\dbatools\public\ConvertTo-DbaDataTable.ps1:277
Line |
 277 |  …             $column.DataType = [System.Type]::GetType($converted.type …
     |                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Exception setting "DataType": "Column requires a valid DataType."
SetValueInvocationException: C:\GitHub\dbatools\public\ConvertTo-DbaDataTable.ps1:277
Line |
 277 |  …             $column.DataType = [System.Type]::GetType($converted.type …
     |                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Exception setting "DataType": "Column requires a valid DataType."
potatoqualitee commented 1 month ago

ahh damn, thank you for checking! we'll need some updated tests.

jpomfret commented 1 month ago

Think I've resolved the issue on the train back from datagrillen and I will build in some tests to prove it and keep it that way ☺️

geomaxson commented 1 month ago

Looking forward to seeing the change. Assuming it will be part of a new release, 2.1.15?Happy to test in our environment as well. Thanks,GeorgeSent from my iPhoneOn May 19, 2024, at 7:17 AM, Jess Pomfret @.***> wrote: Think I've resolved the issue on the train back from datagrillen and I will build in some tests to prove it and keep it that way ☺️

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you are subscribed to this thread.Message ID: @.***>

jpomfret commented 1 month ago

Tests against development branch: image

Tests against convertTable branch image

and the results of my test code image

I'll create a PR and lets see if the tests pass...