Copy-SmallTableData.ps1

<#
.SYNOPSIS
    Transfers data from small tables (5MB or less) between databases using dbatools.

.DESCRIPTION
    This script uses dbatools to copy data from small tables (defined as 5MB or less) 
    from a source database to a destination database. It identifies small tables based 
    on their size, handles dependencies, and provides detailed logging.

.PARAMETER SourceServer
    The source SQL Server instance.

.PARAMETER SourceDatabase
    The source database name.

.PARAMETER DestinationServer
    The destination SQL Server instance.

.PARAMETER DestinationDatabase
    The destination database name.

.PARAMETER MaxSizeMB
    Maximum table size in MB to transfer. Default is 5MB.

.PARAMETER SourceCredential
    PSCredential object for source server authentication.

.PARAMETER DestinationCredential
    PSCredential object for destination server authentication.

.PARAMETER LogFile
    Path to the log file. Default is transfer_log.txt in the current directory.

.EXAMPLE
    .\Copy-SmallTableData.ps1 -SourceServer "SourceSQL" -SourceDatabase "ProductionDB" -DestinationServer "DestSQL" -DestinationDatabase "TestDB"

.NOTES
    Requires the dbatools module: Install-Module -Name dbatools
#>

[CmdletBinding()]
param (
    [Parameter(Mandatory = $true)]
    [string]$SourceServer,

    [Parameter(Mandatory = $true)]
    [string]$SourceDatabase,

    [Parameter(Mandatory = $true)]
    [string]$DestinationServer,

    [Parameter(Mandatory = $true)]
    [string]$DestinationDatabase,

    [Parameter(Mandatory = $false)]
    [int]$MaxSizeMB = 5,

    [Parameter(Mandatory = $false)]
    [PSCredential]$SourceCredential,

    [Parameter(Mandatory = $false)]
    [PSCredential]$DestinationCredential,

    [Parameter(Mandatory = $false)]
    [string]$LogFile = ".\transfer_log.txt"
)

# Function to write to log file and console
function Write-Log {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [string]$Message,

        [Parameter(Mandatory = $false)]
        [ValidateSet("INFO", "WARNING", "ERROR", "SUCCESS")]
        [string]$Level = "INFO"
    )

    $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    $logEntry = "[$timestamp] [$Level] $Message"
    
    # Determine console color based on log level
    $consoleColor = switch ($Level) {
        "INFO" { "White" }
        "WARNING" { "Yellow" }
        "ERROR" { "Red" }
        "SUCCESS" { "Green" }
        default { "White" }
    }
    
    # Write to console with color
    Write-Host $logEntry -ForegroundColor $consoleColor
    
    # Append to log file
    Add-Content -Path $LogFile -Value $logEntry
}

# Check if dbatools module is installed
if (-not (Get-Module -ListAvailable -Name dbatools)) {
    Write-Log "dbatools module not found. Installing..." -Level "WARNING"
    try {
        Install-Module -Name dbatools -Force -AllowClobber
        Import-Module dbatools
        Write-Log "Successfully installed and imported dbatools module." -Level "SUCCESS"
    }
    catch {
        Write-Log "Failed to install dbatools module: $_" -Level "ERROR"
        exit 1
    }
}
else {
    Import-Module dbatools
    Write-Log "dbatools module imported." -Level "INFO"
}

# Create the log file if it doesn't exist
if (-not (Test-Path $LogFile)) {
    New-Item -ItemType File -Path $LogFile -Force | Out-Null
    Write-Log "Created log file: $LogFile" -Level "INFO"
}

Write-Log "Starting data transfer for tables smaller than $MaxSizeMB MB from $SourceServer.$SourceDatabase to $DestinationServer.$DestinationDatabase" -Level "INFO"

# Prepare connection parameters
$sourceParams = @{
    SqlInstance = $SourceServer
    Database    = $SourceDatabase
}

$destParams = @{
    SqlInstance = $DestinationServer
    Database    = $DestinationDatabase
}

if ($SourceCredential) {
    $sourceParams.SqlCredential = $SourceCredential
}

if ($DestinationCredential) {
    $destParams.SqlCredential = $DestinationCredential
}

try {
    # Test connections
    Write-Log "Testing connection to source server..." -Level "INFO"
    $sourceConnection = Connect-DbaInstance @sourceParams
    Write-Log "Connection to source server successful." -Level "SUCCESS"

    Write-Log "Testing connection to destination server..." -Level "INFO"
    $destConnection = Connect-DbaInstance @destParams
    Write-Log "Connection to destination server successful." -Level "SUCCESS"

    # Get table sizes from source database
    Write-Log "Getting table sizes from source database..." -Level "INFO"
    $query = @"
    SELECT 
        s.name AS [SchemaName],
        t.name AS [TableName],
        p.rows AS [RowCount],
        SUM(a.total_pages) * 8 / 1024.0 AS [TotalSizeMB],
        SUM(a.used_pages) * 8 / 1024.0 AS [UsedSizeMB]
    FROM 
        sys.tables t
    INNER JOIN 
        sys.schemas s ON s.schema_id = t.schema_id
    INNER JOIN 
        sys.indexes i ON t.object_id = i.object_id
    INNER JOIN 
        sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
    INNER JOIN 
        sys.allocation_units a ON p.partition_id = a.container_id
    WHERE 
        t.is_ms_shipped = 0
    GROUP BY 
        t.name, s.name, p.rows
    ORDER BY 
        [TotalSizeMB] DESC
"@

    $tableSizes = Invoke-DbaQuery -SqlInstance $sourceConnection -Database $SourceDatabase -Query $query
    Write-Log "Found $($tableSizes.Count) tables in the source database." -Level "INFO"

    # Filter tables by size
    $smallTables = $tableSizes | Where-Object { $_.TotalSizeMB -le $MaxSizeMB }
    Write-Log "Found $($smallTables.Count) tables smaller than $MaxSizeMB MB." -Level "INFO"

    if ($smallTables.Count -eq 0) {
        Write-Log "No tables found that are smaller than $MaxSizeMB MB." -Level "WARNING"
        exit 0
    }

    # Get dependency order for tables
    Write-Log "Analyzing table dependencies..." -Level "INFO"
    
    # Query to get foreign key dependencies
    $dependencyQuery = @"
    WITH TableDependencies AS (
        SELECT
            OBJECT_SCHEMA_NAME(fk.parent_object_id) AS [ParentSchema],
            OBJECT_NAME(fk.parent_object_id) AS [ParentTable],
            OBJECT_SCHEMA_NAME(fk.referenced_object_id) AS [ReferencedSchema],
            OBJECT_NAME(fk.referenced_object_id) AS [ReferencedTable]
        FROM 
            sys.foreign_keys fk
        WHERE 
            fk.is_ms_shipped = 0
    )
    SELECT * FROM TableDependencies
"@

    $dependencies = Invoke-DbaQuery -SqlInstance $sourceConnection -Database $SourceDatabase -Query $dependencyQuery
    Write-Log "Found $($dependencies.Count) foreign key dependencies." -Level "INFO"

    # Build a list of tables in dependency order
    $orderedTables = New-Object System.Collections.ArrayList
    $processedTables = New-Object System.Collections.Generic.HashSet[string]
    
    # Helper function to get the full table name
    function Get-FullTableName {
        param (
            [string]$Schema,
            [string]$Table
        )
        return "$Schema.$Table"
    }

    # Helper function to process a table and its dependencies
    function Process-Table {
        param (
            [string]$SchemaName,
            [string]$TableName
        )
        
        $fullTableName = Get-FullTableName -Schema $SchemaName -Table $TableName
        
        # Skip if already processed
        if ($processedTables.Contains($fullTableName)) {
            return
        }
        
        # Process dependencies first
        $tableDependencies = $dependencies | Where-Object { 
            $_.ParentSchema -eq $SchemaName -and $_.ParentTable -eq $TableName
        }
        
        foreach ($dep in $tableDependencies) {
            # Only process dependencies that are also in our small tables list
            $depFullName = Get-FullTableName -Schema $dep.ReferencedSchema -Table $dep.ReferencedTable
            $depInSmallTables = $smallTables | Where-Object { 
                (Get-FullTableName -Schema $_.SchemaName -Table $_.TableName) -eq $depFullName
            }
            
            if ($depInSmallTables) {
                Process-Table -SchemaName $dep.ReferencedSchema -TableName $dep.ReferencedTable
            }
        }
        
        # Add this table to the processed list
        [void]$processedTables.Add($fullTableName)
        
        # Add to ordered list if it's a small table
        $isSmallTable = $smallTables | Where-Object { 
            $_.SchemaName -eq $SchemaName -and $_.TableName -eq $TableName
        }
        
        if ($isSmallTable) {
            [void]$orderedTables.Add(@{
                SchemaName = $SchemaName
                TableName = $TableName
                Size = ($isSmallTable.TotalSizeMB)
                Rows = ($isSmallTable.RowCount)
            })
        }
    }

    # Process all small tables
    foreach ($table in $smallTables) {
        Process-Table -SchemaName $table.SchemaName -TableName $table.TableName
    }

    Write-Log "Ordered $($orderedTables.Count) tables for transfer based on dependencies." -Level "INFO"

    # Truncate destination tables first (in reverse order to avoid FK violations)
    Write-Log "Truncating destination tables..." -Level "INFO"
    $reversedTables = $orderedTables.Clone()
    [array]::Reverse($reversedTables)

    foreach ($table in $reversedTables) {
        $fullTableName = Get-FullTableName -Schema $table.SchemaName -Table $table.TableName
        try {
            Write-Log "Truncating table: $fullTableName" -Level "INFO"
            $truncateQuery = "TRUNCATE TABLE [$($table.SchemaName)].[$($table.TableName)]"
            
            # Check if the table has foreign key constraints which would prevent TRUNCATE
            $fkCheckQuery = @"
            IF EXISTS (
                SELECT 1 FROM sys.foreign_keys 
                WHERE parent_object_id = OBJECT_ID('[$($table.SchemaName)].[$($table.TableName)]')
                OR referenced_object_id = OBJECT_ID('[$($table.SchemaName)].[$($table.TableName)]')
            )
            SELECT 1 AS HasFK
            ELSE
            SELECT 0 AS HasFK
"@
            $hasFKs = (Invoke-DbaQuery -SqlInstance $destConnection -Database $DestinationDatabase -Query $fkCheckQuery).HasFK
            
            if ($hasFKs -eq 1) {
                $truncateQuery = "DELETE FROM [$($table.SchemaName)].[$($table.TableName)]"
                Write-Log "Table has foreign keys, using DELETE instead of TRUNCATE." -Level "WARNING"
            }
            
            Invoke-DbaQuery -SqlInstance $destConnection -Database $DestinationDatabase -Query $truncateQuery
            Write-Log "Successfully truncated table: $fullTableName" -Level "SUCCESS"
        }
        catch {
            Write-Log "Error truncating table $fullTableName`: $_" -Level "ERROR"
        }
    }

    # For each small table, copy the data
    Write-Log "Starting data transfer of $($orderedTables.Count) tables..." -Level "INFO"
    foreach ($table in $orderedTables) {
        $fullTableName = Get-FullTableName -Schema $table.SchemaName -Table $table.TableName
        
        Write-Log "Copying data for table: $fullTableName (Size: $($table.Size) MB, Rows: $($table.Rows))" -Level "INFO"
        
        try {
            # Use Copy-DbaDbTableData from dbatools
            $copyParams = @{
                SqlInstance          = $sourceConnection
                Destination          = $destConnection
                Database             = $SourceDatabase
                DestinationDatabase  = $DestinationDatabase
                Table                = "[$($table.SchemaName)].[$($table.TableName)]"
                BatchSize            = 10000
                Truncate             = $false  # We already truncated
                BulkCopy             = $true
            }
            
            $result = Copy-DbaDbTableData @copyParams
            
            if ($result) {
                Write-Log "Successfully copied data for table: $fullTableName" -Level "SUCCESS"
            }
            else {
                Write-Log "No result returned when copying table: $fullTableName" -Level "WARNING"
            }
        }
        catch {
            Write-Log "Error copying data for table $fullTableName`: $_" -Level "ERROR"
        }
    }

    # Get identity columns and reseed them
    Write-Log "Reseeding identity columns..." -Level "INFO"
    
    $identityQuery = @"
    SELECT 
        s.name AS [SchemaName],
        t.name AS [TableName],
        c.name AS [ColumnName]
    FROM 
        sys.identity_columns c
    INNER JOIN 
        sys.tables t ON c.object_id = t.object_id
    INNER JOIN 
        sys.schemas s ON t.schema_id = s.schema_id
    WHERE 
        t.is_ms_shipped = 0
"@

    $identityColumns = Invoke-DbaQuery -SqlInstance $destConnection -Database $DestinationDatabase -Query $identityQuery
    Write-Log "Found $($identityColumns.Count) identity columns to reseed." -Level "INFO"

    foreach ($identity in $identityColumns) {
        $fullTableName = Get-FullTableName -Schema $identity.SchemaName -Table $identity.TableName
        
        # Check if this table was in our transfer list
        $tableInTransferList = $orderedTables | Where-Object { 
            $_.SchemaName -eq $identity.SchemaName -and $_.TableName -eq $identity.TableName 
        }
        
        if ($tableInTransferList) {
            try {
                Write-Log "Reseeding identity column for table: $fullTableName" -Level "INFO"
                
                # Get the current max value
                $maxQuery = "SELECT ISNULL(MAX([$($identity.ColumnName)]), 0) AS MaxVal FROM [$($identity.SchemaName)].[$($identity.TableName)]"
                $maxValue = (Invoke-DbaQuery -SqlInstance $destConnection -Database $DestinationDatabase -Query $maxQuery).MaxVal
                
                # Reseed the identity
                $reseedQuery = "DBCC CHECKIDENT ('[$($identity.SchemaName)].[$($identity.TableName)]', RESEED, $maxValue)"
                Invoke-DbaQuery -SqlInstance $destConnection -Database $DestinationDatabase -Query $reseedQuery
                
                Write-Log "Successfully reseeded identity column for table: $fullTableName" -Level "SUCCESS"
            }
            catch {
                Write-Log "Error reseeding identity for table $fullTableName`: $_" -Level "ERROR"
            }
        }
    }

    # Summary
    Write-Log "Data transfer completed. Transferred $($orderedTables.Count) tables." -Level "SUCCESS"
    Write-Log "Total data transferred: $($smallTables | Measure-Object -Property TotalSizeMB -Sum | Select-Object -ExpandProperty Sum) MB" -Level "INFO"
    Write-Log "Total rows transferred: $($smallTables | Measure-Object -Property RowCount -Sum | Select-Object -ExpandProperty Sum)" -Level "INFO"
}
catch {
    Write-Log "An error occurred during the data transfer process: $_" -Level "ERROR"
    exit 1
}
finally {
    # Close connections if they were opened
    if ($sourceConnection) {
        Write-Log "Closing connection to source server." -Level "INFO"
        Disconnect-DbaInstance -SqlInstance $sourceConnection
    }
    
    if ($destConnection) {
        Write-Log "Closing connection to destination server." -Level "INFO"
        Disconnect-DbaInstance -SqlInstance $destConnection
    }
}

Write-Log "Script execution completed." -Level "SUCCESS"