Automatic Backup MSSQL Database to S3 Storage / Minio - Powershell Windows

mssql-backup-storage-powershell-minio-s3.ps1

$server = "127.0.0.1"
$database = "MMSQL_EXAMPLE_DATABSE"
$currentDate = Get-Date -Format "yyyy-MM-dd"
$backupFilePath = "F:\Backups\EXAMPLE-Backup-$currentDate.bak"
$logFilePath = "F:\Backup-Logs\EXAMPLE-Backup-Logs.txt"
$daysToKeep = 10

$mcPath = "F:\mc.exe"
$minioUrl = "https://storage.example.com"
$accessKey = "ACCESS_KEY"
$secretKey = "SECRET_KEY"
$bucketName = "BUCKET_NAME"

$sqlBackup = @"
BACKUP DATABASE MMSQL_EXAMPLE_DATABSE
TO DISK = '$backupFilePath'
WITH COMPRESSION, FORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10;
"@



function Log-Message {
    param (
        [string]$message
    )
    $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    $formattedMessage = "$timestamp - $message"
    Add-Content -Path $logFilePath -Value $formattedMessage
}


# Do Backup
try {
  Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $sqlBackup -QueryTimeout 600
  Log-Message "Backup completed successfully to $backupFilePath."
} catch {
  Log-Message "An error occurred during the backup: $_"
}

# Remove backups older than specified days
try {
    $thresholdDate = (Get-Date).AddDays(-$daysToKeep)
    $oldBackupFiles = Get-ChildItem -Path $backupDirectory -Filter "*.bak" | Where-Object { $_.LastWriteTime -lt $thresholdDate }

    foreach ($file in $oldBackupFiles) {
        Remove-Item $file.FullName -Force
        Log-Message "Removed backup file: $($file.FullName)"
    }

    Log-Message "Clean-up of old backups completed."
} catch {
    Log-Message "An error occurred during cleanup: $_"
}

# Push backup file to minio backup storage
[Environment]::SetEnvironmentVariable("MC_INSECURE", "1", [EnvironmentVariableTarget]::User)
& $mcPath alias set backups-deltadev $minioUrl $accessKey $secretKey
& $mcPath cp $filePath "backups-deltadev/$bucketName/"

if ($LASTEXITCODE -eq 0) {
    Log-Message "Backup uploaded successfully to MinIO."
} else {
    Log-Message "Failed to upload backup file to MinIO."
}

Docker container MSSQL command tool:

/opt/mssql-tools18/bin/sqlcmd -S localhost,1433 -U SA -P 'PASSWORD' -C -Q ""

dump:

/opt/mssql-tools18/bin/sqlcmd -S localhost,1433 -U SA -P 'PASSWORD' -C -Q "BACKUP DATABASE DeltaDb TO DISK = '/var/opt/mssql/data/DeltaDb.bak' WITH FORMAT;"

restore:

/opt/mssql-tools18/bin/sqlcmd -S localhost,1433 -U SA -P 'PASSWORD' -C -Q "RESTORE DATABASE DeltaDb FROM DISK = '/var/opt/mssql/data/DeltaDb.bak' WITH REPLACE;"