search

dataplat / dbatools

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

Copy-DbaDatabase fails - Get-DbaBackupHistory finds Differential backup despite -CopyOnly #4526

Closed sqlslinger-zz closed 5 years ago

sqlslinger-zz commented 5 years ago

Before submitting a bug report:

Steps to Reproduce

/*
    Any T-SQL commands involved or used to produce test objects/data.
*/
<#
Copy-DbaDatabase -Source mySourceServer -Destination myDestServer -BackupRestore -NetworkShare \\myDestServer\backup -AllDatabases -ExcludeDatabase master,msdb,model,tempdb -ReuseSourceFolderStructure -verbose -force
#>

Copy-DbaDatabase -Source mySourceServer -Destination myDestServer -BackupRestore -NetworkShare \myDestServer\backup -AllDatabases -ExcludeDatabase master,msdb,model,tempdb -ReuseSourceFolderStructure -verbose -force

Expected Behavior

Type Name Status Notes

Database WTMaps Successful Database WTMapsDEV Successful Database WTMapsProductionCopy Successful

PLEASE NOTE: In the Verbose Output below, at 16:25:42, on the failed (first) database, there is this line: VERBOSE: [16:25:42][Get-DbaBackupHistory] Valid Differential backup

At 16:25:46, on the second successful database, there is this line (and similar for the third successful database): VERBOSE: [16:25:46][Get-DbaBackupHistory] No Diff found

In actual fact all three of the databases have DIFF backups, but of course this should not matter nor should it be considered since Copy-DbaDatabase is making CopyOnly backups.

This same error happens on Start-DbaMigration, and whether -ReuseSourceFileStructure is used or not (though without that parm the Notes column says ("Failed to restore database").

The databases all share the same simple structure - D:\Data for data files, F:\:Logs for log files, and that is the default database file locations on the target server.

Actual Behavior

PS C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules\dbatools> Copy-DbaDatabase -Source ussql007 -Destination ussqllab120 -BackupRestore -NetworkShare \ussqllab120\backup -AllDatabases -ExcludeDatabase master,msdb,model,tempdb -ReuseSourceFolderStructure -verbose -forc e VERBOSE: [16:25:38][Copy-DbaDatabase] Connecting to ussql007 VERBOSE: [16:25:38][Copy-DbaDatabase] Ensuring user databases exist (counting databases). VERBOSE: [16:25:38][Copy-DbaDatabase] Connecting to ussqllab120 VERBOSE: [16:25:38][Test-DbaSqlPath] Connecting to USSQL007. VERBOSE: [16:25:38][Test-DbaSqlPath] Connecting to USSQLLAB120. VERBOSE: [16:25:38][Copy-DbaDatabase] Performing SMO version check. VERBOSE: [16:25:38][Copy-DbaDatabase] Checking to ensure the source isn't the same as the destination. VERBOSE: [16:25:38][Copy-DbaDatabase] Checking to ensure network path is valid. VERBOSE: [16:25:38][Copy-DbaDatabase] \ussqllab120\backup share can be accessed. VERBOSE: [16:25:38][Copy-DbaDatabase] Checking to ensure server is not SQL Server 7 or below. VERBOSE: [16:25:38][Copy-DbaDatabase] Checking to ensure detach/attach is not attempted on SQL Server 2000. VERBOSE: [16:25:38][Copy-DbaDatabase] Checking to ensure SQL Server 2000 migration isn't directly attempted to SQL Server 2012. VERBOSE: [16:25:38][Copy-DbaDatabase] Warning if migration from 2005 to 2012 and above and attach/detach is used. VERBOSE: [16:25:38][Copy-DbaDatabase] Warning on different collation. VERBOSE: [16:25:38][Copy-DbaDatabase] Collation on ussql007, SQL_Latin1_General_CP1_CI_AI differs from the ussqllab120, Latin1_General_CI_AI. VERBOSE: [16:25:38][Copy-DbaDatabase] Ensuring destination server version is equal to or greater than source. VERBOSE: [16:25:38][Copy-DbaDatabase] Writing warning about filestream being enabled. VERBOSE: [16:25:38][Copy-DbaDatabase] FILESTREAM enabled on ussql007 but not ussqllab120. Databases that use FILESTREAM will be skipped. VERBOSE: [16:25:38][Copy-DbaDatabase] Building database list. VERBOSE: [16:25:38][Copy-DbaDatabase] Performing count. VERBOSE: [16:25:38][Copy-DbaDatabase] Building file structure inventory for 3 databases. VERBOSE: [16:25:39][Get-SqlFileStructure] WTMaps VERBOSE: [16:25:39][Get-SqlFileStructure] WTMapsDEV VERBOSE: [16:25:39][Get-SqlFileStructure] WTMapsProductionCopy VERBOSE: [16:25:39][Copy-DbaDatabase] ######### Database: WTMaps ######### VERBOSE: [16:25:39][Copy-DbaDatabase] Checking for accessibility. VERBOSE: [16:25:39][Test-DbaSqlPath] Connecting to USSQLLAB120. VERBOSE: [16:25:39][Copy-DbaDatabase] Checking Availability Group status. VERBOSE: [16:25:39][Copy-DbaDatabase] Started: 11/14/2018 16:25:39. VERBOSE: Performing the operation "Backup WTMaps from ussql007 and restoring" on target "ussqllab120". VERBOSE: [16:25:39][Backup-DbaDatabase] Connecting to USSQL007 VERBOSE: [16:25:39][Backup-DbaDatabase] 1 database to backup VERBOSE: [16:25:39][Backup-DbaDatabase] Backup database [WTMaps] VERBOSE: [16:25:39][Backup-DbaDatabase] Creating full backup VERBOSE: [16:25:39][Backup-DbaDatabase] Building file name VERBOSE: [16:25:39][Backup-DbaDatabase] Setting filename VERBOSE: [16:25:39][Backup-DbaDatabase] Building backup path VERBOSE: [16:25:39][Test-DbaSqlPath] Connecting to USSQL007. VERBOSE: [16:25:39][Backup-DbaDatabase] Devices added VERBOSE: [16:25:42][Get-DbaBackupHistory] Connecting to USSQL007. VERBOSE: [16:25:42][Get-DbaBackupHistory] Connecting to USSQL007. VERBOSE: [16:25:42][Get-DbaBackupHistory] Processing WTMaps VERBOSE: [16:25:42][Get-DbaBackupHistory] Executing sql query. VERBOSE: [16:25:42][Get-DbaBackupHistory] Processing as grouped output. VERBOSE: [16:25:42][Get-DbaBackupHistory] 2 result-groups found. VERBOSE: [16:25:42][Get-DbaBackupHistory] Connecting to USSQL007. VERBOSE: [16:25:42][Get-DbaBackupHistory] Processing WTMaps VERBOSE: [16:25:42][Get-DbaBackupHistory] Executing sql query. VERBOSE: [16:25:42][Get-DbaBackupHistory] Processing as grouped output. VERBOSE: [16:25:42][Get-DbaBackupHistory] 2 result-groups found. VERBOSE: [16:25:42][Get-DbaBackupHistory] Valid Differential backup VERBOSE: [16:25:42][Get-DbaBackupHistory] Connecting to USSQL007. VERBOSE: [16:25:42][Get-DbaBackupHistory] Executing sql query. VERBOSE: [16:25:42][Get-DbaBackupHistory] Processing as grouped output. VERBOSE: [16:25:42][Get-DbaBackupHistory] 3 result-groups found. VERBOSE: [16:25:42][Copy-DbaDatabase] Reuse = True. VERBOSE: [16:25:42][Restore-DbaDatabase] Changing statement timeout to infinity VERBOSE: [16:25:42][Restore-DbaDatabase] ParameterSet = Restore VERBOSE: [16:25:42][Restore-DbaDatabase] Trust Database Backup History Set VERBOSE: [16:25:42][Restore-DbaDatabase] Processing DatabaseName - WTMaps VERBOSE: [16:25:42][Select-DbaBackupInformation] Processing Db WTMaps WARNING: [16:25:42][Copy-DbaDatabase] Failure attempting to restore WTMaps to ussqllab120 VERBOSE: [16:25:42][Copy-DbaDatabase] Failed to restore WTMaps to ussqllab120. You specified -ReuseSourceFolderStructure. Does the exact same destination directory structure exist? VERBOSE: [16:25:42][Copy-DbaDatabase] Aborting routine for this database.

VERBOSE: [16:25:42][Copy-DbaDatabase] ######### Database: WTMapsDEV ######### VERBOSE: [16:25:42][Copy-DbaDatabase] Checking for accessibility. VERBOSE: [16:25:42][Test-DbaSqlPath] Connecting to USSQLLAB120. VERBOSE: [16:25:42][Copy-DbaDatabase] Checking Availability Group status. VERBOSE: Performing the operation "DROP DATABASE WTMapsDEV" on target "ussqllab120". VERBOSE: [16:25:42][Copy-DbaDatabase] WTMapsDEV already exists. -Force was specified. Dropping WTMapsDEV on ussqllab120. VERBOSE: [16:25:42][Remove-DbaDatabase] Connecting to USSQLLAB120 VERBOSE: [16:25:43][Copy-DbaDatabase] Started: 11/14/2018 16:25:42. VERBOSE: Performing the operation "Backup WTMapsDEV from ussql007 and restoring" on target "ussqllab120". VERBOSE: [16:25:43][Backup-DbaDatabase] Connecting to USSQL007 VERBOSE: [16:25:43][Backup-DbaDatabase] 1 database to backup VERBOSE: [16:25:43][Backup-DbaDatabase] Backup database [WTMapsDEV] VERBOSE: [16:25:43][Backup-DbaDatabase] Creating full backup VERBOSE: [16:25:43][Backup-DbaDatabase] Building file name VERBOSE: [16:25:43][Backup-DbaDatabase] Setting filename VERBOSE: [16:25:43][Backup-DbaDatabase] Building backup path VERBOSE: [16:25:43][Test-DbaSqlPath] Connecting to USSQL007. VERBOSE: [16:25:43][Backup-DbaDatabase] Devices added VERBOSE: [16:25:46][Get-DbaBackupHistory] Connecting to USSQL007. VERBOSE: [16:25:46][Get-DbaBackupHistory] Connecting to USSQL007. VERBOSE: [16:25:46][Get-DbaBackupHistory] Processing WTMapsDEV VERBOSE: [16:25:46][Get-DbaBackupHistory] Executing sql query. VERBOSE: [16:25:46][Get-DbaBackupHistory] Processing as grouped output. VERBOSE: [16:25:46][Get-DbaBackupHistory] 3 result-groups found. VERBOSE: [16:25:46][Get-DbaBackupHistory] Connecting to USSQL007. VERBOSE: [16:25:46][Get-DbaBackupHistory] Processing WTMapsDEV VERBOSE: [16:25:46][Get-DbaBackupHistory] Executing sql query. VERBOSE: [16:25:46][Get-DbaBackupHistory] Processing as grouped output. VERBOSE: [16:25:46][Get-DbaBackupHistory] 2 result-groups found. VERBOSE: [16:25:46][Get-DbaBackupHistory] No Diff found VERBOSE: [16:25:46][Get-DbaBackupHistory] Connecting to USSQL007. VERBOSE: [16:25:46][Get-DbaBackupHistory] Executing sql query. VERBOSE: [16:25:46][Get-DbaBackupHistory] Processing as grouped output. VERBOSE: [16:25:46][Get-DbaBackupHistory] 3 result-groups found. VERBOSE: [16:25:46][Copy-DbaDatabase] Reuse = True. VERBOSE: [16:25:46][Restore-DbaDatabase] Changing statement timeout to infinity VERBOSE: [16:25:46][Restore-DbaDatabase] ParameterSet = Restore VERBOSE: [16:25:46][Restore-DbaDatabase] Trust Database Backup History Set VERBOSE: [16:25:46][Restore-DbaDatabase] Processing DatabaseName - WTMapsDEV VERBOSE: [16:25:46][Select-DbaBackupInformation] Processing Db WTMapsDEV VERBOSE: [16:25:46][Select-DbaBackupInformation] Setting LogBaseLSN VERBOSE: [16:25:46][Format-DbaBackupInformation] Starting VERBOSE: [16:25:46][Format-DbaBackupInformation] String passed in for DB rename VERBOSE: [16:25:46][Format-DbaBackupInformation] New DbName (String) = WTMapsDEV VERBOSE: [16:25:46][Format-DbaBackupInformation] 1 PhysicalName = D:\Data\WTMapsDEV_Data.MDF VERBOSE: [16:25:46][Format-DbaBackupInformation] PhysicalName = D:\Data\WTMapsDEV_Data.MDF VERBOSE: [16:25:46][Format-DbaBackupInformation] 1 PhysicalName = F:\Logs\WTMapsDEV_Log.LDF VERBOSE: [16:25:46][Format-DbaBackupInformation] PhysicalName = F:\Logs\WTMapsDEV_Log.LDF VERBOSE: [16:25:46][Restore-DbaDatabase] VerifyOnly = False VERBOSE: [16:25:46][Get-DbaDbPhysicalFile] Connecting to USSQLLAB120 VERBOSE: [16:25:46][Test-DbaBackupInformation] Testing restore for WTMapsDEV VERBOSE: [16:25:46][Get-DbaDatabase] Connecting to USSQLLAB120. VERBOSE: [16:25:46][Test-DbaBackupInformation] VerifyOnly = False VERBOSE: [16:25:46][Test-DbaSqlPath] Connecting to USSQLLAB120. VERBOSE: [16:25:46][Test-DbaSqlPath] Connecting to USSQLLAB120. VERBOSE: [16:25:46][Test-DbaSqlPath] Connecting to USSQLLAB120. VERBOSE: [16:25:46][Test-DbaSqlPath] Connecting to USSQLLAB120. VERBOSE: [16:25:46][Test-DbaLsnChain] Testing LSN Chain VERBOSE: [16:25:46][Test-DbaLsnChain] Testing LSN Chain - Type Type VERBOSE: [16:25:46][Test-DbaLsnChain] Passed LSN Chain checks VERBOSE: [16:25:46][Test-DbaBackupInformation] Marking WTMapsDEV as verified VERBOSE: [16:25:46][Restore-DbaDatabase] WTMapsDEV passed testing VERBOSE: [16:25:46][Restore-DbaDatabase] Passing in to restore VERBOSE: [16:25:46][Invoke-DbaAdvancedRestore] Performing restore action VERBOSE: [16:25:56][Invoke-DbaAdvancedRestore] Succeeded, Closing Server connection VERBOSE: [16:25:56][Copy-DbaDatabase] Successfully restored WTMapsDEV to ussqllab120. VERBOSE: [16:25:56][Copy-DbaDatabase] Deleting \ussqllab120\backup\WTMapsDEV_201811141625-1-of-3.bak. VERBOSE: [16:25:56][Copy-DbaDatabase] Deleting \ussqllab120\backup\WTMapsDEV_201811141625-2-of-3.bak. VERBOSE: [16:25:56][Copy-DbaDatabase] Deleting \ussqllab120\backup\WTMapsDEV_201811141625-3-of-3.bak. VERBOSE: [16:25:56][Copy-DbaDatabase] Updating database owner to sa. VERBOSE: [16:25:56][Set-DbaDatabaseOwner] Connecting to USSQLLAB120. VERBOSE: [16:25:56][Set-DbaDatabaseOwner] Updating 1 database(s). VERBOSE: [16:25:56][Set-DbaDatabaseOwner] Setting database owner for WTMapsDEV to sa on USSQLLAB120. VERBOSE: Performing the operation "Updating Trustworthy on WTMapsDEV" on target "ussqllab120". VERBOSE: [16:25:56][Copy-DbaDatabase] Successfully updated Trustworthy to True for WTMapsDEV on ussqllab120 VERBOSE: [16:25:56][Copy-DbaDatabase] Finished: 11/14/2018 16:25:56. VERBOSE: [16:25:56][Copy-DbaDatabase] Elapsed time: 00:00:13. VERBOSE: [16:25:56][Copy-DbaDatabase] ######### Database: WTMapsProductionCopy ######### VERBOSE: [16:25:56][Copy-DbaDatabase] Checking for accessibility. VERBOSE: [16:25:56][Test-DbaSqlPath] Connecting to USSQLLAB120. VERBOSE: [16:25:56][Copy-DbaDatabase] Checking Availability Group status. VERBOSE: Performing the operation "DROP DATABASE WTMapsProductionCopy" on target "ussqllab120". VERBOSE: [16:25:56][Copy-DbaDatabase] WTMapsProductionCopy already exists. -Force was specified. Dropping WTMapsProductionCopy on ussqllab120. VERBOSE: [16:25:56][Remove-DbaDatabase] Connecting to USSQLLAB120 VERBOSE: [16:25:56][Copy-DbaDatabase] Started: 11/14/2018 16:25:56. VERBOSE: Performing the operation "Backup WTMapsProductionCopy from ussql007 and restoring" on target "ussqllab120". VERBOSE: [16:25:56][Backup-DbaDatabase] Connecting to USSQL007 VERBOSE: [16:25:56][Backup-DbaDatabase] 1 database to backup VERBOSE: [16:25:56][Backup-DbaDatabase] Backup database [WTMapsProductionCopy] VERBOSE: [16:25:56][Backup-DbaDatabase] Creating full backup VERBOSE: [16:25:56][Backup-DbaDatabase] Building file name VERBOSE: [16:25:56][Backup-DbaDatabase] Setting filename VERBOSE: [16:25:56][Backup-DbaDatabase] Building backup path VERBOSE: [16:25:56][Test-DbaSqlPath] Connecting to USSQL007. VERBOSE: [16:25:56][Backup-DbaDatabase] Devices added VERBOSE: [16:25:59][Get-DbaBackupHistory] Connecting to USSQL007. VERBOSE: [16:25:59][Get-DbaBackupHistory] Connecting to USSQL007. VERBOSE: [16:25:59][Get-DbaBackupHistory] Processing WTMapsProductionCopy VERBOSE: [16:25:59][Get-DbaBackupHistory] Executing sql query. VERBOSE: [16:25:59][Get-DbaBackupHistory] Processing as grouped output. VERBOSE: [16:25:59][Get-DbaBackupHistory] 3 result-groups found. VERBOSE: [16:25:59][Get-DbaBackupHistory] Connecting to USSQL007. VERBOSE: [16:25:59][Get-DbaBackupHistory] Processing WTMapsProductionCopy VERBOSE: [16:25:59][Get-DbaBackupHistory] Executing sql query. VERBOSE: [16:25:59][Get-DbaBackupHistory] Processing as grouped output. VERBOSE: [16:25:59][Get-DbaBackupHistory] 2 result-groups found. VERBOSE: [16:25:59][Get-DbaBackupHistory] No Diff found VERBOSE: [16:25:59][Get-DbaBackupHistory] Connecting to USSQL007. VERBOSE: [16:25:59][Get-DbaBackupHistory] Executing sql query. VERBOSE: [16:26:00][Get-DbaBackupHistory] Processing as grouped output. VERBOSE: [16:26:00][Get-DbaBackupHistory] 808 result-groups found. VERBOSE: [16:26:02][Copy-DbaDatabase] Reuse = True. VERBOSE: [16:26:02][Restore-DbaDatabase] Changing statement timeout to infinity VERBOSE: [16:26:02][Restore-DbaDatabase] ParameterSet = Restore VERBOSE: [16:26:02][Restore-DbaDatabase] Trust Database Backup History Set VERBOSE: [16:26:02][Restore-DbaDatabase] Processing DatabaseName - WTMapsProductionCopy VERBOSE: [16:26:02][Select-DbaBackupInformation] Processing Db WTMapsProductionCopy VERBOSE: [16:26:02][Select-DbaBackupInformation] Setting LogBaseLSN VERBOSE: [16:26:02][Format-DbaBackupInformation] Starting VERBOSE: [16:26:02][Format-DbaBackupInformation] String passed in for DB rename VERBOSE: [16:26:02][Format-DbaBackupInformation] New DbName (String) = WTMapsProductionCopy VERBOSE: [16:26:02][Format-DbaBackupInformation] 1 PhysicalName = D:\Data\WTMapsProductionCopy_Data.MDF VERBOSE: [16:26:02][Format-DbaBackupInformation] PhysicalName = D:\Data\WTMapsProductionCopy_Data.MDF VERBOSE: [16:26:02][Format-DbaBackupInformation] 1 PhysicalName = F:\Logs\WTMapsProductionCopy_Log.LDF VERBOSE: [16:26:02][Format-DbaBackupInformation] PhysicalName = F:\Logs\WTMapsProductionCopy_Log.LDF VERBOSE: [16:26:02][Restore-DbaDatabase] VerifyOnly = False VERBOSE: [16:26:02][Get-DbaDbPhysicalFile] Connecting to USSQLLAB120 VERBOSE: [16:26:02][Test-DbaBackupInformation] Testing restore for WTMapsProductionCopy VERBOSE: [16:26:02][Get-DbaDatabase] Connecting to USSQLLAB120. VERBOSE: [16:26:02][Test-DbaBackupInformation] VerifyOnly = False VERBOSE: [16:26:02][Test-DbaSqlPath] Connecting to USSQLLAB120. VERBOSE: [16:26:02][Test-DbaSqlPath] Connecting to USSQLLAB120. VERBOSE: [16:26:02][Test-DbaSqlPath] Connecting to USSQLLAB120. VERBOSE: [16:26:02][Test-DbaSqlPath] Connecting to USSQLLAB120. VERBOSE: [16:26:02][Test-DbaLsnChain] Testing LSN Chain VERBOSE: [16:26:02][Test-DbaLsnChain] Testing LSN Chain - Type Type VERBOSE: [16:26:02][Test-DbaLsnChain] Passed LSN Chain checks VERBOSE: [16:26:02][Test-DbaBackupInformation] Marking WTMapsProductionCopy as verified VERBOSE: [16:26:02][Restore-DbaDatabase] WTMapsProductionCopy passed testing VERBOSE: [16:26:02][Restore-DbaDatabase] Passing in to restore VERBOSE: [16:26:02][Invoke-DbaAdvancedRestore] Performing restore action VERBOSE: [16:26:14][Invoke-DbaAdvancedRestore] Succeeded, Closing Server connection VERBOSE: [16:26:14][Copy-DbaDatabase] Successfully restored WTMapsProductionCopy to ussqllab120. VERBOSE: [16:26:14][Copy-DbaDatabase] Deleting \ussqllab120\backup\WTMapsProductionCopy_201811141625-1-of-3.bak. VERBOSE: [16:26:14][Copy-DbaDatabase] Deleting \ussqllab120\backup\WTMapsProductionCopy_201811141625-2-of-3.bak. VERBOSE: [16:26:14][Copy-DbaDatabase] Deleting \ussqllab120\backup\WTMapsProductionCopy_201811141625-3-of-3.bak. VERBOSE: [16:26:14][Copy-DbaDatabase] Updating database owner to sa. VERBOSE: [16:26:14][Set-DbaDatabaseOwner] Connecting to USSQLLAB120. VERBOSE: [16:26:14][Set-DbaDatabaseOwner] Updating 1 database(s). VERBOSE: [16:26:14][Set-DbaDatabaseOwner] Setting database owner for WTMapsProductionCopy to sa on USSQLLAB120. VERBOSE: Performing the operation "Updating Trustworthy on WTMapsProductionCopy" on target "ussqllab120". VERBOSE: [16:26:14][Copy-DbaDatabase] Successfully updated Trustworthy to True for WTMapsProductionCopy on ussqllab120 VERBOSE: [16:26:14][Copy-DbaDatabase] Finished: 11/14/2018 16:26:14. VERBOSE: [16:26:14][Copy-DbaDatabase] Elapsed time: 00:00:17. Type Name Status Notes

Database WTMaps Failed Failed to restore. ReuseSourceFolderStructure was specified, verify same directory structure exist on destination. Database WTMapsDEV Successful Database WTMapsProductionCopy Successful

PS C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules\dbatools>

Environmental data

Name Value

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

PS C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules\dbatools>

TargetServer: Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

potatoqualitee commented 5 years ago

Thank you for the report. @Stuart-Moore - I could set it to ignore diffs in the wrapper commands but want to make sure there's not an underlying issue.

Stuart-Moore commented 5 years ago

$CopyOnly wasn't being passed through in the internal call in Get-DbaBackupHistory. It is now.

Fixed in PR #4675

potatoqualitee commented 5 years ago

:bananadance: thank you 💯 !

sqlslinger-zz commented 5 years ago

Thanks for updating this. Has this been released to development zip file or master zip? I've downloaded both and still have the problem...

sqlslinger-zz commented 5 years ago

Version 0.9.533

sqlslinger-zz commented 5 years ago

Chrissy and Stuart,

I don't think this got fixed. It should be relatively easy to test. Create a database, do a full backup then a diff backup on it, then run Copy-DbaDatabase from that server and using that database to a different server. See if it tries to get the diff backup or if it correctly uses the CopyOnly backup.

Vince

On Thu, Nov 15, 2018 at 5:02 PM Chrissy LeMaire notifications@github.com wrote:

Thank you for the report. @Stuart-Moore https://github.com/Stuart-Moore

  • I could set it to ignore diffs in the wrapper commands but want to make sure there's not an underlying issue.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/sqlcollaborative/dbatools/issues/4526#issuecomment-439206345, or mute the thread https://github.com/notifications/unsubscribe-auth/AQ7Ww7TFndMlXTFoKwcODFqHhWQbELoXks5uveRugaJpZM4YerXI .

sqlslinger-zz commented 5 years ago

OK more detail on this problem, and sorry it is coming in drips and drabs. Please reopen this bug.

Our database that is failing when using Copy-DbaDatabase has several recovery chains being pointed out by Get-DbaBackupHistory, The fork that it chooses to report upon has IsCopyOnly = False, as it chooses the last Full backup that was not a CopyOnly (and has a Diff backup after it in the chain) despite the -IncludeCopyOnly parm. But, that was not the most recent full backup according to time, since the -CopyOnly backup that happened just before (from the Copy-DbaDatabase command) should be the one being reported on as the -LastFull backup.

What possible solutions exist for this issue?

  1. Change the -LastFull (and -Last etc) logic to use the last by time not lsn. However, there is this comment in the code:

    recap for future editors (as this has been discussed over

    and over):

    - original editors (from hereon referred as "we") rank

    over backupset.last_lsn desc, backupset.backup_finish_date desc for a good reason: DST

    all times are recorded with the timezone of the server

          #   - we thought about ranking over backupset.backup_set_id

    desc, backupset.last_lsn desc, backupset.backup_finish_date desc

    but there is no explicit documentation about "when" a

    row gets inserted into backupset. Theoretically it could

    happen that backup_set_id for the same database has not

    the same order of last_lsn.

    - given ultimately to restore something lsn IS the source

    of truth, we decided to trust that and only that

    - we know that sometimes it happens to drop a database

    without deleting the history. Assuming then to create a database with the same name,

    and given the lsn are composed in the first part by the

    VLF SeqID, it happens seldomly that for the same database_name backupset holds

    last_lsn out of order. To avoid this behaviour, we

    filter by database_guid choosing the guid that has MAX(backup_finish_date), as we know

    last_lsn cannot be out-of-order for the same database,

    and the same database cannot have different database_guid So if I understand that correctly, lsn keeps the proper order within a recovery fork, but the forks themselves are not ordered according to time, thus the issue. If there is a way to correctly order the recovery forks by time, this might be solved.

  2. Have a parm that is distinct from -IncludeCopyOnly, such as -JustCopyOnly (-OnlyCopyOnly? :-) ). I feel the results given with -IncludeCopyOnly make sense if you want to know about all backups ever, but not if are focusing exclusively on the CopyOnly backup as Copy-DbaDatabase wants to do.

Here, notice that the returned results are not from the most recent

backups PS C:\Windows\system32> Get-DbaBackupHistory -SqlInstance . -Database WTMaps -IncludeCopyOnly -lastfull | select -first 2 | fl WARNING: [16:04:42][Get-DbaBackupHistory] Found backups from multiple recovery forks for WTMaps on NP:., this may affect your results WARNING: [16:04:42][Get-DbaBackupHistory] Between 06/18/2017 01:00:03/267000000042700037 and 11/27/2017 16:10:00/299000000367000037 was on Recovery Fork GUID d88e05ab-7d37-4504-a89c-28629280a50f (69 backups) WARNING: [16:04:42][Get-DbaBackupHistory] Between 11/27/2017 17:31:39/299000000367000037 and 11/28/2018 11:33:42/494000000088100037 was on Recovery Fork GUID 27c93885-d897-41cc-ad1a-655d0caa588c (467 backups) WARNING: [16:04:42][Get-DbaBackupHistory] Between 11/08/2015 10:00:29/1897000000619600057 and 11/11/2015 00:05:14/1901000000464300057 was on Recovery Fork GUID 9c8283a8-5fd2-4c49-bbaa-9145e235105f (2 backups) WARNING: [16:04:42][Get-DbaBackupHistory] Between 10/21/2017 00:05:10/510000002469900037 and 11/27/2017 00:05:20/584000004942600037 was on Recovery Fork GUID 69fa9415-386b-482e-9c8a-a8f53e479c04 (44 backups) WARNING: [16:04:42][Get-DbaBackupHistory] Between 11/11/2015 13:25:53/0 and 10/20/2017 11:35:58/150000000635000037 was on Recovery Fork GUID d2f4fa54-63b9-4ac1-9300-d451181da07c (34611 backups)

ComputerName : (removed) InstanceName : MSSQLSERVER SqlInstance : (removed) Database : WTMaps UserName : (removed) Start : 11/26/2017 1:00:14 AM End : 11/26/2017 1:00:40 AM Duration : 00:00:26 Path : {\USBACK013\SQLFULL3$\USSQL007\WTMaps\FULL\USSQL007_WTMaps_FULL_20171126_010014_1.bak,

\USBACK013\SQLFULL3$\USSQL007\WTMaps\FULL\USSQL007_WTMaps_FULL_20171126_010014_2.bak} TotalSize : 678.13 MB CompressedBackupSize : 451.81 MB CompressionRatio : 1.5 Type : Full BackupSetId : 79093 DeviceType : Disk Software : Microsoft SQL Server FullName : {\USBACK013\SQLFULL3$\USSQL007\WTMaps\FULL\USSQL007_WTMaps_FULL_20171126_010014_1.bak,

\USBACK013\SQLFULL3$\USSQL007\WTMaps\FULL\USSQL007_WTMaps_FULL_20171126_010014_2.bak} FileList : {@{FileType=D; LogicalName=WTMaps; PhysicalName=D:\Data\WTMaps_Data.MDF}, @{FileType=L; LogicalName=WTMaps_log; PhysicalName=F:\Logs\WTMaps_Log.LDF}} Position : 1 FirstLsn : 584000004942600037 DatabaseBackupLsn : 533000000052000037 CheckpointLsn : 584000004942600037 LastLsn : 584000004944500001 SoftwareVersionMajor : 12 IsCopyOnly : False LastRecoveryForkGUID : 69fa9415-386b-482e-9c8a-a8f53e479c04 RecoveryModel : SIMPLE

On this command, notice that if I limit the results to the last week or

so with -Since, I now find the CopyOnly backup I wanted to see: PS C:\Windows\system32> Get-DbaBackupHistory -SqlInstance . -Database WTMaps -IncludeCopyOnly -Since 11/20/2018 -LastFull | select -first 2 | fl

ComputerName : (removed) InstanceName : MSSQLSERVER SqlInstance : (removed) Database : WTMaps UserName : (removed) Start : 11/28/2018 11:33:37 AM End : 11/28/2018 11:33:42 AM Duration : 00:00:05 Path : {\USSQLLAB120\backup\WTMaps_FULL_COPY_ONLY20181128.bak} TotalSize : 348.11 MB CompressedBackupSize : 228.51 MB CompressionRatio : 1.52 Type : Full BackupSetId : 99313 DeviceType : Disk Software : Microsoft SQL Server FullName : {\USSQLLAB120\backup\WTMaps_FULL_COPY_ONLY20181128.bak} FileList : {@{FileType=D; LogicalName=WTMaps; PhysicalName=D:\Data\WTMaps_Data.MDF}, @{FileType=L; LogicalName=WTMaps_log; PhysicalName=F:\Logs\WTMaps_Log.LDF}} Position : 1 FirstLsn : 494000000094900001 DatabaseBackupLsn : 494000000088100037 CheckpointLsn : 494000000094900001 LastLsn : 494000000095200001 SoftwareVersionMajor : 12 IsCopyOnly : True LastRecoveryForkGUID : 27c93885-d897-41cc-ad1a-655d0caa588c RecoveryModel : SIMPLE

On Wed, Nov 28, 2018 at 11:19 AM Vince Iacoboni vince.iacoboni@gmail.com wrote:

Chrissy and Stuart,

I don't think this got fixed. It should be relatively easy to test. Create a database, do a full backup then a diff backup on it, then run Copy-DbaDatabase from that server and using that database to a different server. See if it tries to get the diff backup or if it correctly uses the CopyOnly backup.

Vince

On Thu, Nov 15, 2018 at 5:02 PM Chrissy LeMaire notifications@github.com wrote:

Thank you for the report. @Stuart-Moore https://github.com/Stuart-Moore

  • I could set it to ignore diffs in the wrapper commands but want to make sure there's not an underlying issue.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/sqlcollaborative/dbatools/issues/4526#issuecomment-439206345, or mute the thread https://github.com/notifications/unsubscribe-auth/AQ7Ww7TFndMlXTFoKwcODFqHhWQbELoXks5uveRugaJpZM4YerXI .

Stuart-Moore commented 5 years ago

Or we could make sure that the current recoveryforkGUID is being passed back into the history function.

PR imminent

sqlslinger-zz commented 5 years ago

Yes even if you record the timestamp before the copy only backup in Copy-DbaDatabase, then pass that timestamp as the -Since parameter to Get-DbaDatabase it would ensure you get the most recent.

On Thu, Nov 29, 2018, 9:51 AM Stuart Moore <notifications@github.com wrote:

Or we could make sure that the current recoveryforkGUID is being passed back into the history function.

PR imminent

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/sqlcollaborative/dbatools/issues/4526#issuecomment-442861097, or mute the thread https://github.com/notifications/unsubscribe-auth/AQ7Wwx2fW3HR0UCfjQByuFbNuTDz2Wg0ks5uz_R6gaJpZM4YerXI .

Stuart-Moore commented 5 years ago

Nope, we've already got the recoveryForkguid so we'll just pass that back in.

If you're doing a restore at the same time as the copy then you'll break it, but you'll it a lot more than just that ;)

sqlslinger-zz commented 5 years ago

Hi Stuart,

Don't mean to rush you, but we are looking at a deploy of hundreds of servers through Octopus where we would like to use Copy-DbaDatabase. We are preparing the install this week. Any chance you could provide an ETA on the RecoveryForkGuid fix for Get-DbaBackupHistory? Thanks and again don't mean to rush, you guys do this for the good of the community and you have your own lives and work. What you do is much appreciated!

Vince

On Thu, Nov 29, 2018 at 10:15 AM Stuart Moore notifications@github.com wrote:

Nope, we've already got the recoveryForkguid so we'll just pass that back in.

If you're doing a restore at the same time as the copy then you'll break it, but you'll it a lot more than just that ;)

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/sqlcollaborative/dbatools/issues/4526#issuecomment-442870025, or mute the thread https://github.com/notifications/unsubscribe-auth/AQ7Ww131usaUEYjMr4sQupZXi-5GhcFSks5uz_oRgaJpZM4YerXI .

sqlslinger-zz commented 5 years ago

Stuart, any update on this bug?

Stuart-Moore commented 5 years ago

Sorry, it actually got pushed in a while ago. Can you try with a recent copy of master (or dev branch) please

potatoqualitee commented 5 years ago

will close unless it's not fixed, please let us know @sqlslinger and thank you for fixing, @Stuart-Moore !

Stuart-Moore commented 5 years ago

New year same functions. Just I'll have a look

--
Stuart Moore

On 31 December 2018 at 18:43:31, Chrissy LeMaire (notifications@github.com(mailto:notifications@github.com)) wrote:

will close unless it's not fixed, please let us know @sqlslinger(https://github.com/sqlslinger) and thank you for fixing, @Stuart-Moore(https://github.com/Stuart-Moore) !

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub(https://github.com/sqlcollaborative/dbatools/issues/4526#issuecomment-450679140), or mute the thread(https://github.com/notifications/unsubscribe-auth/AFWoZoN8YKe62tRWS19yX11elN8LxB4wks5u-lrTgaJpZM4YerXI).