/// <summary> /// Copies objects from one database to another. /// </summary> /// <param name="orderedObjectNamesToCopy">Names of objects to copy in order of expected execution.</param> /// <param name="sourceDatabaseConnectionString">Connection string to the source database.</param> /// <param name="targetDatabaseConnectionString">Connection string to the target database.</param> /// <param name="announcer">Optional announcer to log messages about what is happening.</param> /// <returns>Task for async.</returns> public static async Task CopyObjects( IReadOnlyList <string> orderedObjectNamesToCopy, string sourceDatabaseConnectionString, string targetDatabaseConnectionString, Action <Func <object> > announcer = null) { new { orderedObjectNamesToCopy }.AsArg().Must().NotBeNullNorEmptyEnumerableNorContainAnyNulls(); new { sourceDatabaseConnectionString }.AsArg().Must().NotBeNullNorWhiteSpace(); new { targetDatabaseConnectionString }.AsArg().Must().NotBeNullNorWhiteSpace(); void NullAnnounce(Func <object> announcement) { /* no-op */ } var localAnnouncer = announcer ?? NullAnnounce; void SqlMessagesToAnnouncerAdapter(object sender, SqlInfoMessageEventArgs args) { localAnnouncer(() => args); } var scriptedObjects = Scripter.ScriptObjectsFromDatabase(sourceDatabaseConnectionString, orderedObjectNamesToCopy); using (var targetConnection = await targetDatabaseConnectionString.OpenSqlConnectionAsync(SqlMessagesToAnnouncerAdapter)) { async Task RunScriptOnServer(ScriptedObject scriptedObject, string scriptToRun) { localAnnouncer(() => Invariant($"Applying create script for '{scriptedObject.Name}' of type '{scriptedObject.ScriptableObjectType}'")); try { async Task ServerAction(Server server) { // because it might contain "GO" statements most likely this needs to be executed via the SMO connection. server.ConnectionContext.ExecuteNonQuery(scriptToRun); await Task.Run(() => { }); } await SqlServerSmoDatabaseManager.RunOperationOnSmoServerAsync(ServerAction, targetConnection); } catch (Exception ex) { throw new FailedOperationException( Invariant($"Failed to run script on database {targetDatabaseConnectionString.ObfuscateCredentialsInConnectionString()}; {scriptToRun}"), ex); } } foreach (var scriptedObject in scriptedObjects.Reverse()) { await RunScriptOnServer(scriptedObject, scriptedObject.DropScript); } foreach (var scriptedObject in scriptedObjects) { await RunScriptOnServer(scriptedObject, scriptedObject.CreateScript); } var tables = scriptedObjects.Where(_ => _.ScriptableObjectType == ScriptableObjectType.Table).ToList(); if (tables.Any()) { var copyOptions = SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls | SqlBulkCopyOptions.TableLock; using (var sourceConnection = await sourceDatabaseConnectionString.OpenSqlConnectionAsync(SqlMessagesToAnnouncerAdapter)) { foreach (var table in tables) { table.Name.MustForArg(Invariant($"{nameof(table)}.{nameof(ScriptedObject.Name)}")).NotBeNullNorWhiteSpace().And().BeAlphanumeric(TableDefinition.TableNameAlphanumericOtherAllowedCharacters); using (var transaction = targetConnection.BeginTransaction("BcpTable-" + table.Name)) { using (var bcp = new SqlBulkCopy(targetConnection, copyOptions, transaction) { DestinationTableName = table.Name }) { using (var command = sourceConnection.CreateCommand()) { command.CommandType = CommandType.Text; command.CommandText = "SELECT * FROM " + table.Name; using (var reader = await command.ExecuteReaderAsync()) { await bcp.WriteToServerAsync(reader); } } } transaction.Commit(); } } } } } }