1

I'm trying to create a docker container to use from docker for windows as a test instance for a sql server database. I have a backup file MyDb.bak that I would like to restore as part of the container creation

Currently my dockerfile looks like this 

FROM microsoft/mssql-server-linux:latest
ENV ACCEPT_EULA="Y"
ENV SA_PASSWORD="<my_sa_password>"
COPY ./MyDb.bak /var/opt/mssql/data/MyDb.bak
ENTRYPOINT /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P <my_sa_password> -Q "RESTORE DATABASE [MyDb] FROM DISK = N'/var/opt/mssql/data/MyDb.bak'"

docker problem: The entry point command runs before sql server is spun up and I can't find how to delay it until a connection is possible

sql problem: I can't restore the database when I run the restore script manually because it can't find whatever an mdf file is

Msg 5133, Level 16, State 1, Server c15ec76da554, Line 1
Directory lookup for the file "C:\SQLData\MyDb.mdf" failed with the operating system error 2(The system cannot find the file specified.).
Kelson Ball
  • 948
  • 1
  • 13
  • 30
  • When doing the restore, does the folder: "C:\SQLData\" exist? I believe that the folder has to exist first. – Leptonator Jun 18 '18 at 17:23
  • It's hard to get that to exist in a linux container. I know when I restore manually with SSMS I can tell it to move things to a new folder but every variation I can find of the t-sql restore command was still giving me that same error – Kelson Ball Jun 18 '18 at 17:28
  • Hmm.. https://stackoverflow.com/a/50342967/175063 – Leptonator Jun 18 '18 at 17:34
  • That could be very useful for waiting until the container is ready for sqlcmd to connect, but I don't think that will help tell the restore command to not use C:\SqlData. I think I need to close this question and ask a more specific one – Kelson Ball Jun 18 '18 at 17:39

3 Answers3

2

I can't help you with your docker problem, but this SQL command is incomplete:

RESTORE DATABASE [MyDb] FROM DISK = N'/var/opt/mssql/data/MyDb.bak'

You need to specify where the database should go once it's been restored and where the database log file should go. If you don't provide that, then the database will try to restore the database file and log to the same location it was at on the original server.

Your command should look like this:

RESTORE DATABASE YourDB
FROM DISK = '/var/opt/mssql/backup/YourDB.bak'
WITH MOVE 'YourDB' TO '/var/opt/mssql/data/YourDB.mdf',
MOVE 'YourDB_Log' TO '/var/opt/mssql/data/YourDB_Log.ldf'

See also Migrate a SQL Server database from Windows to Linux using backup and restore.

If you want to see the logical names and paths stored in the backup file, you can run:

RESTORE FILELISTONLY FROM DISK = N'/var/opt/mssql/data/MyDb.bak'

That will return a result set representing the contents and organization of the database that was backed up. See RESTORE FILELISTONLY for more.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • The output of `restore filelistonly` was extremely difficult to read because of formatting issues - but I eventually found that I needed 'MyDb_dat' for the WITH MOVE line and was able to restore – Kelson Ball Jun 18 '18 at 18:23
  • @KelsonBall Yeah, I would highly recommend running RESTORE FILELISTONLY from something more usable than sqlcmd. Sqlcmd functions, but it's a very old tool at this point. SQL Server Management Studio is the preferred method, but `-Q "RESTORE FILELISTONLY FROM DISK = N'/var/opt/mssql/data/MyDb.bak'" -w 2048 -o myfile.txt` and viewing in a text editor, or `-Q "SET NOCOUNT ON; RESTORE FILELISTONLY FROM DISK = N'/var/opt/mssql/data/MyDb.bak'" -w 2048 -W -s"," -o myfile.csv` and viewing in a spreadsheet program are much easier. – Bacon Bits Jun 18 '18 at 18:53
0

In your Database Settings within sql-server there should be three fields listed as Data, Log, and Backup make sure the paths are correct and valid.

ccarpenter32
  • 1,058
  • 2
  • 9
  • 17
  • What is `sql-server`, is that a command line tool like sqlcmd, or do you mean Sql Server Management Studio? I need to do the restore from bash, like I am in the question – Kelson Ball Jun 18 '18 at 17:33
  • Bash move of the backup file: `mv /home/user1/MyDB.bak /var/opt/mssql/data` (this should be the natural placement). Also, I suppose if you don't have the directory `mkdir -p /var/opt/mssql/data` (you'd need to do that first) – ccarpenter32 Jun 18 '18 at 17:43
0

Linux version does not work with Windows style filepath like C:\SQLData\MyDb.mdf but requires something like /SQLData/MyDb.mdf so you need to use the WITH MOVE option to perform the restore as someone already wrote in the comments. In addition since the file you are trying to restore is coming from a Windows MS-SQL Server system doublecheck if the running version on the windows server is 2014 or higher. Since Linux engine has been developed recently it have issues while loading data from legacy windows MS-SQL version. Once I had to first upload the .bak on a Windows Ms-SQL Server 2014 by using the Developer Edition and than I was able to make a backup that could be loaded onto a Linux box.

A. Lion
  • 673
  • 5
  • 12