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