MSSQL: batch exporter tables data

# batch_exporter_tables_data.ps1
# batch exporter table data
# Author: Pitt Phunsanit
# --- CONFIGURATION SECTION ---

# 1. Define your SQL Server Instance and Database
$SqlServerName = "LOCALHOST" # Example: "SQLDEV\INSTANCE1" or "LOCALHOST"
$DatabaseNameSource = "DB_DEV" # Example: "DB_DEV"
$DatabaseNameTarget = "DB_QA" # Example: "DB_QA"

# 2. Define the Authentication Method (Choose one of the two blocks below)

# --- A) WINDOWS AUTHENTICATION (Recommended) ---
# $UseWindowsAuth = $true
# $SqlUserName = ""
# $SqlPassword = ""

# --- B) SQL SERVER AUTHENTICATION (Uncomment and fill in details) ---
$UseWindowsAuth = $false
$SqlUserName = "pitt" # Example: "sa"
$SqlPassword = "phunsanit" # Example: "your_password"

# 3. Define the Output Directory
$OutputDirectory = "C:\portables\SSMS\exporter\$(Get-Date -Format 'yyyyMMdd_HHmmss')_data" # Change to your desired output path

# 4. Script Options
$IncludeTruncateStatements = $true # Set to $false if you don't want TRUNCATE TABLE statements
$IncludeTruncateComments = $true # Set to $false if you don't want decorative comments around TRUNCATE statements
$CommentOutTruncateStatements = $true # Set to $true to wrap TRUNCATE statements in /* */ block comments

# 5. List of Tables to Export (Parsed from your request)
$TableList = @(
    "PP.APPROVE_LOG",
    "PP.ORDER"
    "PP.PAY_ORDER_EMAIL_LOG",
    "PP.PAY_ORDER_PREMIUM_VAT",
    "PP.PAY_ORDER_VAT",s
    "PP.PAY_ORDER",
    "PP.PAYMENT_CHEQUE",
    "PP.PAYMENT_CYCLE",
    "PP.PAYMENT_VOUCHER",
    "PP.PERMISSION",
    "PP.SEARCH",
    "PP.USERS",
)

# Requires PowerShell 5.1 or newer.
# REQUIRES: The SQL Server Management Objects (SMO) component must be installed
# on the machine running this script. This is typically included with a full SSMS installation.

# Check if running as Administrator
$IsAdmin = ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")

if (-not $IsAdmin) {
    Write-Warning "This script is not running with Administrator privileges."
    Write-Host "To install SqlServer module, you need to:" -ForegroundColor Yellow
    Write-Host "  1. Right-click PowerShell and select 'Run as Administrator'" -ForegroundColor Cyan
    Write-Host "  2. Run: Install-Module -Name SqlServer -Force -AllowClobber" -ForegroundColor Cyan
    Write-Host "  3. Then run this script normally" -ForegroundColor Cyan
    Write-Host ""
    Write-Host "Continuing with current privileges..." -ForegroundColor Yellow
    Write-Host ""
}

# --- SCRIPT LOGIC (DO NOT EDIT BELOW THIS LINE) ---

# Check prerequisites and load SMO
try {
    Import-Module SqlServer -ErrorAction Stop
}
catch {
    Write-Error "Could not load the 'SqlServer' module. Please install it by running 'Install-Module -Name SqlServer' in an Administrator PowerShell session."
    exit 1
}

# Create output directory
if (-not (Test-Path $OutputDirectory)) {
    Write-Host "Creating output directory: $OutputDirectory"
    New-Item -Path $OutputDirectory -ItemType Directory | Out-Null
}

# Setup SMO Server object
if ($UseWindowsAuth) {
    Write-Host "Connecting to $SqlServerName using Windows Authentication..."
    $Srv = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServerName
}
else {
    Write-Host "Connecting to $SqlServerName using SQL Login: $SqlUserName..."
    $sc = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($SqlServerName, $SqlUserName, $SqlPassword)
    $Srv = New-Object Microsoft.SqlServer.Management.Smo.Server $sc
}

# Check connection
try {
    $null = $Srv.Version
}
catch {
    Write-Error "Failed to connect to SQL Server '$SqlServerName'. Check server name, credentials, and network."
    exit 1
}

$Db = $Srv.Databases[$DatabaseNameSource]
if ($Db -eq $null) {
    Write-Error "Database '$DatabaseNameSource' not found on server '$SqlServerName'."
    exit 1
}

# Configure Scripter for Data
$Scripter = New-Object Microsoft.SqlServer.Management.Smo.Scripter $Srv

$Scripter.Options.ScriptSchema = $false
$Scripter.Options.ScriptData = $true
$Scripter.Options.NoCommandTerminator = $false
$Scripter.Options.ScriptDrops = $false
$Scripter.Options.ToFileOnly = $true
$Scripter.Options.Encoding = [System.Text.Encoding]::UTF8

Write-Host "Scripting DATA for $($TableList.Count) tables from '$DatabaseNameSource'..."

# Process each table
$TableList | ForEach-Object {
    $FullName = $_
    if ($FullName -match "(.+)\.(.+)") {
        $SchemaName = $Matches[1]
        $TableName = $Matches[2]
    }
    else {
        Write-Warning "Skipping '$FullName': Invalid Schema.Table format."
        return
    }

    $Table = $Db.Tables[$TableName, $SchemaName]
    if ($Table -ne $null) {
        $OutputFile = Join-Path $OutputDirectory "$SchemaName.$TableName.sql"
        Write-Host "  -> Scripting data for $FullName to $OutputFile" -ForegroundColor Yellow

        try {
            $UseStatement = @"
-- ================================================================================================
-- batch_Exporter_Data.ps1
-- DATA EXPORT TIMESTAMP: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')
-- SOURCE DATABASE: $DatabaseNameSource
-- TARGET DATABASE: $DatabaseNameTarget
-- ================================================================================================
USE [$DatabaseNameTarget]; --- create in DATABASE: $DatabaseNameTarget from structure in $DatabaseNameSource
GO

"@
            # Create TRUNCATE TABLE statement (if enabled)
            $TruncateStatement = ""
            if ($IncludeTruncateStatements) {
                if ($IncludeTruncateComments) {
                    $TruncateContent = @"
-- ================================================================================================
-- TRUNCATE TABLE: $SchemaName.$TableName
-- ================================================================================================
IF OBJECT_ID('[$SchemaName].[$TableName]', 'U') IS NOT NULL
    TRUNCATE TABLE [$SchemaName].[$TableName];
GO
"@
                    if ($CommentOutTruncateStatements) {
                        $TruncateStatement = @"
/*
$TruncateContent
*/

-- ================================================================================================
-- INSERT DATA: $SchemaName.$TableName
-- ================================================================================================

"@
                    }
                    else {
                        $TruncateStatement = @"
$TruncateContent

-- ================================================================================================
-- INSERT DATA: $SchemaName.$TableName
-- ================================================================================================

"@
                    }
                }
                else {
                    $TruncateContent = "-- Truncate table if exists`r`nIF OBJECT_ID('[$SchemaName].[$TableName]', 'U') IS NOT NULL`r`n    TRUNCATE TABLE [$SchemaName].[$TableName];`r`nGO"
                    if ($CommentOutTruncateStatements) {
                        $TruncateStatement = "/*`r`n$TruncateContent`r`n*/`r`n`r`n"
                    }
                    else {
                        $TruncateStatement = "$TruncateContent`r`n`r`n"
                    }
                }
            }

            # Write header statements to file
            $UseStatement + $TruncateStatement | Out-File -FilePath $OutputFile -Encoding UTF8 -Force

            # Append data (INSERT statements) to the same file
            $Scripter.Options.FileName = $OutputFile
            $Scripter.Options.AppendToFile = $true
            $Scripter.EnumScript(@($Table))
        }
        catch {
            Write-Error "Error scripting data for table $FullName`: $($_.Exception.Message)"
        }
    }
    else {
        Write-Warning "Table '$FullName' not found in database '$DatabaseNameSource'. Skipping."
    }
}

Write-Host "--- Data export process complete. Check files in '$OutputDirectory' ---"