Issues with SMO Transfer

I am trying to come up with a way to copy a database from one server to another. I have been running tests using the Transfer method from SMO. I can make it work in some cases and not in others. Currently I am trying to copy a database and am getting an error in a script to create a View. This View is the first one in the list of views from the source database. The problem appears to be that this View refers to another view that has yet to be created in the new database. Is there a way to handle this kind of dependency or is this a limitation to the Transfer method.I'm having the same issue. ScriptTransfer() doesn't seem to generate the scripts in the correct order. Have you been able to resolve it? If so how?|||Have you tried parsing the DependencyTree and scripting the objects based on that order?|||

I was hoping to avoid having to do that. The odd thing about this issue is when we create our database from scripts generated from DB Ghost and then attempt the transfer everything works fine. It's once certain types of changes to that database are made that this problem happens.

Is there an example out there that scripts the entire database using the DependencyTree?

|||

Dim srv As Server
Dim srvConn As ServerConnection
srv = New Server("MyServer")
srvConn = srv.ConnectionContext
srvConn.LoginSecure = True

Dim db As Database
db = srv.Databases("SourceDB")
Dim strDBName As String

strDBName = "DestDB"

Dim xfr As Transfer
xfr = New Transfer(db)
xfr.CopyAllObjects = True
xfr.CopyAllUsers = True
xfr.CopyData = True
xfr.Options.WithDependencies = True
xfr.Options.ContinueScriptingOnError = True
xfr.DestinationDatabase = strDBName
xfr.DestinationServer = srv.Name
xfr.DestinationLoginSecure = True
xfr.TransferData()

|||

Thanks for the response, but that is essentially the code that I'm using when I run into trouble. Except I'm using the ScriptTransfer() function. But, both methods get the same error.

To try and break it down a little further, I executed the scripts in the StringCollection individually:

strColl = xsfr.ScriptTransfer()

Dim i As Integer

For i = 0 To strColl.Count - 1

dbEngagement.ExecuteNonQuery(strColl(i))

Next

I get an exception complaining about a view that doesn't exist while trying to create another view that is dependent upon it. So for some reason the Transfer object is getting the scripts out of order.

Further information:

I just went into Server Manager to check dependencies on the view in question. Server Manager didn't get the dependencies correct either. It left out the the sub-query when checking objects that this object depends on.