3

I wrote a shell script to automate the download and restore of a SQL Server database. The script works great with one exception. It downloads the .bak file(s) that it is supposed to and then, if appropriate, initiates a database restore via SQLCMD. The restore executes as expected, but once it is complete, it never exits SQLCMD, so the script never completes.

~ UPDATE 1/24/23 - It turns out, the SQLCMD process does eventually exit. The restore completes (and reports success) in 5–30 seconds, then the script hangs as described below. I thought that that was the end of the story, but it turns out that after about another 10 minutes, the process actually does exit and the script proceeds. Setting the -t timeout flag does not alter the length of this pause. ~

The script starts by checking to see if there is a newer .bak file on the sftp server than the last one it downloaded. If there is, it downloads it and then executes the database restore. The RESTORE is based on a RESTORE scripted by Azure Data Studio's restore function.

One wrinkle is that in order to run a SQL Server database on my M2 Mac, it is running inside a docker container. I have no idea if this fact has any bearing on the situation.

Although I intend to automate the script via cron, I am currently running it manually in Terminal.

#!/usr/bin/zsh

### Download code goes here

tail_log_date=$(date +"%Y-%m-%d_%H-%M-%S")
new_latest_full=$(ls -r -1 /Path/to/container/var/opt/mssql/sldbBackup/full | head -1)
new_latest_diff=$(ls -r -1 /Path/to/container/var/opt/mssql/sldbBackup/diff | head -1)

docker exec container_name opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <password> -d my_database -Q "USE [master] ALTER DATABASE [my_database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE BACKUP LOG [my_database] TO  DISK = N'/var/opt/mssql/data/my_database_LogBackup_${tail_log_date}.bak' WITH NOFORMAT, NOINIT,  NAME = N'my_database_LogBackup_${tail_log_date}', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5 RESTORE DATABASE [my_database] FROM  DISK = N'/var/opt/mssql/sldbBackup/full/${new_latest_full}' WITH  FILE = 1,  MOVE N'mscrm' TO N'/var/opt/mssql/data/my_database.mdf',  MOVE N'mscrm_log' TO N'/var/opt/mssql/data/my_database_log.ldf',  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5 RESTORE DATABASE [my_database] FROM  DISK = N'/var/opt/mssql/sldbBackup/diff/${new_latest_diff}' WITH  FILE = 1,  NOUNLOAD,  STATS = 5 ALTER DATABASE [my_database] SET MULTI_USER;"

The RESTORE executes successfully with the following output:

Changed database context to 'master'.
Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
56 percent processed.
100 percent processed.
Processed 227 pages for database 'my_database', file 'mscrm_log' on file 1.
BACKUP LOG successfully processed 227 pages in 0.006 seconds (294.921 MB/sec).
5 percent processed.
10 percent processed.
15 percent processed.
20 percent processed.
25 percent processed.
30 percent processed.
35 percent processed.
40 percent processed.
45 percent processed.
50 percent processed.
55 percent processed.
60 percent processed.
65 percent processed.
70 percent processed.
75 percent processed.
80 percent processed.
85 percent processed.
90 percent processed.
95 percent processed.
100 percent processed.
Processed 1960600 pages for database 'my_database', file 'mscrm' on file 1.
Processed 478 pages for database 'my_database', file 'mscrm_log' on file 1.
RESTORE DATABASE successfully processed 1961078 pages in 15.529 seconds (986.600 MB/sec).
5 percent processed.
11 percent processed.
17 percent processed.
20 percent processed.
26 percent processed.
31 percent processed.
37 percent processed.
40 percent processed.
46 percent processed.
52 percent processed.
55 percent processed.
60 percent processed.
66 percent processed.
72 percent processed.
75 percent processed.
81 percent processed.
87 percent processed.
92 percent processed.
95 percent processed.
100 percent processed.
Processed 17592 pages for database 'my_database', file 'mscrm' on file 1.
Processed 59 pages for database 'my_database', file 'mscrm_log' on file 1.
Converting database 'my_database' from version 869 to the current version 957.
Database 'my_database' running the upgrade step from version 869 to version 875.
Database 'my_database' running the upgrade step from version 875 to version 876.
Database 'my_database' running the upgrade step from version 876 to version 877.
Database 'my_database' running the upgrade step from version 877 to version 878.
Database 'my_database' running the upgrade step from version 878 to version 879.
Database 'my_database' running the upgrade step from version 879 to version 880.
Database 'my_database' running the upgrade step from version 880 to version 881.
Database 'my_database' running the upgrade step from version 881 to version 882.
Database 'my_database' running the upgrade step from version 882 to version 883.
Database 'my_database' running the upgrade step from version 883 to version 884.
Database 'my_database' running the upgrade step from version 884 to version 885.
Database 'my_database' running the upgrade step from version 885 to version 886.
Database 'my_database' running the upgrade step from version 886 to version 887.
Database 'my_database' running the upgrade step from version 887 to version 888.
Database 'my_database' running the upgrade step from version 888 to version 889.
Database 'my_database' running the upgrade step from version 889 to version 890.
Database 'my_database' running the upgrade step from version 890 to version 891.
Database 'my_database' running the upgrade step from version 891 to version 892.
Database 'my_database' running the upgrade step from version 892 to version 893.
Database 'my_database' running the upgrade step from version 893 to version 894.
Database 'my_database' running the upgrade step from version 894 to version 895.
Database 'my_database' running the upgrade step from version 895 to version 896.
Database 'my_database' running the upgrade step from version 896 to version 897.
Database 'my_database' running the upgrade step from version 897 to version 898.
Database 'my_database' running the upgrade step from version 898 to version 899.
Database 'my_database' running the upgrade step from version 899 to version 900.
Database 'my_database' running the upgrade step from version 900 to version 901.
Database 'my_database' running the upgrade step from version 901 to version 902.
Database 'my_database' running the upgrade step from version 902 to version 903.
Database 'my_database' running the upgrade step from version 903 to version 904.
Database 'my_database' running the upgrade step from version 904 to version 905.
Database 'my_database' running the upgrade step from version 905 to version 906.
Database 'my_database' running the upgrade step from version 906 to version 907.
Database 'my_database' running the upgrade step from version 907 to version 908.
Database 'my_database' running the upgrade step from version 908 to version 909.
Database 'my_database' running the upgrade step from version 909 to version 910.
Database 'my_database' running the upgrade step from version 910 to version 911.
Database 'my_database' running the upgrade step from version 911 to version 912.
Database 'my_database' running the upgrade step from version 912 to version 913.
Database 'my_database' running the upgrade step from version 913 to version 914.
Database 'my_database' running the upgrade step from version 914 to version 915.
Database 'my_database' running the upgrade step from version 915 to version 916.
Database 'my_database' running the upgrade step from version 916 to version 917.
Database 'my_database' running the upgrade step from version 917 to version 918.
Database 'my_database' running the upgrade step from version 918 to version 919.
Database 'my_database' running the upgrade step from version 919 to version 920.
Database 'my_database' running the upgrade step from version 920 to version 921.
Database 'my_database' running the upgrade step from version 921 to version 922.
Database 'my_database' running the upgrade step from version 922 to version 923.
Database 'my_database' running the upgrade step from version 923 to version 924.
Database 'my_database' running the upgrade step from version 924 to version 925.
Database 'my_database' running the upgrade step from version 925 to version 926.
Database 'my_database' running the upgrade step from version 926 to version 927.
Database 'my_database' running the upgrade step from version 927 to version 928.
Database 'my_database' running the upgrade step from version 928 to version 929.
Database 'my_database' running the upgrade step from version 929 to version 930.
Database 'my_database' running the upgrade step from version 930 to version 931.
Database 'my_database' running the upgrade step from version 931 to version 932.
Database 'my_database' running the upgrade step from version 932 to version 933.
Database 'my_database' running the upgrade step from version 933 to version 934.
Database 'my_database' running the upgrade step from version 934 to version 935.
Database 'my_database' running the upgrade step from version 935 to version 936.
Database 'my_database' running the upgrade step from version 936 to version 937.
Database 'my_database' running the upgrade step from version 937 to version 938.
Database 'my_database' running the upgrade step from version 938 to version 939.
Database 'my_database' running the upgrade step from version 939 to version 940.
Database 'my_database' running the upgrade step from version 940 to version 941.
Database 'my_database' running the upgrade step from version 941 to version 942.
Database 'my_database' running the upgrade step from version 942 to version 943.
Database 'my_database' running the upgrade step from version 943 to version 944.
Database 'my_database' running the upgrade step from version 944 to version 945.
Database 'my_database' running the upgrade step from version 945 to version 946.
Database 'my_database' running the upgrade step from version 946 to version 947.
Database 'my_database' running the upgrade step from version 947 to version 948.
Database 'my_database' running the upgrade step from version 948 to version 949.
Database 'my_database' running the upgrade step from version 949 to version 950.
Database 'my_database' running the upgrade step from version 950 to version 951.
Database 'my_database' running the upgrade step from version 951 to version 952.
Database 'my_database' running the upgrade step from version 952 to version 953.
Database 'my_database' running the upgrade step from version 953 to version 954.
Database 'my_database' running the upgrade step from version 954 to version 955.
Database 'my_database' running the upgrade step from version 955 to version 956.
Database 'my_database' running the upgrade step from version 956 to version 957.
RESTORE DATABASE successfully processed 17651 pages in 3.793 seconds (36.356 MB/sec).

The script then stops there. I don't actually need the script to do anything else after the restore, just exit. But testing with echo "All done!" following the restore portion confirms that nothing happens after the restore. Terminal never returns to the prompt, and I am forced to exit with ctrl+C

Screenshot showing the final two lines of the output above followed by the shell cursor.

Running SQLCMD using the -b "exit on error" flag makes no difference (no surprise as there is no error, but worth a try). A simple SELECT query substituted where the RESTORE script is does exit after completion, so the issue seems to lie in the difference between those two processes.

Unlikely to be relevant: The RESTORE occurs inside an IF condition. Running the RESTORE not in a conditional statement does not change the outcome.

  • SQL Server 2022
    • Microsoft SQL Server 2022 (RTM-CU11) (KB5032679) - 16.0.4105.2 (X64) Nov 14 2023 18:33:19 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 22.04.3 LTS)
  • Mac OS Sonoma
    • 14.2.1 (23C71)
  • OrbStack
    • Version 1.3.0 (16556)
Improve this question
3
  • Do you know if it's reading from stdin when it hangs? If so, a </dev/null may suffice to clear things up. Commented Jan 22, 2024 at 18:17
  • I don't know. Based on another SO answer, I did try adding < /dev/null to the end of the line with no results. That is, I tried all of [space]<[space]/dev/null, [space]</dev/null, and <[space]/dev/null immediately following SET MULTI_USER;" ( so, …SET MULTI_USER;" </dev/null) with no results in any case. Commented Jan 22, 2024 at 21:16
  • That answers the question well enough -- rules out that potential cause effectively, thank you. Commented Jan 23, 2024 at 13:01

2 Answers 2

Reset to default
2

I have the same issue and i resolve it with version 18 of command line tools : https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools?tabs=ubuntu-install&view=sql-server-ver16#ubuntu

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I cannot edit this answer to provide the steps I used to update the SQL Server command line tools, because there are "too many pending edits", and my explanation ran over the max length of a comment, but this is the correct answer. After upgrading the SQL Server CL tools from v.17 to v.18, SQLCMD now exits immediately upon completing the restore and allows the remainder of the script to progress.
Note, because version 18 uses an encrypted connection by default I had to set the -C trust server certificate flag on SQLCMD.
-1

Since I'm running inside docker container without permission to install apt packages (also because I don't want to make another Dockerfile to persist the installation of mssql-tools18 and unixodbc-dev, which takes 400mb+ already as big as the whole compressed image), here is what I use:

the sqlcmd restore command which reproduce this problem:

/opt/mssql-tools/bin/sqlcmd \
  -S mssql -U 'user' -P 'pass' \
  -Q "RESTORE DATABASE mydatabase FROM DISK = '/path/to/backup.bak' WITH RECOVERY, REPLACE "

sqlcmd version:

user@mydocker:/$ /opt/mssql-tools/bin/sqlcmd -?
Microsoft (R) SQL Server Command Line Tool
Version 17.10.0001.1 Linux
Copyright (C) 2017 Microsoft Corporation. All rights reserved.

the solution:

grep -m1 'RESTORE DATABASE successfully processed' <( command_to_restore_here 2>&1 )

final solution:

grep -m1 'RESTORE DATABASE successfully processed' <( /opt/mssql-tools/bin/sqlcmd \
  -S mssql -U 'user' -P 'pass' \
  -Q "RESTORE DATABASE mydatabase FROM DISK = '/path/to/backup.bak' WITH RECOVERY, REPLACE " 2>&1 )

Explanation:

  • grep -m1 'RESTORE DATABASE successfully processed' <( sqlcmd ... ) = use grep to forcefully terminate process substitution sqlcmd ... after RESTORE DATABASE successfully processed string is found (taken from https://unix.stackexchange.com/a/679669 )
  • 2>&1 = in case the string RESTORE DATABASE successfully processed is not outputted to STDOUT but to STDERR (I haven't checked yet which is the output, just to be safe; so that it's output still grep'ed)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.