Issue
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
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)
Solution
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
Answered By - fanta759 Answer Checked By - Cary Denson (WPSolving Admin)