Bash-script to restore a mssql database on linux mssql server06.06.17

example bash script to find the latest backup from a remote server and download it.
than unzip the file and move it to a location where the sqlcmd utility can import it.
after the restart of the linux mssql server it will check for the version of the database(created table zz_version)

#!/bin/bash
systemctl restart mssql-server
scp root@192.168.1.66:`ssh root@8.8.8.8 ls -1td /mnt/sql_backup/SL/\* | head -1` "/var/opt/mssql/backup/zip/sl.zip"
cd /var/opt/mssql/backup/
unzip -o /var/opt/mssql/backup/zip/sl.zip -d /var/opt/mssql/backup/
rm /var/opt/mssql/backup/zip/sl.zip
mv /var/opt/mssql/backup/*.bak  /var/opt/mssql/backup/sl.bak
sqlcmd -S 127.0.0.1 -U SA -P PASSWORD -Q "RESTORE DATABASE SL FROM DISK=N'/var/opt/mssql/backup/sl.bak' WITH  FILE = 1,NOUNLOAD,  REPLACE,  STATS = 10"  
systemctl restart mssql-server
sqlcmd -S 127.0.0.1 -U SA -P PASSWORD -Q "SELECT * FROM sl.dbo.zz_version"

if you want to run this bash with a cronjob you need to declare the environment variable like
in the cronjob file

SHELL=/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/opt/mssql-tools/bin

15 4 * * *   root    /scripts/mssql_restore.sh

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-connect-and-query-sqlcmd

Anonymous

Bash-script to restore a mssql database on linux mssql server06.06.17

Information Epoch 1747244940