CODEWITHSUNDEEP
sqlsql-serverdocker
PopeCoke
PopeCoke

Reputation: 1

Restoring database through docker

Currently learning SQL online. I've been trying to restore the database from this link:

http://app.sixweeksql.com:2000/SqlCourse.bak

when I run SQL Server through Docker (Mac user, can't run SSMS unfortunately). I've been following directions from Microsoft here:

https://learn.microsoft.com/en-us/sql/linux/tutorial-restore-backup-in-sql-server-container?view=sql-server-2017

I moved the file into my container and checked the files listed inside (Course New and CourseNew_log) so I could write out its file path:

sudo docker cp SqlCourse.bak container_name:/var/opt/mssql/backup

followed by:

sudo docker exec -it container_name /opt/mssql-tools/bin/sqlcmd -S localhost \
   -U SA -P "Password" \
   -Q 'RESTORE FILELISTONLY FROM DISK = "/var/opt/mssql/backup/SqlCourse.bak"'

Yet I just don't know how to restore the database. I've tried this:

sudo docker exec -it container_name /opt/mssql-tools/bin/sqlcmd \
   -S localhost -U SA -P "Password" \
   -Q 'RESTORE DATABASE SqlCourse FROM DISK = "/var/opt/mssql/backup/SqlCourse.bak" WITH MOVE "CourseNew" TO "/var/opt/mssql/data/SqlCourse.mdf", MOVE "CourseNew_log" TO "/var/opt/mssql/data/SqlCourse.ldf"

and it returns "unexpected argument." Clearly that's not the right call but I'm not sure how else to proceed.

(Running mcr.microsoft.com/mssql/server:2019-CTP3.2-ubuntu)

Upvotes: 0

Views: 76

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46291

Single quotes are used to enclose string literals in T-SQL so the resultant RESTORE T-SQL script needs to be:

RESTORE DATABASE SqlCourse
FROM DISK = '/var/opt/mssql/backup/SqlCourse.bak\'
WITH
      MOVE 'CourseNew' TO '/var/opt/mssql/data/SqlCourse.mdf'
    , MOVE 'CourseNew_log' TO '/var/opt/mssql/data/SqlCourse.ldf';

Since you are passing the command as a bash shell command-line argument, you also need to prefix the argument string with '$' and escape the single quotes within the string by preceding them with a \:

sudo docker exec -it container_name /opt/mssql-tools/bin/sqlcmd \
   -S localhost -U SA -P "Password" \
   -Q $'RESTORE DATABASE SqlCourse FROM DISK = \'/var/opt/mssql/backup/SqlCourse.bak\' WITH MOVE \'CourseNew\' TO \'/var/opt/mssql/data/SqlCourse.mdf\', MOVE \'CourseNew_log\' TO \'/var/opt/mssql/data/SqlCourse.ldf\';'

You can avoid the escaping ugliness by copying the normal RESTORE script into the container and running with the SQLCMD -i argument.

Upvotes: 1

Related Questions