Copy-DbaDatabase sometimes chooses incorrect path for restore step

[Bendy22]

Bug Report

I am using Copy-DbaDatabase to migrate several databases from clustered instances of SQL 2008 R2 to SQL 2016. Most of the databases work fine, but there are a few on each instance that fail. The backup step works fine and backs up to the -NetworkShare path I provide. But for a few databases, they do not use the -NetworkShare path for the restore step and seem to use some old path that the original database was restored from on the source instance.

General Troubleshooting steps

Copy-DbaDatabase -Source DCPTSQLC01INT03.decepticons.amsod\EIQ -Destination AP11DBC01N09 -Database CustomerDefault -BackupRestore -NetworkShare \\backupa.gc.local\source11\DCPT -Force

I'm using -Force so I don't have to delete the destination database that I have restored manually in the past in case the command actually completes.

The basic error is:
WARNING: [Invoke-DbaAdvancedRestore][15:18:04] Failed to restore db CustomerDefault, stopping | Exception calling "SqlRestore" with "1" argument(s): "Restore failed for Server 'AP11DBC01N09'. "

Running $error[0] | select * gives me

Type                     Name            Status Notes                     
----                     ----            ------ -----                     
Database (BackupRestore) CustomerDefault Failed Failed to restore database
writeErrorStream      : True
PSMessageDetails      : 
Exception             : System.Exception: Exception calling "SqlRestore" with "1" argument(s): "Restore failed for Server 'AP11DBC01N09'. " ---> System.Management.Automation.MethodInvocationException: Exception 
                        calling "SqlRestore" with "1" argument(s): "Restore failed for Server 'AP11DBC01N09'. " ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Restore failed for Server 
                        'AP11DBC01N09'.  ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> 
                        System.Data.SqlClient.SqlException: Cannot open backup device 'J:\Microsoft SQL Server\MSSQL10_50.EIQ\MSSQL\Backup\CustomerDefault_EIQRestore'. Operating system error 3(The system cannot 
                        find the path specified.).
                        RESTORE DATABASE is terminating abnormally.
                           at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
                           at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
                           --- End of inner exception stack trace ---
                           at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
                           at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType, Boolean retry)
                           at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
                           at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
                           --- End of inner exception stack trace ---
                           at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
                           at CallSite.Target(Closure , CallSite , Object , Object )
                           --- End of inner exception stack trace ---
                           at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
                           at Invoke-DbaAdvancedRestore<End>(Closure , FunctionContext )
                           --- End of inner exception stack trace ---
TargetObject          : 
CategoryInfo          : NotSpecified: (:) [Write-Error], Exception
FullyQualifiedErrorId : dbatools_Invoke-DbaAdvancedRestore,Stop-Function
ErrorDetails          : Exception calling "SqlRestore" with "1" argument(s): "Restore failed for Server 'AP11DBC01N09'. "
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at Stop-Function, <No file>: line 193
                        at Invoke-DbaAdvancedRestore<End>, <No file>: line 293
                        at Restore-DbaDatabase<End>, <No file>: line 662
                        at Copy-DbaDatabase<Process>, <No file>: line 1071
                        at <ScriptBlock>, <No file>: line 1
PipelineIterationInfo : {0, 1}

• Verified running the latest release of dbatools?
• Verified errors are not related to permissions?
• Can duplicate in new/clean PowerShell session (clean = powershell -NoProfile)?

Version Information

• Operating System (Name|Version): Windows Server 2012 R2
• PowerShell Version: 5.1.14409.1012
• SQL Server (Edition|Version): Source: 2008 R2 Destination: 2016

Steps to Reproduce

Run the above Copy-DbaDatabase command on the misbehaving databases and it will fail every time.

dbatoolslog.zip

• Attach any screenshots (if possible/allowed)
• Attach logs generated by dbatools (Get-DbatoolsLog)
• Attach output from PowerShell console (if possible/allowed)

Problem to solve

Copy-DbaDatabase should always use the path given in the -NetworkShare parameter for the restore step.

Additional information

I believe the path in the large error above is a full file path and not a folder path. When I run this command on the other misbehaving databases, I get paths terminating in .bak files. So I believe CustomerDefault_EIQRestore was a filename with no extension and not a folder.

[Stuart-Moore]

Hi @Bendy22 could you run Get-DbaBackupHistory -LastFull against the database please and let us know the results.

And then Get-DbaBackupHistory -LastFull -IncludeCopyOnly

My guess is that it's getting the path of the real full backup rather than the one that Copy-DbaDatabase is performing.

Tests may not work if there's been another full since the last fail. Don't know if you'd be able to rerun the copy if so?

[Bendy22]

PS C:\Users\bdavis> Get-DbaBackupHistory -LastFull -SqlInstance DCPTSQLC01INT03.decepticons.amsod\EIQ -Database CustomerDefault

SqlInstance         Database        Type TotalSize DeviceType Start                   Duration End                    
-----------         --------        ---- --------- ---------- -----                   -------- ---                    
DCPTSQLC01INT03\EIQ CustomerDefault Full 3.06 MB   Disk       2012-05-20 22:09:09.000 00:00:02 2012-05-20 22:09:11.000



PS C:\Users\bdavis> Get-DbaBackupHistory -LastFull -SqlInstance DCPTSQLC01INT03.decepticons.amsod\EIQ -Database CustomerDefault -IncludeCopyOnly

SqlInstance         Database        Type TotalSize DeviceType Start                   Duration End                    
-----------         --------        ---- --------- ---------- -----                   -------- ---                    
DCPTSQLC01INT03\EIQ CustomerDefault Full 18.08 MB  Disk       2012-07-19 08:17:51.000 00:00:00 2012-07-19 08:17:51.000

These databases do not get regular backups. They are QA databases and I guess my predecessors decided they can restore from production or whatnot if need be. This fact may be part of the issue, but Copy-DbaDatabase takes a copy-only backup and most of the other databases work even though they seem to be in the same boat.

Thanks for looking at this! I update my email in github so I should get notifications now whereas I didn't get notification for your update yesterday.

[Stuart-Moore]

Hi,
not forgotten, just having trouble reproing which means I'm guessing. Think I know where it's happening, jsut trying come up with a solid solution

[Bendy22]

Running into this issue again. I will attempt separate backup and restore steps for the databases that suffer from this problem for now. Thanks in advance for your help on getting this fixed.

[duncfair]

same problem. The restore process is attempting to find the backup from the last sceduled backup location rather than the network share specified in the command. Never had any problems with this until I updated DBATools...

writeErrorStream : True
PSMessageDetails :
Exception : System.Exception: Exception calling "SqlRestore" with "1" argument(s): "Restore failed for Server 'SQL2012FUSION'. " ---> System.Management.Automation.MethodInvocationException: Exception calling
"SqlRestore" with "1" argument(s): "Restore failed for Server 'SQL2012FUSION'. " ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Restore failed for Server 'SQL2012FUSION'. --->
Microsoft.SqlServer.Management.Smo.SmoException: System.Data.SqlClient.SqlError: Cannot open backup device 'D:\MSSQL11.MSSQLSERVER\MSSQL\Backup\PandaXP.2018.0201.bak'. Operating system error 21(The
device is not ready.).
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages, Boolean retry)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
at CallSite.Target(Closure , CallSite , Object , Object )
--- End of inner exception stack trace ---
at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
--- End of inner exception stack trace ---
TargetObject :
CategoryInfo : NotSpecified: (:) [Write-Error], Exception
FullyQualifiedErrorId : dbatools_Invoke-DbaAdvancedRestore,Stop-Function
ErrorDetails : Exception calling "SqlRestore" with "1" argument(s): "Restore failed for Server 'SQL2012FUSION'. "
InvocationInfo : System.Management.Automation.InvocationInfo
ScriptStackTrace : at Stop-Function, C:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.334\allcommands.ps1: line 91108
at Invoke-DbaAdvancedRestore, C:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.334\allcommands.ps1: line 46347
at Restore-DbaDatabase, C:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.334\allcommands.ps1: line 67367
at Copy-DbaDatabase, C:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.334\allcommands.ps1: line 5964
at , : line 1
PipelineIterationInfo : {0, 1}

[janeztr]

Hi, same here.
But it only fails on one database (same) out of seven.

Copy-DbaDatabase -Source $SourceInstance -Destination $DestinationInstance -BackupRestore -NetworkShare $BackupSharedFolderUNC -Database $DB -ReuseSourceFolderStructure -WithReplace -Force -Verbose:$Verbose

WARNING: [09:25:34][Copy-DbaDatabase] Failure attempting to restore DW_Metadata to GENINGDWHTEST01 | System.Data.SqlClient.SqlError: Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL11 .MSSQLSERVER\MSSQL\Backup\{D828C1D5-7E1C-454A-A904-03F81736F1C7}1'. Operating system error 2(The system cannot find the file specified.).
Path C:\Program Files\Microsoft SQL Server\MSSQL11 doesn't exist nor on source nor on destination server.

I can succesfully restore backup files which are left after Copy-DbaDatabase error with
Set-Location $BackupSharedFolderUNC
Get-ChildItem | Restore-DbaDatabase -SqlInstance $DestinationInstance -WithReplace -ReuseSourceFolderStructure

[potatoqualitee]

Can you test this again @janeztr , @duncfair or @Bendy22 ? We've made a lot of mods since.

[janeztr]

Hi, just updated to version 0.9.517 and still get the same error.

writeErrorStream : True
PSMessageDetails :
Exception : Microsoft.SqlServer.Management.Smo.SmoException: System.Data.SqlClient.SqlError: Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup{
D828C1D5-7E1C-454A-A904-03F81736F1C7}1'. Operating system error 2(The system cannot find the file specified.).
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessage
s, Boolean retry)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
TargetObject :
CategoryInfo : NotSpecified: (:) [Write-Error], SmoException
FullyQualifiedErrorId : dbatools_Copy-DbaDatabase,Stop-Function
ErrorDetails : System.Data.SqlClient.SqlError: Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup{D828C1D5-7E1C-454A-A904-03F81736F1C7}1'. Operatin
g system error 2(The system cannot find the file specified.).
InvocationInfo : System.Management.Automation.InvocationInfo
ScriptStackTrace : at Stop-Function, C:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.517\allcommands.ps1: line 106792
at Copy-DbaDatabase, C:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.517\allcommands.ps1: line 7362
at , : line 57
PipelineIterationInfo : {0, 1}

[niphlod]

that's different ... seems you have backups done by an external party ... unless you have files named Backup{
D828C1D5-7E1C-454A-A904-03F81736F1C7}1

[janeztr]

Yeah, we use MS DPM ...

[niphlod]

Yeah "we do have files named like that" or "yeah, we use DPM which doesn't leave files around" ?
Because if the latter is true, you can't use Copy-DbaDatabase if you keep having DPM actively backing up your instances.

[Bendy22]

I am sorry, but I am no longer have access to the client with this environment where I can re-test.

[potatoqualitee]

d'oh! down to @duncfair

thank you both for responding and thanks niph for handling it

[Stuart-Moore]

Looks like my PR tag didn't make it into this one. This was one of the issues that the the Get-DbaBackuphistory/Backup-DbaDatabase fixes should fix as it was most likely either:

1. Picking up a higher LSN backup from another ForkID
2. Picking up a concurrent 'proper' backup

[potatoqualitee]

fantastic news, thank you, @Stuart-Moore! Marking as closed.

[janeztr]

@niphlod
Ok, thx for the explanation. I'm not familiar with DPM, don't know how it names backup files nor where it leaves them. Still, Copy-DbaDatabase works fine for 6 out of 7 DBs, all of them backed up using DPM.

[Bendy22]

Thanks for fixing this!

[nvarscar]

Based on my experience, most of the backups made by third-party tools are pretty easy to identify (and ignore, if they stand in the way). There are two identifiers which we could use to filter out irrelevant backups (that's what I did back when I used T-SQL to generate restore scripts).
For native SQL Server disk backups, I would look at the following properties of the backup entries:

First of all, it should not be a snapshot. Some of the backup providers would snapshot SQL Server databases during host backups and the resulting backup entries would interfere with the backup LSN chain:
table =>msdb.dbo.backupset
field => is_snapshot
value => 0
This one is an easy target to exclude, as it's not breaking the LSN chain. And I actually think this might be the reason of this issue since DPM is involved. With that said, I'm not sure if it will impact Azure backups. Another switch that we could add to the function? -ExcludeSnapshots or -IncludeSnapshots depending what will be our baseline.

The second part is a backup type, which in most cases we use is a disk drive backup, not a virtual device. This one actually is supported by the function parameter -DeviceType Disk and I would encourage @janeztr to test if using this filter resolves your issue.

[niphlod]

hum, not really.
irregardless of the backup software, the backup history is there. That's why there are ALREADY switches to identify virtual or disk backups.
The "grin" here is that get-dbabackuphistory does play well in most cases, EXCEPT backup forks (because they dirsupt the natural order of lsns, and get-dbabackuphistory follows strictly lsn order).
Main issue of get-dbabackuphistory -Last stands (given forks are allowed in MSSQL, lsn order can't always be trusted). Now, recent commits tame the "error" tring to restrict the set with -LastLsn and/or fetch infos from other sources, but the problem stll stays, albeit in a more "restricted corner".
The problem with Copy-DbaDatabase here is multifold, but basically the root cause is backup-dbadatabase using get-dbadbhistory to get the properties of the backup "just done". If you fall in the "restricted corner", the backup still happens, but the output object comes from a different "line in msdb".

We can tame more doing -DeviceType Disk (at least for the moment since backup-dbadatabase doesn't support anything else) and/or -Since (notloolongago), but the problem with get-dbabackuphistory and forks will still be there, just in more and more restricted corners, without solving the root cause (which would be a PITA, but it's technically solvable)

[duncfair]

Hi Chrissy, I am at another company site working on a project that includes the migration of multiple db’s (It was only supposed to be 2). Go live is tonight but we have been using the copy-dbafatabase command extensively (and successfully) as recently as 3 days ago. Any updates since then? Thanks, Duncan Best Regards, Duncan Fairweather 805-907-8869

…

________________________________ From: Chrissy LeMaire <notifications@github.com> Sent: Thursday, November 15, 2018 5:23 PM To: sqlcollaborative/dbatools Cc: duncfair; Mention Subject: Re: [sqlcollaborative/dbatools] Copy-DbaDatabase sometimes chooses incorrect path for restore step (#3225) Can you test this again @janeztr<https://github.com/janeztr> , @duncfair<https://github.com/duncfair> or@Bendy22<https://github.com/Bendy22> ? We've made a lot of mods since. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<#3225 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AYZ6N9DKCe9795hF_EmfioDakO4_PFKSks5uvhOZgaJpZM4SDDCq>.

[potatoqualitee]

awesome, that's great news @duncfair - thank you! No changes since then, looks like stuart's earlier changes worked 🎉

[janeztr]

Hi, 0.9.533 update resolved my issue, thx!

[potatoqualitee]

fantastic! thanks for the update @janeztr 💯