Remote SQL Backup Service

This is a .net windows service written in c# that automates backing up from remote database services.

Goals

Allow for backing up of databases hosted on remote platforms you may not have control over. In this case, I was hosting on SmarterASP.net and utilizing both Microsoft SQL Server and MySQL databases and wanted to make sure I had regular backups of my data away from the hosting provider in case anything goes sideways. There are numerous options with command line tools to create backups from remote database sources, but they do nothing to help with managing multiple backups, nameing the file based on the date and time, or compressing the output. This service aims to automate all of that.

This service was a quick and fun side-project for me to solve these problems - not intended to showcase the best programming practices.

Features

• Automatic File Naming based on Date and Time.
• Automatic ZIP File Compression.
• Compares backup output to previous output and removes the data if they're the same.
• Automatic File Rotation

Configuration

The service is configured using the appsettings.json by specifying one or more BackupSettings.

Each BackupSetting consists of:

Property	Type	Description
Name	String	The display name of the setting used for logging.
Progam	String	The full path to the program to execute without any arguments.
ProgramParameters	String	All the command line arguments to pass to the program to execute. Use %filename% to specify where the full path and file name should be entered. Keep in mind that full paths that include spaces should be quoted.
FrequencyTimeSpan	TimeSpan	How often to run the backup expressed as a TimeSpan. Values greater than 365 days or less than 30 minutes are not supported. If the frequency is less than 24 hours, the backup will reset every day starting at TimeOfDay. If the frequency is exactly 24 hours, the backup will run every day at TimeOfDay If the frequency is less than or equal to 15 days, the backup will run offset from the first day of the month. Any frequency greater than 15 days will run offset from the first day of the year. Examples: 1.0:00 1 Day (24 hours) 1:00 1 Hour 5.2:30 5 Days, 2 hours and 30 minutes.
TimeOfDay	TimeOnly	The time of day to start the backups - this is essentially an offset so backups don't all start at exacly midnight. The default value is 12:00 AM which means the backup will start at midnight.
KeepMostRecents	Integer	The number of most-recent backups to keep.
KeepDays	Integer	The number of daily backups to keep - where only the first backup of the day is kept.
KeepWeeks	Integer	The number of weekly backups to maintain - where only the earliest backup starting from Sunday is kept.
KeepMonths	Integer	The number of single montly backups to keep. Only the first backup of the month is kept.
BackupFileExtension	String	The extension of the backup file.
BackupFolder	String	The folder to keep all the backups in.
ZIPResults	Boolean	If true, the resulting file after the command is run will be zipped up.
IsZIP	Boolean	Used to indicate the output of the command is actually a zip file regardless of extension. Microsoft .bacpac files are ZIP files in discuise for example.

Output

The backup service runs the commands after replacing the %filename% placholder with a date and time name and extension. After the backup successfully completes, the file is compared to the previous backup file and if they're the same, the file is deleted and replaced with a placeholder empty file with .SameAsPrevious extension before the expected normal file extension. If the file is different, the file is kept and optionally zipped up. The service will then analize all files in the directory and remove any outside the scope of Keep values specified in the configuration, ensuring that files with actual data are maintained for any empty .SameAsPrevious files.

Program Execution Notes

The backup service will execute any program you want with whatever program parameters are needed. Care was taken with the example MSSQL and MySQL commands to ensure that the output is exactly the same on different runs so that the service can determine if the backup is the same as the previous backup. A common issue with backup programs is appending current dates or using unique GUIDs in the output, so that was avoided. HowToBackup.txt and HowToRestore.txt include some pointers on the configurations for those two backup methods included in the AppSettings.json file.

Installation

It wasn't a priority to create a windows installer for the service as it's more of a one-off, so here's command-line instructions.

1. Build or download the latest release.
2. Update the appsettings.json file with the appropiate configurations for the databases you want backed up.
3. Register and start the service with the following administrator commands replacing the folder location with the location of the RemoteSQLBackup.exe file:

sc.exe create "Remote SQL Backup" binpath="C:\Program Files\RemoteSQLBackup\RemoteSQLBackup.exe"`

sc.exe description "Remote SQL Backup" "Runs backups on remote SQL databases using the specified commands found in its config file."`

sc config "Remote SQL Backup" start=delayed-auto`

sc start "Remote SQL Backup"`

Development Note

dotnet/runtime#97558 means that the published file cannot be trimmed at this time as collections aren't being code generated correctly for configuration loading.