Automate SQL Server Backup and Restore Tasks in Linux

Problem

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.

Solution

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

1. You have a Linux system; these screen shots are from Ubuntu 16.04.
2. You have SQL Server installed. See this tip to install SQL Server on Linux.
3. 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.

whereis sqlcmd

The response will most likely be:

sqlcmd: /opt/mssql-tools/bin/sqlcmd

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:

sh connect.sh

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`

Explanation:

• 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.

FILE_NAME=`basename $BACKUP_NAME`

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 SQL Server Agent

I suggest that you use the SQL Server Agent to automate the backup task.

The SQL Server Agent is available on Unix.

Here are more tips on SQL Server on Linux.

Conclusion

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

Next Steps

• 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.