Automate Database Backup with Bash, Python, and PowerShell

Backing up databases regularly is essential for data recovery and continuity. In this guide, we’ll automate backups for MySQL and PostgreSQL using Bash and Python, and for MSSQL with PowerShell. Each script will include scheduling options and a way to manage backup retention.

Prerequisites

1. Database Clients: Ensure mysql, pg_dump, and sqlcmd (or SQL Server Management Objects) are installed.
2. Permissions: Ensure the script user has permissions to read databases and write backups.
3. Scheduling: We’ll cover how to schedule these scripts using cron for Linux and Task Scheduler for Windows.

Part 1: MySQL Backup Automation

Method 1: Bash Script
Bash Script for MySQL Backup:

#!/bin/bash

# Configuration
DB_NAME="your_database"
DB_USER="your_user"
DB_PASSWORD="your_password"
BACKUP_DIR="/path/to/backup/dir"
TIMESTAMP=$(date +"%F")
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_backup_$TIMESTAMP.sql"

# MySQL Dump Command
mysqldump -u $DB_USER -p$DB_PASSWORD $DB_NAME > $BACKUP_FILE

# Compression (optional)
gzip $BACKUP_FILE

# Log completion
echo "MySQL Backup for $DB_NAME completed: $BACKUP_FILE.gz"

1. Save this script as mysql_backup.sh.
2. Make it executable: chmod +x mysql_backup.sh.
3. Add to cron for daily backups at midnight:

0 0 * * * /path/to/mysql_backup.sh >> /path/to/backup/logs/backup.log 2>&1

Method 2: Python Script
Python Script for MySQL Backup:

import os
from datetime import datetime
import subprocess

# Configuration
DB_NAME = "your_database"
DB_USER = "your_user"
DB_PASSWORD = "your_password"
BACKUP_DIR = "/path/to/backup/dir"
TIMESTAMP = datetime.now().strftime("%Y-%m-%d")
BACKUP_FILE = os.path.join(BACKUP_DIR, f"{DB_NAME}_backup_{TIMESTAMP}.sql")

# MySQL Dump Command
command = f"mysqldump -u {DB_USER} -p{DB_PASSWORD} {DB_NAME} > {BACKUP_FILE}"
subprocess.call(command, shell=True)

# Optional compression
subprocess.call(["gzip", BACKUP_FILE])

print(f"MySQL backup for {DB_NAME} completed: {BACKUP_FILE}.gz")

- Run as a cron job similar to the Bash script:

0 0 * * * python3 /path/to/mysql_backup.py >> /path/to/backup/backup.log 2>&1

---

Part 2: PostgreSQL Backup Automation

Method 1: Bash Script
Bash Script for PostgreSQL Backup:

#!/bin/bash

# Configuration
DB_NAME="your_database"
DB_USER="your_user"
BACKUP_DIR="/path/to/backup/dir"
TIMESTAMP=$(date +"%F")
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_backup_$TIMESTAMP.sql"

# PostgreSQL Dump Command
PGPASSWORD="your_password" pg_dump -U $DB_USER $DB_NAME > $BACKUP_FILE

# Compression (optional)
gzip $BACKUP_FILE

# Log completion
echo "PostgreSQL Backup for $DB_NAME completed: $BACKUP_FILE.gz"

• Save as postgres_backup.sh.
• Set cron job:

0 0 * * * /path/to/postgres_backup.sh >> /path/to/backup/backup.log 2>&1

---

Method 2: Python Script
Python Script for PostgreSQL Backup:

import os
from datetime import datetime
import subprocess

# Configuration
DB_NAME = "your_database"
DB_USER = "your_user"
BACKUP_DIR = "/path/to/backup/dir"
TIMESTAMP = datetime.now().strftime("%Y-%m-%d")
BACKUP_FILE = os.path.join(BACKUP_DIR, f"{DB_NAME}_backup_{TIMESTAMP}.sql")

# PostgreSQL Dump Command
command = f"PGPASSWORD='your_password' pg_dump -U {DB_USER} {DB_NAME} > {BACKUP_FILE}"
subprocess.call(command, shell=True)

# Optional compression
subprocess.call(["gzip", BACKUP_FILE])

print(f"PostgreSQL backup for {DB_NAME} completed: {BACKUP_FILE}.gz")

• Schedule with cron:

0 0 * * * python3 /path/to/postgres_bkp.py >> /path/to/backup/backup.log 2>&1

---

Part 3: MSSQL Backup Automation with PowerShell

PowerShell Script for MSSQL Backup:

# Configuration
$serverName = "your_server"
$dbName = "your_database"
$backupDir = "C:\path\to\backup\dir"
$timestamp = Get-Date -Format "yyyy-MM-dd"
$backupFile = "$backupDir\$dbName`_backup_$timestamp.bak"

# SQL Backup Command
Invoke-Sqlcmd -ServerInstance $serverName -Query "BACKUP DATABASE [$dbName] TO DISK = N'$backupFile' WITH NOFORMAT, NOINIT, NAME = '$dbName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

# Optional compression with 7-Zip (if installed)
& "C:\Program Files\7-Zip\7z.exe" a "$backupFile.zip" "$backupFile"
Remove-Item $backupFile

Write-Output "MSSQL Backup for $dbName completed: $backupFile.zip"

1. Save as mssql_backup.ps1.
2. Schedule with Windows Task Scheduler: Create a new task, set the action to run powershell.exe with the script path as an argument. Set the schedule (e.g., daily at midnight).

---

Backup Retention Policy

To avoid running out of storage, consider setting a retention policy to delete backups older than a specified number of days.

Bash Command:

find /path/to/backup/dir -type f -mtime +30 -name "*.gz" -exec rm {} \;

PowerShell Command:

$backupDir = "C:\path\to\backup\dir"
Get-ChildItem -Path $backupDir -Filter "*.zip" | Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-30) } | Remove-Item

Add these commands to the end of each script or as separate scheduled tasks to keep backup directories clean and organized.

Conclusion

With these scripts, you’ll have a reliable way to automatically back up MySQL, PostgreSQL, and MSSQL databases, reducing the risk of data loss. Remember to test the backups regularly to ensure data integrity and be ready to restore when needed!

🔗 Support my Work

▶️ Support by Subscribing my YouTube
▶️ Explore more open-source tutorials on my website
▶️ Follow me on X
☕ Buy me a Coffee

Learn Complete Bash CLI Below: