This is a .net windows service written in c# that automates backing up from remote database services.
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.
- 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
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 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
If the frequency is exactly 24 hours, the backup will run every day at 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:
|
| 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 |
| 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. |
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.
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.
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.
- Build or download the latest release.
- Update the appsettings.json file with the appropiate configurations for the databases you want backed up.
- 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"`
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.