Objective
By default, I already use the backup option on my VPS that is configurable per Cloud provider ( in my case, my Windows Servers run on Vultr and Linux servers on Hetzner). So I won’t go into detail on this.
The objective of this is to backup my data and sql databases automatically, since a complete system backup is not sufficient for my use-case.
Here are the requirements:
- Daily backup of data
- Daily backup of sql databases ( retention period 30 days)
- Able to manually start a backup task
- Backup should be accessible over a network share
- Crossplatform solution ( Windows + Linux )
Stack
-
rclone is a cross-platform cli that manages files locally and in the cloud. It connects to > 70 storage providers.
-
BackBlaze B2 is a cheap reliable backup storage. I’ve picked them because of their great blog posts concerning quarterly Hard Drive stats and opensourcing their Storage Pod. Their web UI however is underwhelming and I’ve noticed some bugs, but I won’t need that normally.
-
Task Scheduler - an underrated cronjob tool in Windows
-
Scripts (powershell, batch) to backup sql database, call rsync, …
Difference between a backup of ‘Data’ vs ‘database’
I don’t consider data and MS SQL databases to be similar.
‘Data’ are the additional assets that a web app generates, taking into account that ‘source-code’ is always under version control.
Example of data:
- Images uploaded by clients for product data
- Logs
- …
Data should be up to date, as I don’t need a backup from yesterday.
An ‘Sql Database’ is a versioned backup. I want to be able to return 30 days ago and compare different version if someone (customer/me) messed up.
Setup
For handling these 2 types of backups. The data will always be stored to the latest folder and the SQL databases will be stored to the latest folder AND the ‘{current-day}’ folder.
This is done by running a couple of scripts daily through Task Scheduler that backup and sync to the BackBlaze bucket:
-
/backup/data/{server-name@ip}/latest
-
/backup/sql/{server-name@ip}/latest
-
/backup/sql/{server-name@ip}/{current-day}
Since I use rclone, I can mount the storage provider to a networked drive. In this case, my BlackBlaze bucket will be mounted through ‘Task Scheduler’ when i boot my desktop. So I have immediate access to all the backups from a network drive.
Some notes:
-
I’ve considered zipping the files and then syncing, but that limits my options. Eg. Binary Diffs are supported in Rsync but not in Rclone (which has better support for cloud providers)
-
All production ms-SQL databases are backupped by default, except the system ones ( master, model, msdb, tempdb )
-
I used box.com in the past, but i’ve switched to backblaze a month ago
-
In my dev environment, I created some Linqpad scripts that restore my databases from a network share and change the data ( eg. changing emails to my own domain, …), …
-
Since I use this setup regularely, I don’t need a script to check if the backup ran based on the timestamp. YMMV.
Summary
This setup has been low-maintenance, low-change and has worked pretty well for me over the years. Eg:
- Being able to reproduce a production issue in dev under 2 minutes from any device that I own, has been a time-saver
- Switching from Box to BackBlaze was easy, since I mainly needed to change my config on my devices and my servers