Run a data synchronization script against a different database

# ============================================================
# dbForge Studio for SQL Server CLI
# Generate the sync script and safely update DB_NAME() and USE statements
# ============================================================
 
# ---------- Database name for script execution ----------
$ExecutionTargetDatabase = "yourExecutionDatabase"
 
# ---------- dbForge CLI path ----------
$DbForgeCliPath = "C:\Program Files\Devart\dbForge Studio for SQL Server\dbforgesql.com"
 
# ---------- Source connection ----------
$SourceServer = "yourSourceServer"
$SourceDatabase = "yourSourceDatabase"
$SourceUser = "yourUsername"
$SourcePassword = "yourPassword"
 
# ---------- Target connection ----------
$TargetServer = "yourTargetServer"
$TargetDatabase = "yourTargetDatabase"
$TargetUser = "yourUsername"
$TargetPassword = "yourPassword"
 
# ---------- Output files ----------
$SyncScriptFile = "D:\DataCompare\Synchronization scripts\data_compare_sync.sql"
$LogFile = "D:\DataCompare\Logs\data_compare.log"
 
# ============================================================
# Ensure the output directory exists
# ============================================================
$OutputDir = Split-Path $SyncScriptFile
if (-not (Test-Path $OutputDir)) {
New-Item -Path $OutputDir -ItemType Directory | Out-Null
}
 
# ============================================================
# Build the dbForge Data Compare command
# ============================================================
$Arguments = @(
"/datacompare",
"/source connection:`"Data Source=$SourceServer;Encrypt=False;Initial Catalog=$SourceDatabase;Integrated Security=False;User ID=$SourceUser;Password=$SourcePassword`"",
"/target connection:`"Data Source=$TargetServer;Encrypt=False;Initial Catalog=$TargetDatabase;Integrated Security=False;User ID=$TargetUser;Password=$TargetPassword`"",
"/sync:`"$SyncScriptFile`"",
"/log:`"$LogFile`""
)
 
Write-Host "Running dbForge Data Compare CLI..."
Write-Host "$DbForgeCliPath $($Arguments -join ' ')"
Write-Host ""
 
Start-Process `
-FilePath $DbForgeCliPath `
-ArgumentList $Arguments `
-Wait `
-NoNewWindow
 
# ============================================================
# Validate sync script generation
# ============================================================
if (-not (Test-Path $SyncScriptFile)) {
throw "Synchronization SQL file was not created: $SyncScriptFile"
}
 
$content = Get-Content -Path $SyncScriptFile -Raw
 
if ([string]::IsNullOrWhiteSpace($content)) {
Write-Warning "Synchronization SQL file is empty: $SyncScriptFile. Possibly no changes detected."
return
}
 
# ============================================================
# Update DB_NAME() safety check
# ============================================================
$patternDBName = "IF\s+DB_NAME\(\)\s+<>\s+N'[^']+'\s+SET\s+NOEXEC\s+ON"
$replacementDBName = "IF DB_NAME() <> N'$ExecutionTargetDatabase' SET NOEXEC ON"
 
$newContent = [regex]::Replace($content, $patternDBName, $replacementDBName)
 
if ($newContent -eq $content) {
Write-Warning "DB_NAME() safety check was not found or not replaced."
}
 
# ============================================================
# Replace all USE statements with the execution database
# ============================================================
# Matches any line starting with USE [OldDatabase] (case-insensitive)
$newContent = [regex]::Replace(
$newContent,
"(?i)^\s*USE\s+(\[?[^\]\s]+\]?)",
"USE [$ExecutionTargetDatabase]",
'Multiline'
)
 
# ============================================================
# Save the modified script with UTF-8 encoding
# ============================================================
$newContent | Set-Content -Path $SyncScriptFile -Encoding UTF8
 
# ============================================================
# Final output
# ============================================================
Write-Host "Synchronization script generated successfully."
Write-Host "DB_NAME() check updated to: $ExecutionTargetDatabase"
Write-Host "All USE statements replaced with: $ExecutionTargetDatabase"
Write-Host "SQL file: $SyncScriptFile"
Write-Host "Log file: $LogFile"