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

Solution:

In this blog post, we will discuss how to automatically build a Linux command to restore a SQL Server database that has multiple data files. We will use the FILELISTONLY command to get information from the database backup files.

First, let’s understand the database and how many files it has based on the backup. We can use the following command:

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

When running this command using sqlcmd in Linux, the output may not be formatted neatly. To automatically build a Linux command to format the output better, we can use the awk command.

Once we have the logical file names, we can design the restore script. The basic T-SQL command to restore a multi-file backup is as follows:

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

To build this command using Linux commands, we can use the following steps:

1. Get the file list from the backup using the following command:

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

2. Remove the unrequired lines:

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

3. Add single quotes around the file names:

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

4. Add the T-SQL commands:

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

5. Create a shell script to build the SQL Server database restore 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
 
# 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

By following these steps, we can automate the multi-file SQL Server database restore process in Linux.

Remember to clean up the temporary files after the restore process is complete. You can add the following command to the script:

rm /tmp/restore_*

Documenting the commands and scripts you create is essential for future reference. You can create a summary page or register to keep track of your automation tools.

Automation Summary Register:

By following these steps and using the provided shell script, you can automate the process of restoring a multi-file SQL Server database in Linux.

Remember to test the script thoroughly before using it in a production environment.