Automate SQL Server Backup and Restore Tasks in Linux


By:   |   Updated: 2020-10-15   |   Comments (6)   |   Related: More > SQL Server on 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
check sql server tool installation linux

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
			
run linux command

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.
backup sql server database on linux

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.

ssms databases

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

# Location Command Parameters Description
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.
Next Steps


Last Updated: 2020-10-15


get scripts

next tip button



About the author
MSSQLTips author Graham Okely Graham Okely is a contract SQL Server DBA and has been working with database systems since 1984 and has been specializing in SQL Server since 2007.

View all my tips
Related Resources





Comments For This Article




Wednesday, September 16, 2020 - 3:31:24 AM - Graham Okely Back To Top (86485)
Hi Muneeb
Check this link it lists limitations for Linux and SQL Server Agent jobs.
https://cloudblogs.microsoft.com/sqlserver/2017/03/17/sql-server-on-linux-running-jobs-with-sql-server-agent/

Regards
Graham

Tuesday, September 15, 2020 - 10:29:18 AM - Muneeb Ashraf Back To Top (86480)
Hi Graham,
Thanks for the reply. Can i use Linux commands like mkdir , tar in SQL agent job?.... Just learned that in Windowsi "CmdExec" does OS tasks but probably in Linux based SQL currently not available
i think i may have to schedule my Linux commands and SQL agent job to run 1 by 1.

Thanks again

Tuesday, September 15, 2020 - 9:05:51 AM - Graham Okely Back To Top (86479)
Hi Muneeb

Thanks for your question.
I have not found a simple way of encrypting passwords when using crontab.

I suggest that you use the SQL Server Agent to the backup task.
And all regular SQL Server tasks.
The SQL Server Agent is available on Unix.
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-sql-agent?view=sql-server-ver15

And yes, you can create a stored procedure to do backups.
This site has well tested maintenance system: https://ola.hallengren.com/

Regards Graham Okely

Tuesday, September 15, 2020 - 7:35:31 AM - Muneeb Ashraf Back To Top (86473)
Hi,
im using SQL server on linux and uptill now using backup with Cronjob but for audit requirements want to automate without mentioning clear text password in script... im aware of Environment variable usage but it still would be visiblle... My current bash script creates a folder with respective date, backs up databases , creates TAR and deletes folder... In Oracle we can run a shell script in Procedure but in SQL server linux im not sure we can do that.... It would be great if you can guide

Muneeb

Thursday, February 22, 2018 - 9:06:26 PM - Graham Okely Back To Top (75283)

Hi Reza

Thanks for your reply. Note that in the article I say "Databases with more than two files will require extra scripting on your part." I have shown the simplest example. If you have multiple files you will need to adjust the script.

I will consider your request about future articles.

Regards

Graham


Thursday, February 22, 2018 - 10:51:14 AM - Reza Back To Top (75278)

Thanks for sharing. it is very practical and useful. my only concern is, in this case study we assume db has only two db files (data * log) in Restore action. ALSO, can you please provide some Lab and article in create multi-node sql server cluster on Ubuntu 16.04.

 



download





Recommended Reading

Restore a SQL Server database on a Linux based instance from a backup on a Windows instance

How to Change Default Data and Log file directory for SQL Server running on Linux

How to Stop, Start, Enable and Disable SQL Server Services on Linux

Configure SQL Server on Linux to Use Windows Authentication

How to Change SQL Server Instance Name Running on a Linux Server








get free sql tips
agree to terms