Automate SQL Server multi-file database restores on Linux

By:   |   Comments   |   Related: > SQL Server on Linux


How can you automate a multi file SQL Server database restore in Linux?


In this tip I show how to automatically build a Linux command to restore a SQL Server database that has many data files.

Getting Information from Database Backup Files

First letís understand the database and how many files it has based on the backup.  We use the FILELISTONLY command. See this tutorial for more details.

RESTORE FILELISTONLY FROM DISK = N'/var/opt/mssql/data/AAA-2018317-13-10-38.bak'

TIP: When copying commands make sure you paste the right sort of single quote and the right spaces between commands.

Notice that SQL Server Operations Studio shows all the file information neatly.

operations studio

But if we run the command using sqlcmd in Linux it's a mess.


Let’s see how we can automatically build a Linux command to format this better.

Overview and Note on Using sqlcmd

This is something to be aware of:

Note we cannot use standard input into the sqlcmd at the current time ... we do not support reading from stdin for sqlcmd/bcp on *nix platforms for now. Currently fixing this is not in our near-term roadmap, however please file a connect item and if enough upvotes are received we will prioritize accordingly.

That means we cannot do this type of thing:

echo "Select @@Version" | sqlcmd -S localhost -U sa -P XXXXX

Therefore, using the -Q option we get a result from the command and the command exits.


As the results are too much, let’s pass that output using the awk command and just return the first column which is the logical file name. By default, awk uses a space as the column or field delimiter.

local host

Building the Database Restore Command

Now that we have the logical file names we can design the restore script.

My approach to writing Linux shell scripts:

  • Keep it simple.
  • Build the command line using pipes.
    • When it gets too big make a file, or shell script.
  • Use commands I know well.
  • When I can’t do something use a new command.

Let’s remind ourselves of the basic T-SQL command to restore a multi file backup.

USE [master]
RESTORE DATABASE [AAA] FROM  DISK = N'AAA-2018317-13-10-38.bak' WITH  FILE = 1,  
MOVE N'AAA' TO N'/var/opt/mssql/data/AAA.mdf',  
MOVE N'AAB' TO N'/var/opt/mssql/data/AAB.ndf',  
MOVE N'AAC' TO N'/var/opt/mssql/data/AAC.ndf',  
MOVE N'AAA_log' TO N'/var/opt/mssql/data/AAA_log.ldf',  NOUNLOAD,  STATS = 5

Now let’s build that using Linux commands.

We can easily get the PhysicalName by getting the second file using awk. Use $2.

physical name

We do not want the first two lines. We use the tail command:

sqlcmd -S localhost -U sa -P XXXXX -Q "RESTORE FILELISTONLY FROM DISK='AAA-2018317-13-10-38.bak';"  
| tail -n +3 			

Nor do we want the last two rows:

sqlcmd -S localhost -U sa -P XXXXX -Q "RESTORE FILELISTONLY FROM DISK='AAA-2018317-13-10-38.bak';"  
| tail -n +3 | head -n -2			

Now, using awk, let’s add single quotes around that.

sqlcmd -S localhost -U sa -P XXXXX -Q "RESTORE FILELISTONLY FROM DISK='AAA-2018317-13-10-38.bak';"  
| tail -n +3 | head -n -2 | awk '{ print "\x27"$1"\x27 \x27"$2"\x27 " }'			

Now we can add some T-SQL commands:

from disk

The awk command has a BEGIN and END sections.

sqlcmd -S localhost -U sa -P XXXXX -Q "RESTORE FILELISTONLY FROM DISK='AAA-2018317-13-10-38.bak';"  
| tail -n +3 | head -n -2 
| awk 'BEGIN { print "RESTORE DATABASE [AAA] FROM DISK " } { print "MOVE N\x27"$1"\x27 TO N\x27"$2"\x27, " } 
END { print "NO UNLOAD, STATS=5" }'

Creating a shell script to build SQL Server Database Restore Script

As the command is getting long it will be easier to place it into a file or shell script.

Good organization requires good naming. Let’s call this file:			

Yes, it is a long file name, but it's easy to know what it does!

I locate it in the ‘bin’ area even though it is not a binary. It makes sense to me and the path is in the PATH variable.

Step by Step

When building a Linux command script make it understandable to YOU!

Don’t complicate it to impress someone else because you may have to revisit it.

Here is the final command as text.

Note: Check quotes get copied over correctly if you copy this shell script.

# File name :
# Author : Graham Okely B App Sc (IT)
# Site :
# USAGE: automated_restore_multi_file_database DATABASE_NAME FILE_NAME
# Say what is happening
echo "Restoring $1 from the file $2"
# Get the file list from the backup
sqlcmd -S localhost -U sa -P XXXXX -Q "RESTORE FILELISTONLY FROM DISK='$2';"  > /tmp/restore_1_file_list.txt
# remove unrequired lines
cat /tmp/restore_1_file_list.txt | tail -n +3 | head -n -2 >/tmp/restore_2_trimmed.txt
# Add the TSQL around the file names
cat /tmp/restore_2_trimmed.txt |  awk 'BEGIN { print "RESTORE DATABASE ['$1'] FROM DISK= ~'$2'~ WITH FILE=1," } { print "MOVE N\x27"$1"\x27 TO N\x27"$2"\x27, " } END { print "NOUNLOAD, STATS=5" }' >/tmp/restore_3_TSQL.txt
# Note at this point it was too difficult to add single quotes in the command line above
# The return on effort was minimal so I went to another Linux utility: tr
# Replace ~ for single quotes
cat /tmp/restore_3_TSQL.txt | tr "~" "'" > /tmp/restore_4_final.sql
# Process the TSQL
sqlcmd -S localhost -U sa -P XXXXX -i /tmp/restore_4_final.sql
# display the TSQL
cat /tmp/restore_4_final.sql

Note: Check quotes get copied over correctly if you copy this shell script.

Here is the output from a run.


We can check via SQL Server Operations Studio to see the multi-file database has been restored.


Clean up

When Linux restarts it will clean up /tmp, but we could add this to the script.

rm /tmp/restore_*			

Document Minimally

As mentioned in my tip 5353 make a note of the command utilities you make.

Reminder tip #1 - Each time you login 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) 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) None The very simplest way I can check if the SQL Server instance is running.
3 ~/bin (When logged in as smith) Database_Name user_name Fixes orphans and adds executor role.
4 ~/bin DATABASE_NAME FILE_NAME Restores a database from a backup when the database has many files.
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms