Sql Server Utilities

DataCopy.cs

using System;
using System.Data;
using Microsoft.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Threading.Tasks;
using System.Collections.Generic;

namespace SchemaCopyApp
{
    class Program
    {
        static async Task Main(string[] args)
        {
            // Inputs
            var sourceConnString = "Server=SRC_SERVER;Database=SourceDb;User Id=sa;Password=YourPwd;";
            var targetConnString = "Server=TGT_SERVER;Database=TargetDb;User Id=sa;Password=YourPwd;";
            var sourceSchema = "dbo";
            var targetSchema = "test";

            // 1. Create tables as before…
            var tables = CreateTargetSchema(sourceConnString, targetConnString, "SourceDb", "TargetDb", sourceSchema, targetSchema);

            // 2. Copy data—parallelizing up to 4 tables at once
            var semaphore = new System.Threading.SemaphoreSlim(4);
            var tasks = new List<Task>();
            
            foreach (var table in tables)
            {
                await semaphore.WaitAsync();
                tasks.Add(Task.Run(async () =>
                {
                    try
                    {
                        await CopyTableDataAsync(
                          sourceConnString,
                          targetConnString,
                          sourceSchema,
                          targetSchema,
                          table,
                          batchSize: 50000);
                        Console.WriteLine($"✓ {table} copied");
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"✗ {table} failed: {ex.Message}");
                    }
                    finally
                    {
                        semaphore.Release();
                    }
                }));
            }

            await Task.WhenAll(tasks);
            Console.WriteLine("All data copy tasks complete.");
        }

        static async Task CopyTableDataAsync(
            string sourceConnString,
            string targetConnString,
            string sourceSchema,
            string targetSchema,
            string tableName,
            int batchSize = 50000)
        {
            var sourceFullName = $"[{sourceSchema}].[{tableName}]";
            var targetFullName = $"[{targetSchema}].[{tableName}]";

            using var sourceConn = new SqlConnection(sourceConnString);
            using var command = sourceConn.CreateCommand();
            await sourceConn.OpenAsync();
            command.CommandText = $"SELECT * FROM {sourceFullName}";
            command.CommandTimeout = 0;

            // Stream rows
            using var reader = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess);

            // Bulk‐copy into target
            using var bulkCopy = new SqlBulkCopy(
                targetConnString,
                SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.UseInternalTransaction)
            {
                DestinationTableName = targetFullName,
                BatchSize = batchSize,
                BulkCopyTimeout = 0  // infinite
            };

            // Auto‐map columns by name
            for (int i = 0; i < reader.FieldCount; i++)
                bulkCopy.ColumnMappings.Add(reader.GetName(i), reader.GetName(i));

            await bulkCopy.WriteToServerAsync(reader);
        }
    }
}

SchemaCopy.cs

using System;
using System.Linq;
using Microsoft.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace SchemaCopyApp
{
    class Program
    {
        static void Main(string[] args)
        {
            // 1. Inputs: connection strings, db names, source/target schema
            var sourceConnString = "Server=SRC_SERVER;Database=SourceDb;User Id=sa;Password=YourPwd;";
            var targetConnString = "Server=TGT_SERVER;Database=TargetDb;User Id=sa;Password=YourPwd;";
            var sourceDbName = "SourceDb";
            var targetDbName = "TargetDb";
            var sourceSchema = "dbo";
            var targetSchema = "test";

            // 2. Initialize SMO servers
            var srcServer = new Server(new ServerConnection(new SqlConnection(sourceConnString)));
            var tgtServer = new Server(new ServerConnection(new SqlConnection(targetConnString)));
            var srcDb = srcServer.Databases[sourceDbName];
            var tgtDb = tgtServer.Databases[targetDbName];

            // 3. Script out all tables in the source schema
            var scripter = new Scripter(srcServer)
            {
                Options =
                {
                    ScriptSchema = true,
                    ScriptData = false,
                    SchemaQualify = true,
                    IncludeIfNotExists = true,
                    DdlHeaderOnly = false
                }
            };

            // Collect all tables in the source schema
            var tables = srcDb.Tables
                .Cast<Table>()
                .Where(t => t.Schema.Equals(sourceSchema, StringComparison.OrdinalIgnoreCase))
                .ToList();

            // 4. Generate Create Table scripts
            var createScripts = tables
                .SelectMany(t => scripter.Script(new Urn[] { t.Urn }))
                .Select(line => line
                    // Rewrite schema name: [dbo].[MyTable] → [test].[MyTable]
                    .Replace($"[{sourceSchema}].", $"[{targetSchema}]."))
                .ToList();

            // 5. Execute scripts against the target DB
            using (var conn = new SqlConnection(targetConnString))
            {
                conn.Open();
                foreach (var script in createScripts)
                {
                    using var cmd = conn.CreateCommand();
                    cmd.CommandText = script;
                    cmd.ExecuteNonQuery();
                }
            }

            Console.WriteLine("Schema copy complete.");
        }
    }
}