CODEWITHSUNDEEP
sql-serverdocker
joe
joe

Reputation: 9513

ms sql server linux docker asking for file in D:\

I am now trying to restore .bak file of MS SQL Server 2008 to docker mssql-serverlinux in order to see the content inside and draw my own scheman in Postgres

I read this and conclude that my command to restore it to my container supposed to be in single line

sqlcmd -S localhost -U SA -Q "RESTORE DATABASE mytrash FROM DISK = '/trash/dbWINS_MPMBA201803141100.bak' WITH FILE = 1, NOUNLOAD, REPLACE, NORECOVERY, STATS = 5"

Here is the full error and directory I place the bak file. Since it will be no use when I finish I name the directory trash in order to delete it later.

root@891ce27ef07a:/trash# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -Q "RESTORE DATABASE mytrash FROM DISK = '/trash/dbWINS_MPMBA201803141100.bak' WITH FILE = 1, NOUNLOAD, REPLACE, NORECOVERY, STATS = 5"
Password:
Msg 5133, Level 16, State 1, Server 891ce27ef07a, Line 1
Directory lookup for the file "D:\MSSQL\Data\dbWINS_MPMBA.mdf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Server 891ce27ef07a, Line 1
File 'dbERP_New_Data' cannot be restored to 'D:\MSSQL\Data\dbWINS_MPMBA.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Server 891ce27ef07a, Line 1
Directory lookup for the file "D:\MSSQL\Data\dbWINS_MPMBA_1.ldf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Server 891ce27ef07a, Line 1
File 'dbERP_New_Log' cannot be restored to 'D:\MSSQL\Data\dbWINS_MPMBA_1.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Server 891ce27ef07a, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Server 891ce27ef07a, Line 1
RESTORE DATABASE is terminating abnormally.
root@891ce27ef07a:/trash# pwd
/trash
root@891ce27ef07a:/trash# ls
dbWINS_MPMBA201803141100.bak
root@891ce27ef07a:/trash#

My docker-compose. For any one who would like to show me the answer.

version: "3.5"

services:
  mssql:
    image: "microsoft/mssql-server-linux"
    environment:
      SA_PASSWORD: "hardlongpassword"
      ACCEPT_EULA: "Y"
    volumes:
      - type: bind
        source: ./data
        target: /trash

Update I tried to move the location according to the TZHX comment from this I got new error now

1>
1> RESTORE DATABASE NewDB
2> FROM DISK = '/trash/dbWINS_MPMBA201803141100.bak'
3> WITH MOVE 'NewDB' TO '/trash/NewDB.mdf',
4> MOVE 'NewDB' TO '/trash/NewDB_Log.ldf'
5> GO
Msg 3234, Level 16, State 2, Server 891ce27ef07a, Line 1
Logical file 'NewDB' is not part of database 'NewDB'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Server 891ce27ef07a, Line 1
RESTORE DATABASE is terminating abnormally.

Update2: From this. I am reading the output

1> RESTORE FILELISTONLY FROM DISK ='/trash/dbWINS_MPMBA201803141100.bak' WITH FILE=1
2> GO
LogicalName                                                                                                                      PhysicalName                                                                                                                                                                                                                                                         Type FileGroupName                                                                                                                    Size                 MaxSize              FileId               CreateLSN                   DropLSN                     UniqueId                             ReadOnlyLSN                 ReadWriteLSN                BackupSizeInBytes    SourceBlockSize FileGroupId LogGroupGUID                         DifferentialBaseLSN         DifferentialBaseGUID                 IsReadOnly IsPresent TDEThumbprint                              SnapshotUrl
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- --------------------------- --------------------------- ------------------------------------ --------------------------- --------------------------- -------------------- --------------- ----------- ------------------------------------ --------------------------- ------------------------------------ ---------- --------- ------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
dbERP_New_Data                                                                                                                   D:\MSSQL\Data\dbWINS_MPMBA.mdf                                                                                                                                                                                                                                       D    PRIMARY                                                                                                                                     256311296       35184372080640                    1                           0                           0 00000000-0000-0000-0000-000000000000                           0                           0            143196160             512           1 NULL                                        73150000001788800289 B582C71F-CCDF-4030-9CF8-30785258D515          0         1 NULL                                       NULL
dbERP_New_Log                                                                                                                    D:\MSSQL\Data\dbWINS_MPMBA_1.ldf                                                                                                                                                                                                                                     L    NULL                                                                                                                                       2945384448       35184372080640                    2                           0                           0 00000000-0000-0000-0000-000000000000                           0                           0                    0             512           0 NULL                                                           0 00000000-0000-0000-0000-000000000000          0         1 NULL                                       NULL

(2 rows affected)

update3:

1> RESTORE DATABASE dbERP_New_Data
2> FROM DISK = '/trash/dbWINS_MPMBA201803141100.bak'
3> WITH MOVE 'dbERP_New_Data' TO '/trash/dbERP_New_Data.mdf',
4> MOVE 'dbERP_New_Log' TO '/trash/dbERP_New_Log.ldf'
5> GO
Msg 5120, Level 16, State 101, Server 891ce27ef07a, Line 1
Unable to open the physical file "/trash/dbERP_New_Data.mdf". Operating system error 87: "87(The parameter is incorrect.)".
Msg 3013, Level 16, State 1, Server 891ce27ef07a, Line 1
RESTORE DATABASE is terminating abnormally.

Update4: It does add new mdf file

Upvotes: 0

Views: 572

Answers (1)

Jeroen Mostert
Jeroen Mostert

Reputation: 28809

Apparently, restoring database files to a volume on the host doesn't work (at least not with the default options on a MacOS host; there may be advanced Docker options to get it to work). What will work is restoring the files within the host itself:

RESTORE DATABASE dbERP_New_Data
FROM DISK = '/trash/dbWINS_MPMBA201803141100.bak'
WITH MOVE 'dbERP_New_Data' TO '/root/dbERP_New_Data.mdf',
MOVE 'dbERP_New_Log' TO '/root/dbERP_New_Log.ldf'

Probably not something you should do for a production setup, but good enough if you only need to access the database for a short while.

Upvotes: 1

Related Questions