SMO.Transfer and filestreaming

Question

Has anyone managed to get the transfer mechanism working with FILESTREAM columns?

My code is as follows:

Transfer transfer = new Transfer(sourceDatabase);
transfer.CopyAllObjects = true;

transfer.DropDestinationObjectsFirst = true;
transfer.CopySchema = true;
transfer.CopyData = false;
transfer.CopyAllTables = true;
transfer.Options.WithDependencies = true; 

transfer.DestinationServer = server.Name;
transfer.DestinationDatabase = targetDatabaseName;

transfer.TransferData();

I am getting the error message when creating the table with filestreaming columns:

"ERROR : errorCode=-1073548784 description=Executing the query ... failed with the following error: "A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}"

This is odd because the source table certainly has these properties set. Am I missing part of the transfer process?

Answer 1

Oddly enough, this works fine:

//transfer.TransferData();

System.Collections.Specialized.StringCollection script = transfer.ScriptTransfer();

Microsoft.SqlServer.Management.Smo.Database targetDatabase = server.Databases["dbname"];
targetDatabase.ExecuteNonQuery(script);

Can anyone shed any light on this? Why would the transfer fail, yet generating and running the script work fine?