SQL Database Backup -Powershell

This section contains scripts that hMailServer has contributed with. hMailServer 5 is needed to use these.
Post Reply
gotspatel
Senior user
Senior user
Posts: 372
Joined: 2013-10-08 05:42
Location: INDIA

SQL Database Backup -Powershell

Post by gotspatel » 2024-06-19 12:57

Greetings..

I wanted to share SQL Database Script which does below tasks, it is 99.00% Complete (Just needs some reorganization and trimming)

You are welcome to use it as it is, suggest better approach/modifications, and bugs if you encounter any (I sincerely Hope you will not have to :lol: )

Most of Thanks goes to our Poweshell guru @Palinka (for inspiration and also I copy pasted lot of code from Hmailserver Backup Script HERE)
And also Thanks to @SorenR, @Rvdh, @Mattg (For I utilized logic of VBScript from their code and used in powershell)

## Purpose:
The PowerShell script automates database backup tasks for MSSQL OR MySQL (MySQL or Mariadb) databases, including archiving/ moving, Pruning and NAS backups with logging and Email Status.

## Features:
- Loads supporting files for configuration and functions.
- Validates and selects the type of database to backup (MSSQL or MySQL).
- Retrieves system and database-specific variables.
- Installs Necessary requirements 7-zip, 7za.exe, Powershell SQL Module etc.
- Cleans up old files and creates necessary directories.
- Initiates database backup based on user input.
- % can be used as wildcard character in Database Name
- Creates archives if enabled, prunes backups, and performs NAS backups.
- Reports backup success and any errors.
- Checks for updates (commented out). YET Figuring out how to use GITHUB.
- Sends email notifications with backup results.

## Key Components:
- `SQLBackupConfig.ps1`: Configuration file for database backup settings.
- `SQLBackupFunctions.ps1`: Functions for database backup operations.

## Initial Setup Requirements:
## COMMON
- MSSQL/MYSQL/MariaDB Installed
- Working Mail Setup

## WINDOWS
-Pending

## Linux
- Needs Powershell minimum Version 5 Installed
- Run command "which pwsh" and get the location to powershell
- Change the first line for all the three script files "#!/snap/bin/pwsh -Command" with this location for eg: "#!/usr/bin/pwsh -Command"
- IMPORTANT: It has to be the First line in the file adn without any leading or trailing spaces
- The Script need to be executable by running command below
chmod +x /your/path/to script/SQLBackup.ps1

## EXAMPLE
## MANUAL RUN
.\SQLBackup.ps1 mssql
Runs the script to initiate backup operations for MSSQL databases.

.\SQLBackup.ps1 mysql
Runs the script to initiate backup operations for MySQL databases.

## Linux Cron details
With CRON DEBUG just in case you need to check
MIN HOUR DOM MON DOW CMD
50 12 * * * /snap/bin/pwsh "/mnt/scripts/SQLBackup.ps1 mysql" >> /mnt/scripts/cron.log 2>&1

Without CRON DEBUG
MIN HOUR DOM MON DOW CMD
50 12 * * * /snap/bin/pwsh "/mnt/scripts/SQLBackup.ps1 mysql"

## Windows Task Scheduler

Run SQLBackup.ps1 mssql OR SQLBackup.ps1 mysql from task scheduler

## I have Testing it working on Windows 10, 11, Server 2016, 2019, 2022 & Linux Ubuntu (desktop & Server), Debian, Centos, AlmaLinux, Red Hat (desktop & Server), Mint

Your message contains 108144 characters.
The maximum number of allowed characters is 60000.

Files Attached herewith :lol:

SQL_Backup.zip Size: 21.50 KB SHA256: 02e4de5fdebf8f074ec09d03596b3a64de84981cddf30f3f68a0d67725267541
Attachments
SQL_Backup.zip
Initial
(21.5 KiB) Downloaded 12 times
Last edited by gotspatel on 2024-06-19 13:02, edited 2 times in total.

gotspatel
Senior user
Senior user
Posts: 372
Joined: 2013-10-08 05:42
Location: INDIA

Re: SQL Database Backup -Powershell

Post by gotspatel » 2024-06-25 13:21

## Change Log: 01

1. Minor Code revision in function Install-7Zip when 7-zip is installed but 7za.exe not available
2. Typo rectified in function Set-SevenZipExePath
3. Time calculation updated in function BackupDatabases for MSSQL case

Size: 15.34 KB SHA256: 94915fc18647cfaf88c5dc967ad5d313e3dfc6c92e1d8161a200110c0a696090
Attachments
SQLBackupFunctions.7z
Replace the Functions File
(15.34 KiB) Downloaded 7 times

Post Reply