Sample Header Ad - 728x90

Extended properties are not copied using SMO

0 votes
2 answers
117 views
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(); } }
Asked by Elena2020 (71 rep)
Jan 26, 2021, 03:13 PM
Last activity: Jan 28, 2021, 12:07 AM