Microsoft.SqlServer.Management.Smo.Transfer class

I'm trying to copy sql db between servers using Smo.Transfer class.

And it's ok when you're copying absolutely normal database. It works.

But what if your data inconsistent? For example I have a function and it gets the value from a table and non-existed column (someone renamed the column and now the function wouldn't work). But if you try to generate a script it will be generated ok.

But the problem begins when you actually try to run this script. Sql server wouldn't let you create the function, because It cannot refer to a non-existed column.

Something like that happens with Transfer class when you actually run .T开发者_JAVA百科ransferData() method.

The questions is. Is it possible to skip somehow the creation of object (in our example the function) entirely?

How to catch the error, skip the object and let the .TransferData() method continue it's job?

I would first take the approach of fixing the broken dependencies. Then I would take an approach designed to keep those dependencies from being broken in the future. The effect would be that your Transfer code would begin to work, and probably other code or interaction with the db code would work better as well.

To do all of this, you'll need to know about broken dependencies. I have something around here somewhere. If I find it, I will post back. I'm thinking maybe RedGate's Dependency Tracker 2? As I recalled, it still wasn't in the "zone" for me as far as being useful, but it still might do the job for you.

It's impossible to prevent the TransferData method from stopping on first error. However, there's a workaround. You can instead generate a sql script and execute it immediately - in this scenario you can force it to continue on error:

$Transfer.Options.ScriptBatchTerminator = $true
$ScriptFileName = $PSScriptRoot + '\\' + $DbName + '.sql'
$Transfer.Options.FileName = $ScriptFileName
$Transfer.Options.ToFileOnly = $true
$Transfer.EnumScriptTransfer()

Invoke-SqlCmd -InputFile $ScriptFileName -ServerInstance $SQLInstanceName -Database $TargetDbName -ErrorAction Continue

Just remember to set Options.ScriptBatchTerminator to true.