CODEWITHSUNDEEP
sql-serverlinuxdockerdatabase-restore
Kelson Ball
Kelson Ball

Reputation: 983

Trouble restoring sql server DB on docker container

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.).

Upvotes: 1

Views: 3471

Answers (3)

A. Lion
A. Lion

Reputation: 680

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.

Upvotes: 0

Bacon Bits
Bacon Bits

Reputation: 32145

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.

Upvotes: 2

ccarpenter32
ccarpenter32

Reputation: 1077

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.

Upvotes: 0

Related Questions