Closed sqlslinger closed 1 year ago
wsmelton
commented
1 year ago If you are using the command to export to XML you have to convert the DbaSize property to the raw property. Export command you are using is not aware of how our object is formatted to do that for you.
wsmelton
commented
1 year ago Please review example 2 in the Get-DbaBackupInformation docs. We do not support the file format that Export-CliXml creates. We do not support the use of PowerShell's Export-Clixml.
You need to run:
Get-DbaBackupInformation -SqlInstance MySourceServer -Database MyDb -LastFull -ExportPath .\MyDb_LastFull.xmlThat will then be a formatted file that the -Import parameter will be familar with to use.
wsmelton
commented
1 year ago If you continue to have issues with the above process please reopen the issue or you can open a discussion as well to work through it.
sqlslinger
commented
1 year ago If you are using the command to export to XML you have to convert the DbaSize property to the raw property. Export command you are using is not aware of how our object is formatted to do that for you.
Hi @wsmelton, thanks for replying and teaching me about the correct usage here.
Our goal is to use the output of Get-DbaDbBackupHistory exported to a file. We want to export the backup history from msdb of a source server, export it to a file, and use that file as the input to a process that restores the data to another server. We specifically do not want to connect to restore source server at the time of the restore for security reasons. Neither do we want to scan the headers of 2000 files found on the backup directory for the source server.
We have tried piping Get-DbaDbBackupHistory into Get-DbaBackupInformation with -ExportPath to create the xml file. Then when using Get-DbaBackupInformation with -Import and -Path to that file and piping to Restore-DbaDatabase, we still get the same error.
So how would you suggest we meet our goal?
It would seem that supporting Get-DbaDbBackupInfomation exported using Export-CliXml would be a boon to dbatools if that were possible, and it doesn't seem like it is far away. The restore itself works when doing so, only the calculation of the total size is presenting an error, and that can be resolved without much trouble.
wsmelton
commented
1 year ago Get-DbaDbBackupInformation runs Export-CliXml when it is anonymizing the data.
Thinking about this ... @Stuart-Moore @potatoqualitee @jpomfret
I don't think it would be unreasonable if we create ConvertTo-DbaBackupHistory command that only supports output from Get-DbaDbBackupHistory, and outputs the Dataplat.Dbatools.Database.BackupHistory. Then the user can determine what export format they want from there. The convert command takes care of skimming the data down to the raw form to create that object.
The backup history command already outputs that object. So basics would be @sqlslinger I think just need to offer a flag on the command that removes the properties and make the output look exactly like Get-DbaBackupInformation then you could export that to XML or whatever file format you want.
wsmelton
commented
1 year ago Will have to let it sit a few days to think about what we'd call the parameter to make it self-explanatory though :thinking:
-ExportBackupInformationThat flag would have the output be just like the object Get-DbaBackupInformation outputs and should (in theory) support:
Get-DbaDbBackupHistory ... -ExportBackupInformation | Export-CliXml somefile.xml
Get-DbaBackupInformation -Import -Path somefile.xml | Restore-DbaDatabase ... -TrustDbBackupHistoryIn reference to this PR #8780 The PR should be closed as it does not solve the issue and would require a good bit of a rewrite to many things in our backup/restore command set.
I need to perform restore of databases on a secondary server using exported backup history from a source. I also do not want to the restore workflow to scan any of the files, trust the backup history dataset.
<# against the source server export the raw data to XML #>
Get-DbaDbBackupHistory -SqlInstance sourceServer | Export-Clixml -Path C:\tmp\backupData.xml
<# import that data back on the target server without having to scan anything, this just creates the BackupHistory object that the Restore command needs to use #>
$backupHistory = Get-DbaBackupInformation -Import -Path c:\tmp\backupData.xml
$backupHistory | Restore-DbaDatabase -SqlInstance targetServer -TrustBackupHistory -WhatIfOn the latest release, 1.1.145, this works as expected via WhatIf output
The files are not being scanned using this method, only tested to see if the physical file is accessible.
sqlslinger
commented
1 year ago @wsmelton, thanks for considering this bug. Unfortunately, it seems to still be an issue with 1.1.146. Please try when running without the -WhatIf.
PS> Get-DbaDbBackupHistory -SqlInstance MyServer -Database MyDb -Last | Export-Clixml $env:temp\MyDb_LastFull.xml
PS> $bhi = Import-Clixml $env:temp\MyDb_LastFull.xml
PS> $bhi | Restore-DbaDatabase -SqlInstance MyServer -DatabaseName MyDb -TrustDbBackupHistory -ReplaceDbNameInFile -RestoredDatabaseNamePrefix test_ -WithReplace
Measure-Object : Input object "23.56 GB" is not numeric.
At C:\Users\MyUserName\Documents\WindowsPowerShell\Modules\dbatools\1.1.146\allcommands.ps1:44969 char:153
+ ... th]::Round(($backup | Measure-Object -Property TotalSize -Sum).Sum / ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidType: (23.56 GB:PSObject) [Measure-Object], PSInvalidOperationException
+ FullyQualifiedErrorId : NonNumericInputObject,Microsoft.PowerShell.Commands.MeasureObjectCommand
Measure-Object : Input object "1.31 GB" is not numeric.
At C:\Users\MyUserName\Documents\WindowsPowerShell\Modules\dbatools\1.1.146\allcommands.ps1:44970 char:164
+ ... (($backup | Measure-Object -Property CompressedBackupSize -Sum).Sum / ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidType: (1.31 GB:PSObject) [Measure-Object], PSInvalidOperationException
+ FullyQualifiedErrorId : NonNumericInputObject,Microsoft.PowerShell.Commands.MeasureObjectCommand
Measure-Object : Input object "23.56 GB" is not numeric.
At C:\Users\MyUserName\Documents\WindowsPowerShell\Modules\dbatools\1.1.146\allcommands.ps1:44975 char:149
+ ... [dbasize](($backup | Measure-Object -Property TotalSize -Sum).Sum / ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidType: (23.56 GB:PSObject) [Measure-Object], PSInvalidOperationException
+ FullyQualifiedErrorId : NonNumericInputObject,Microsoft.PowerShell.Commands.MeasureObjectCommand
Measure-Object : Input object "1.31 GB" is not numeric.
At C:\Users\MyUserName\Documents\WindowsPowerShell\Modules\dbatools\1.1.146\allcommands.ps1:44976 char:160
+ ... (($backup | Measure-Object -Property CompressedBackupSize -Sum).Sum / ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidType: (1.31 GB:PSObject) [Measure-Object], PSInvalidOperationException
+ FullyQualifiedErrorId : NonNumericInputObject,Microsoft.PowerShell.Commands.MeasureObjectCommand
ComputerName : MyServer
InstanceName : MSSQLSERVER
SqlInstance : MyServer
BackupFile : \\MyBackupServer\SQLBackups$\MyServer\MyDb\FULL\MyServer_MyDb_FULL_20230403_1
12109_1.bak,\\MyBackupServer\SQLBackups$\MyServer\MyDb\FULL\MyServer_MyDb_FUL
L_20230403_112109_2.bak
BackupFilesCount : 2
BackupSize : 0 B
CompressedBackupSize : 0 B
Database : test_MyDb
Owner : MYDOMAIN\MyUserName
DatabaseRestoreTime : 00:00:47
FileRestoreTime : 00:00:46
NoRecovery : False
RestoreComplete : True
RestoredFile : test_MyDb_Data.MDF,test_MyDb_Log.LDF
RestoredFilesCount : 2
Script : {RESTORE DATABASE [test_MyDb] FROM DISK = N'\\MyBackupServer\SQLBackups$\MyServer
\MyDb\FULL\MyServer_MyDb_FULL_20230403_112109_1.bak', DISK = N'\\USBACK035.bethel.jw.
org\SQLBackups$\MyServer\MyDb\FULL\MyServer_MyDb_FULL_20230403_112109_2.bak' WITH
FILE = 1, MOVE N'MyDb_Data' TO N'D:\Data\test_MyDb_Data.MDF', MOVE N'MyDb_Log'
TO N'F:\Logs\test_MyDb_Log.LDF', NOUNLOAD, REPLACE, STATS = 10}
RestoreDirectory : D:\Data,F:\Logs
WithReplace : True
PS> Get-Module dbatools
ModuleType Version Name ExportedCommands
---------- ------- ---- ----------------
Script 1.1.146 dbatools {Select-DbaObject, Set-DbatoolsConfig, Add-DbaAgDatabase, ...
The issue you are having is not going to go away because our library class is controlling the type values. The restore command is not written to deal with that nor should it be.
Look at the XML you are generating with:
Get-DbaDbBackupHistory -SqlInstance MyServer -Database MyDb -Last | Export-Clixml $env:temp\MyDb_LastFull.xmlThe value showing is the DbaSize type. This has to be the raw value.
Using the -Raw param will cause the command to not output that DbaSize type.
Get-DbaDbBackupHistory -SqlInstance MyServer -Database MyDb -Last -Raw | Export-Clixml $env:temp\MyDb_LastFull.xml
But I do notice we leave out the physical file data which the Restore command does need that. 🤔 @Stuart-Moore any ideas?
sqlslinger
commented
1 year ago Hi Shawn.
The -Raw flag is a great idea, but it seems to cause issue with the LastLsn object:
PS C:\Users\MyUser> Get-DbaDbBackupHistory -SqlInstance MyServer -Database MyDb -Last -Raw | Export-Clixml $env:temp\MyDb_LastFull.xml Could not compare "105310000018730500001" to "105283000020690900001 105283000020690900001". Error: "Cannot convert the "System.Object[]" value of type "System.Object[]" to type "System.Decimal"." At C:\Users\MyUser\Documents\WindowsPowerShell\Modules\dbatools\1.1.146\allcommands.ps1:23505 char:25
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : ComparisonFailureGet-DbaDbBackupHistory : Cannot process argument transformation on parameter 'LastLsn'. Cannot convert null to type "System.Numerics.BigInteger". At C:\Users\MyUser\Documents\WindowsPowerShell\Modules\dbatools\1.1.146\allcommands.ps1:23522 char:146
+ CategoryInfo : InvalidData: (:) [Get-DbaDbBackupHistory],
ParameterBindingArgumentTransformationExcepti
on
+ FullyQualifiedErrorId :
ParameterArgumentTransformationError,Get-DbaDbBackupHistoryOn Wed, Apr 5, 2023 at 3:21 PM Shawn Melton @.***> wrote:
The issue you are having is not going to go away because our library class is controlling the type values. The restore command is not written to deal with that nor should it be.
Look at the XML you are generating with:
Get-DbaDbBackupHistory -SqlInstance MyServer -Database MyDb -Last | Export-Clixml $env:temp\MyDb_LastFull.xml
The value showing is the DbaSize type. This has to be the raw value. [image: image] https://user-images.githubusercontent.com/11204251/230182127-cceded27-f9c1-425f-98cf-b03b2fa7e5f7.png
Using the -Raw param will cause the command to not output that DbaSize type.
Get-DbaDbBackupHistory -SqlInstance MyServer -Database MyDb -Last -Raw | Export-Clixml $env:temp\MyDb_LastFull.xml
[image: image] https://user-images.githubusercontent.com/11204251/230183061-f5685c0d-a908-4bc8-ab52-7cdb02b4813b.png
But I do notice we leave out the physical file data which the Restore command does need that. 🤔 @Stuart-Moore https://github.com/Stuart-Moore any ideas?
— Reply to this email directly, view it on GitHub https://github.com/dataplat/dbatools/issues/8778#issuecomment-1497998657, or unsubscribe https://github.com/notifications/unsubscribe-auth/APNFWAWNMFNKYP2SH7HLN5TW7XAZ5ANCNFSM6AAAAAAVJZR7NA . You are receiving this because you were mentioned.Message ID: @.***>
potatoqualitee
commented
1 year ago lemme see what i can do here. if i figure it out, the fix will be in dbatools 2.0
potatoqualitee
commented
1 year ago K that outta do it. Also, you can probably fix it in 1.x with:
Set-DbatoolsConfig -FullName formatting.size.style -Value b@potatoqualitee, sorry to say that Set-DbatoolsConfig does not fix the issue in 1.x. I tried both -Value b and -Value plain with Set-DbatoolsConfig cmdlet. The value apparently continues to be considered as PsObject type on export/import despite the formatting preference, and this causes Measure-Object to choke as it expects a number.
PS C:\Users\viacoboni2> Set-DbatoolsConfig -FullName formatting.size.style -Value plain
PS C:\Users\viacoboni2> Get-DbaDbBackupHistory -SqlInstance MyServer -Database restoredb -LastFull |
>> Export-Clixml -Path .\restoredb_LastFull.xml # Export backup history to XML
PS C:\Users\viacoboni2> $bhi = Import-Clixml -Path .\restoredb_LastFull.xml # Import history from XML
PS C:\Users\viacoboni2> $bhi | Measure-Object TotalSize -Sum | select Sum
Measure-Object : Input object "25300190208" is not numeric.
At line:1 char:8
+ $bhi | Measure-Object TotalSize -Sum | select Sum
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidType: (25300190208:PSObject) [Measure-Object], PSInvalidOperationException
+ FullyQualifiedErrorId : NonNumericInputObject,Microsoft.PowerShell.Commands.MeasureObjectCommand
Sum
---
Verified issue does not already exist?
I have searched and found no existing issue
What error did you receive?
Measure-Object : Input object "922.09 MB" is not numeric. At C:\Users\viacoboni2\Documents\WindowsPowerShell\Modules\dbatools\1.1.134\allcommands.ps1:69857 char:153
Measure-Object : Input object "44.76 MB" is not numeric. At C:\Users\viacoboni2\Documents\WindowsPowerShell\Modules\dbatools\1.1.134\allcommands.ps1:69858 char:164
Measure-Object : Input object "922.09 MB" is not numeric. At C:\Users\viacoboni2\Documents\WindowsPowerShell\Modules\dbatools\1.1.134\allcommands.ps1:69863 char:149
Measure-Object : Input object "44.76 MB" is not numeric. At C:\Users\viacoboni2\Documents\WindowsPowerShell\Modules\dbatools\1.1.134\allcommands.ps1:69864 char:160
Steps to Reproduce
Please confirm that you are running the most recent version of dbatools
1.1.134
Other details or mentions
I will be submitting a pull request to fix the issue.
What PowerShell host was used when producing this error
Windows PowerShell (powershell.exe)
PowerShell Host Version
Name Value
PSVersion 5.1.17763.3770
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.17763.3770
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 2019 (RTM-CU17) (KB5016394) - 15.0.4249.2 (X64) Jul 22 2022 12:11:33 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)
.NET Framework Version
PSChildName Version
Client 4.8.03761 Full 4.8.03761 Client 4.0.0.0