Automate SQL Server Backup and Restore Tasks in Linux
You have several SQL Server tasks already running and working on Windows and now you want to repeat those SQL Server tasks in Linux. For example, you would like to kick off a restore of a few databases. But how? Here are some script ideas to get you started.
Linux shell scripts are easy and flexible, when you know how. This tip is a step by step example for you. In this case we will use a Linux ‘shell script’ to control along with sqlcmd and then automate it. The Linux sqlcmd is like the Windows version. See this tip if you are unfamiliar with sqlcmd.
Connect to SQL Server using a Linux script
Let’s build an example script carefully, step by step.
VIP: Go one step at a time. If a step does not work re-read it and try again.
Build your environment
- You have a Linux system; these screen shots are from Ubuntu 16.04.
- You have SQL Server installed. See this tip to install SQL Server on Linux.
- Sqlcmd will need to be in your environment PATH for it to be accessible to these scripts.
Log into Linux and run this command to see if you have the SQL Server tools installed.
env | grep PATH | grep mssql-tools
If nothing is listed then install the SQL Server utilities. Follow the tips in the SQL Server on Linux Tips category on installing SQL Server on Linux.
And follow this tip to add the folder to your PATH environment variable.
This command will tell you where sqlcmd exists.
The response will most likely be:
Step 1 - Build a simple connection script to your SQL Server instance.
Here is a simple script.
#!/bin/bash # File name : connect.sh # Check system is up sqlcmd -S localhost -U sa -P XXXXX -Q"Select @@version,getdate(),@@Servername"
Tips: The #!/bin/bash is a convention in script writing. It shows where the shell you are using is located.
The other lines beginning with # are comment lines. Name your scripts at the top and save them first in an organized folder. Then add the code. I find that order of doing things clarifies your scripts purpose and keeps you on track.
You can use another user rather than sa, but we will be restoring some databases and so we will use sa.
Save the file. Then run that command by:
Or make it executable by the chmod command:
chmod a+x connect.sh connect.sh
Success! We can see the version, date and instance name. We know sqlcmd is installed and working and we have a valid username and password.
Tips: Note that the ~ or tilde indicate the path to your login folder. So ~/bin means your local bin folder.
Now proceed to step 2.
Step 2 - Let’s find the most recent backup we have in the backup area.
Let’s imagine you have production databases you wish to restore to test or development SQL Servers on Linux.
This Linux command will find the most recent backup in a backup area:
BACKUP_NAME=`ls -t1 /files/secured/Backups/Admin/Admin_backup_* | head -n 1` echo $BACKUP_NAME FILE_NAME=`basename $BACKUP_NAME`
- The commands within the backticks (`) will cause the output to be set to the given variable name.
- The command of ls -t1 will list the backup files in one column in time order.
- The variable $BACKUP_NAME holds the path and the file name.
- The variable $FILE_NAME only holds the file name.
Even though we could sort on the backup file names it is best to let the operating system list the files in order. Because a file may be named with a date that is not the date the backup ran.
The pipe symbol (|) sends the output to the “head -n 1” command and that returns only the most recent file name.
I like to use capital letters for variables. Remember the back ticks execute the code.
Tip: Make sure you have no spaces around the equals sign when you set a variable.
BACKUP_NAME=`ls -t1 /files/secured/Backups/Admin/Admin_backup_* | head -n 1`
The basename command gets only the file name not the path.
So now $FILE_NAME now holds the most recent backup file name of: Admin_backup_2018_1_3_21_04_09.BAK
Step 3 - Copy the backup to the standard location
# Copy the file sudo cp -u $BACKUP_NAME /var/opt/mssql/backup/
The sudo command will ask for our password then copy the source file to the standard SQL Server backup location.
And if that location does not exist you will need to create it, but only once.
sudo mkdir /var/opt/mssql/backup
The -u “copy only when the SOURCE file is newer than the destination file or when the destination file is missing”.
Step 4 - Use the Linux variables and track what we do.
SQL Server keeps records in msdb, see this tip to track backups we have restored.
And now we can use Linux variables in a T-SQL script.
If it helps your work flow, first check to see if we have already loaded that backup. If a file has not been restored we restore it.
# Restore sqlcmd -S localhost -U sa -P XXXXX -Q" if not exists ( Select [physical_device_name] from msdb..backupmediafamily where physical_device_name='$BACKUP_NAME') BEGIN RESTORE DATABASE Admin FROM DISK = '/var/opt/mssql/backup/$FILE_NAME' WITH MOVE 'Admin' TO '/var/opt/mssql/data/Admin.mdf', MOVE 'Admin_Log' TO '/var/opt/mssql/data/Admin_Log.ldf' END GO "
Well done. We have automated a recovery.
Step 5 - More automation
Let’s list the full script and think: How can we improve it?
#!/bin/bash # File name : load_database.sh # Author : Graham Okely B App Sc (IT) # Get latest backup BACKUP_NAME=`ls -t1 /files/secured/Backups/Admin/Admin_backup_* | head -n 1` echo $BACKUP_NAME FILE_NAME=`basename $BACKUP_NAME` # Copy the file sudo cp -u -p $BACKUP_NAME /var/opt/mssql/backup/ # Restore sqlcmd -S localhost -U sa -P XXXXX -Q" if not exists ( Select [physical_device_name] from msdb..backupmediafamily where physical_device_name='$BACKUP_NAME') BEGIN RESTORE DATABASE Admin FROM DISK = '/var/opt/mssql/backup/$FILE_NAME' WITH MOVE 'Admin' TO '/var/opt/mssql/data/Admin.mdf', MOVE 'Admin_Log' TO '/var/opt/mssql/data/Admin_Log.ldf' END GO"
Where can we automate? What about any place we have the database name?
If we replace the database name with another variable we can restore any, non-system, database.
The same script with more automation. The $1 variable below means the first argument supplied to the script.
#!/bin/bash # File name: load_database.sh # Usage: load_database.sh database_name # Author: Graham Okely B App Sc (IT) # Get latest backup BACKUP_NAME=`ls -t1 /files/secured/Backups/$1/$1_backup_* | head -n 1` echo $BACKUP_NAME FILE_NAME=`basename $BACKUP_NAME` # Copy the file sudo cp -u -p $BACKUP_NAME /var/opt/mssql/backup/ # Restore sqlcmd -S localhost -U sa -P XXXXX -Q" if not exists ( Select [physical_device_name] from msdb..backupmediafamily where physical_device_name='$BACKUP_NAME') BEGIN RESTORE DATABASE $1 FROM DISK = '/var/opt/mssql/backup/$FILE_NAME' WITH MOVE '$1' TO '/var/opt/mssql/data/$1.mdf', MOVE '$1_Log' TO '/var/opt/mssql/data/$1_Log.ldf' END GO"
Notice that the database file names in the database backup will need to be named Admin and the log file name Admin_Log for this script to work. If you have simple two file databases (i.e. database and transaction log) that were created with the default naming then it will work. Databases with more than two files will require extra scripting on your part.
This, more automated, script is run by:
sh load_database.sh Admin
And if we want to load many databases we can use several commands.
sh load_database.sh Admin sh load_database.sh Dictionary sh load_database.sh Creativity
In each case the $1 variable holds the first parameter.
The variable $1 will hold the database name provided on the command line at the time you run the script.
Note: the databases are restored, but user logins may still need to be repaired.
Step 6 - Fix a user
You possibly will be restoring production databases to test and development servers. In that case automating the user security would be a good idea.
Let’s build a simple script to do that. The usage would be:
fix_account.sh Database_Name user_name
Here is the full script.
# File name : fix_account.sh # Author : Graham Okely B App Sc (IT) echo "Fixing account" # During testing the sqlcmd was not in the environment path variable # so it was fully specified here. /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P XXXXX -Q" Use [$1] go EXEC sp_change_users_login 'Auto_Fix',’$2’ GO CREATE ROLE [db_executor] AUTHORIZATION [dbo] GO GRANT EXECUTE TO [db_executor] GO EXEC sp_addrolemember N'db_executor', N'$2' GO "
Step 7 - Automate with Crontab
Crontab is the Linux job scheduling system. Each user, including root can have their own crontab task schedule.
Issue this command to list your crontab tasks, if they exist.
Issue this command to edit your crontab tasks.
The -e flag will start editing your crontab file. See this tip on the vi editor.
A crontab task is held on one line and each part is separated by white space of some sort. The structure is:
minute hour day month week command
So we could automate the database restore of the Admin database in test for every Monday morning like so:
crontab -e 01 06 * * 1 ~/bin/load_database.sh Admin
Tip: Sometimes we spend a long time creating an automation tool. If you forget you created, debugged, tested and used it then you lose the efficiency gained by automation. A tip to help you remember is to create some system to remind you.
Reminder tip #1 - Each time you log in get a list of your automation tools.
Add this command to the bottom of your ~/.bashrc file.
ls -l ~/bin
Reminder Tip #2 - A Wiki page entry, a summary page or something like this register.
Automation Summary Register
|1||~/bin (When logged in as joe)||load_database.sh||Database_Name||Restores the most recent backup in /files/secured/Backups/Database_Name To the default instance on localhost.|
|2||~/bin (When logged in as smith)||Connect.sh||None||The very simplest way I can check if the SQL Server instance is running.|
|3||~/bin (When logged in as smith)||fix_account.sh||Database_Name user_name||Fixes orphans and adds executor role.|
- Think about adding the call to the fix_account.sh script from the load_database.sh script.
- What commands have you built? Are they in a summary register or displayed when you log in?
- Check out more operating system commands available to you.
- Check out more SQL Server and Linux tips.
Last Updated: 2018-02-21
About the author
View all my tips