Auto-Backup MariaDB Database (Windows)

AutoDBBackup.ps1

param(
  [Parameter(Mandatory, HelpMessage = 'Path to Maria DB Installation. C:\Program Files\MariaDB 10.9')]
  [string]$SQLPath,
  [Parameter(Mandatory, HelpMessage = 'Location to store backups. C:\DBServerBackups')]
  [string]$BackupDirectory,
  [Parameter(Mandatory, HelpMessage = 'SQL User with access to your DB.')]
  [string]$User,
  [Parameter(Mandatory, HelpMessage = 'Password for the SQL User.')]
  [string]$Pass,
  [Parameter(HelpMessage = 'Port Number for your SQL Instance. Default: 3306')]
  [int]$Port = 3306,
  [Parameter(Mandatory, HelpMessage = 'Database you are wanting to backup')]
  [string]$Database,
  [Parameter(Mandatory, HelpMessage = 'Maximum Age of backups. Will delete anything older.')]
  [string]$MaxBackupAge
)

$BackupDate = Get-Date -Format FileDate
$Filename = $Database + "_backup_" + $BackupDate

New-Item $BackupDirectory -ItemType Directory -Force | Out-Null

try {
  Set-Location "$SQLPath\bin"
  .\mysqldump.exe -P $Port -u $User -p"$Pass" $Database | Out-File "$BackupDirectory\$Filename.sql" -Encoding Ascii
}
catch {
  Write-Output("Backup Failed!")
}

try {
  $timespan = New-TimeSpan -days $MaxBackupAge;
  Get-ChildItem -Path $BackupDirectory -Filter *.sql | ForEach-Object {
    if (((Get-Date) - $_.CreationTime) -gt $timespan ){
      Remove-Item $_.FullName
    }
  }
}
catch {
  Write-Output("Failed to clean up backup directory.")
}

CreateTaskForPS.md

Auto DB Backup

Description

This script can be used to backup multiple databases by creating additional tasks. The steps below outline how to create a single task to back up one database. Simply duplicate the Create Task steps for as many databases as you wish to backup. Make sure to change the params to reflect the database you want to back up and the location for which you'd like to store those backups. I highly recommend individual backup locations for each database.

Parameters

• SQLPath - Path to your SQL installation (MySQL or MariaDB). C:\Program Files\MariaDB 10.9
• BackupDirectory - Where you want to save the backups.
• User - The SQL username that has access to query the database. Play with the permissions if you'd like. I'm a bad boy and use my Root account lol.
• Pass - Password for the user mentioned above.
• Port - SQL Port in use. Default is 3306
• Database - Name of the database you're wanting to backup.
• MaxBackupAge - Maximum age of backups in days.

To-Do

• Auto-purge backups based on a provided age. - Added on 12/31/2022
• Create a GUI to create these backup schedules and configure the options. Fully OSS (Raw PowerShell will always be available)

Initial Steps

You'll want to save the above script and then test it manually before creating a task. All errors will show in the console so that you are able to troubleshoot the issue.

The path specified in BackupDirectory will be created if it doesn't exist. Make sure to set the User, Password, and Database options in the below example. You can also specify a Port if you are not using the default 3306 with -Port. To change your port to 3307 you can use -Port 3307

1. Save the above code to a file with a .ps1 extension
2. Open Windows Terminal (store app) or PowerShell
3. Type `cd "C:\Path\To\Script"
4. Type .\ScriptName.ps1 -SQLPath "C:\Program Files\MariaDB 10.9" -BackupDirectory "C:\DBServerBackups" -User "" -Pass "" -Database ""

At this point you should have your first back up present in the folder provided. You can safely delete this as it was a test run. Continue on to the next section to create a task.

Note: If you get an error please take a look at the Common Errors section at the bottom of this page.

Creating a Task

First you'll want to open Task Scheduler. Do this by clicking on the Start menu and then typing Task Scheduler Next, create a new taks by Right-Clicking on Task Schedluer (Local) Item on the left and select Create Task

On this first screen the only setting you need to change is the Name. This can be what ever you'd like. E.g. MwG Outlaws

Next, you'll want to create the schedule for which this task runs on. To do so click on the Triggers Tab. At the bottom of the window Click New... You can define as many triggers as you'd like. For this example I created a weekly backup trigger.

Next, we'll want to create the action. This is where we configure the task to run the above PowerShell script to create a dump of the database. Click on the Actions Tab. You'll want to make sure Action is set to Start a program. In the settings section set the Program to powershell. You can also click browse and navigate to PowerShell mine is located in C:\Windows\System32\WindowsPowerShell\v1.0.

Next, we want to tell the task to execute our script. To do this we set the Add arguments option. Be sure to change the path to where you saved the script above. You'll also need to put in your SQL User, Password and Database you want to backup. -File "C:\Path\To\Script.ps1" -SQLPath "C:\Program Files\MariaDB 10.9" -BackupDirectory "C:\DBServerBackups" -User "" -Pass "" -Database "" -MaxBackupAge 10

Note: If you are not running MariaDB on the default port of 3306 you can specifiy the -Port parameter to set your port number. -Port 3308 would use the port 3308

Once you've configured the above line click OK to save the Action. And click OK again to create the task.

Now we are ready to test to make sure the task runs properly. Click on the Task Scheduler Library on the left pane and locate your created task.

Right-Click on your task and select run. This will force the task to run and should create your backup file in the provided location. Make sure to check that the contents of the file contains your entire Database.

Conclusion

This script does not currently support clearing backup files that are X days old however I will eventually get that added. Make sure you are cleaning up the directory to your liking.

troubleshooting.md

Common Errors

Execution Policy

Problem: cannot be loaded because running scripts is disabled on this system.

The default execution policy for Windows Server operating systems is already RemoteSigned so it should work without an issue. If you are running your server on Windows 10 or Windows 11 then you may run into the problem where scripts are disabled. This is a securty feature built into windows so we don't want to make it overly permissive.

Solution:

1. Open PowerShell as administrator
2. Run Set-ExecutionPolicy RemoteSigned

RemoteSigned The default execution policy for Windows server computers. Scripts can run. Requires a digital signature from a trusted publisher on scripts and configuration files that are downloaded from the internet which includes email and instant messaging programs. Doesn't require digital signatures on scripts that are written on the local computer and not downloaded from the internet. Runs scripts that are downloaded from the internet and not signed, if the scripts are unblocked, such as by using the Unblock-File cmdlet. Risks running unsigned scripts from sources other than the internet and signed scripts that could be malicious.

Microsoft Docs