Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Manage and Monitor SQL Server - Lots of demos!
 

Automate SQL Server multi-file database restores on Linux


By:   |   Read Comments   |   Related Tips: More > 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.

operations studio

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

ubuntu

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.

ubuntu

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

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 " }'			
restore

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" }'
			
disk

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.

orocessed

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

linux

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


Last Update:


next webcast button


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





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools