Sql-server – Extended properties are not copied using SMO

smosql server

I'm using the following code to copy a database from server to localhost using SMO. The code works well except the fact the extended properties are not copied.

    //Set Source SQL Server Instance Information
    Server sourceServer = new Server(sourceServerName);

    //Set Destination SQL Server Instance Information
    Server destinationServer = new Server(destinationServerName);

    try
    {
        //Using windows authentication
        sourceServer.ConnectionContext.LoginSecure = true;
        #region Optional - Using SQL Server authentication
        #endregion\ 
        sourceServer.ConnectionContext.Connect();

        //Using windows authentication
        destinationServer.ConnectionContext.LoginSecure = true;
        #region Optional - Using SQL Server authentication
        #endregion
        destinationServer.ConnectionContext.Connect();

        InitServer(sourceServer);
        InitServer(destinationServer);

        //Set Source Database Name [Database to Copy]
        Microsoft.SqlServer.Management.Smo.Database databaseSource = sourceServer.Databases[databaseName];
        //Set Destination Database Name 
        Microsoft.SqlServer.Management.Smo.Database databaseDestination = new Microsoft.SqlServer.Management.Smo.Database(destinationServer, databaseName);
        
        databaseDestination.Create();
      

        //Set Transfer Class Source Database
        Transfer transferDatabase = new Transfer(databaseSource);

        //transferDatabase.CopyAllUsers = true;
        transferDatabase.CopyAllObjects = false;
        transferDatabase.CopyAllSchemas = true;
        transferDatabase.CopyAllUserDefinedDataTypes = true;
        transferDatabase.CopyAllTables = true;
        transferDatabase.CopyData = true;
        transferDatabase.CopyAllStoredProcedures = true;
        transferDatabase.DestinationServer = destinationServer.Name;
        transferDatabase.DestinationDatabase = databaseDestination.Name;
        transferDatabase.TemporaryPackageDirectory = @"C:\DATA\";

        transferDatabase.Options.WithDependencies = true;
        transferDatabase.Options.DriAll = true;
        transferDatabase.DropDestinationObjectsFirst = true;
        transferDatabase.Options.ContinueScriptingOnError = true;

        transferDatabase.TransferData();

    }
    catch (Exception ex)
    {
        log.Error(ex.Message);
    }
    finally
    {
        if (sourceServer.ConnectionContext.IsOpen)
        {
            sourceServer.ConnectionContext.Disconnect();
        }

        if (destinationServer.ConnectionContext.IsOpen)
        {
            destinationServer.ConnectionContext.Disconnect();
        }
    }

Best Answer

yes extended properties are a bit "special" to handle, fortunately Phil Factor has written a long article with helper functions which you can find here :- PhilFactorsArticle