Bash Script to Automate SQL Server Database Backups with mssql-scripter

mssql_dump.sh

#!/bin/bash
###################################################################
#Script Name    : mssql_dump.sh
#Description    : will create SQL scripts as defined by mssql-scripter
#                 and will zip backup files and store for X days (set to 30)
#Author         : Tim Moses
#Github         : https://github.com/tjmoses
#Notes          : Make sure to add the appropriate MSSQL_SCRIPTER_CONNECTION_STRING
#                 for your db connection. Also, revise your target & edition
#                 sql server for the mssql-scripter command below. The zip library is
#                 required, but you can easily add another method to the code below.
###################################################################
current_date_time="$(date +%Y_%m_%d)";
date_time_min="$(date +%Y_%m_%d_%m)";
filename="create_${current_date_time}.sql";
zip_foldername="database_${current_date_time}.zip";
log_filename="db_scripts.log"
backup_dirname="db_backups"
curr_day=$(date +%d);
daystokeep=30;

mkdir -p $backup_dirname; # create the db directory if it doesn't exist
touch $log_filename; # create the log file if it doesn't exist

echo "Check ${log_filename} for current output & errors. working...";
echo -e "\n------------- Start of Log Date: ${date_time_min} -------------" >> $log_filename;
# set environment variables and start you DB Dump Create Scripts from any database (edit this template data)
export MSSQL_SCRIPTER_CONNECTION_STRING='Server=myserver;Database=mydb;User Id=myuser;Password=mypassword;'
mssql-scripter \
  --schema-and-data \
  --display-progress \
  --target-server-version "2014" \
  --target-server-edition "Standard" \
  --script-create \
  --exclude-objects SQLCLR_clrLogin_user > "./${backup_dirname}/${filename}" 2>> $log_filename;

cd $backup_dirname;

# Zip the backup file and remove the base
if zip "$zip_foldername" "$filename"
then
  rm "$filename" "$last_months_zip_filename";
  # remove files older than the days specified
  find ./ -mtime +$daystokeep -delete
  echo -e "**** File zipped successfully ****" >> "../${log_filename}";
else
  echo -e "\n**** ERROR: File did not zip! ****" >> "../${log_filename}";
fi
cd ../

should I install zip library on windows or wsl ?

Setting up WSL should work, but I've only tested it on a linux machine.