By: Graham Okely | Comments | Related: > SQL Server on Linux
Problem
How can you automate a multi file SQL Server database restore in Linux?
Solution
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' GO
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.
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.
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] GO 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 GO
Now let’s build that using Linux commands.
We can easily get the PhysicalName by getting the second file using awk. Use $2.
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:
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:
automated_restore_multi_file_database.sh
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.
#!/bin/bash
# File name : automated_restore_multi_file_database.sh
# Author : Graham Okely B App Sc (IT)
# Site : www.MSSQLTips.com
#
# 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) | 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. |
| 4 | ~/bin | automated_restore_multi_file_database.sh | DATABASE_NAME FILE_NAME | Restores a database from a backup when the database has many files. |
Next Steps
- Review my tip 5353 on automating basic database backups.
- A good Microsoft site on installing SQL Server on Ubuntu.
- Have a look at awk.
- See this tutorial for more details the FILELISTONLY command.
- See this tutorial for more details the RESTORE HEADERONLY command.
About the author
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
