Last active
June 17, 2022 07:02
-
-
Save tjmoses/45ee6b3046be280c9daa23b0f610f407 to your computer and use it in GitHub Desktop.
Bash Script to Automate SQL Server Database Backups with mssql-scripter
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 ../ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Setting up WSL should work, but I've only tested it on a linux machine.