search

dataplat / dbatools

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

Copy-DbaDatabase sometimes chooses incorrect path for restore step #3225

Closed Bendy22 closed 5 years ago

Bendy22 commented 6 years ago

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}

Version Information

Steps to Reproduce

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

dbatoolslog.zip

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 commented 6 years ago

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 commented 6 years ago 
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 commented 6 years ago

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 commented 6 years ago

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 commented 6 years ago

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 commented 6 years ago

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 commented 5 years ago

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

janeztr commented 5 years ago

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 commented 5 years ago

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 commented 5 years ago

Yeah, we use MS DPM ...

niphlod commented 5 years ago

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 commented 5 years ago

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

potatoqualitee commented 5 years ago

d'oh! down to @duncfair

thank you both for responding and thanks niph for handling it

Stuart-Moore commented 5 years ago

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 commented 5 years ago

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

janeztr commented 5 years ago

@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 commented 5 years ago

Thanks for fixing this!

nvarscar commented 5 years ago

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 commented 5 years ago

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 commented 5 years ago

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 @janeztrhttps://github.com/janeztr , @duncfairhttps://github.com/duncfair or@Bendy22https://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 GitHubhttps://github.com/sqlcollaborative/dbatools/issues/3225#issuecomment-439249461, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AYZ6N9DKCe9795hF_EmfioDakO4_PFKSks5uvhOZgaJpZM4SDDCq.

potatoqualitee commented 5 years ago

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

janeztr commented 5 years ago

Hi, 0.9.533 update resolved my issue, thx!

potatoqualitee commented 5 years ago

fantastic! thanks for the update @janeztr 💯