Reputation: 9513
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.
Upvotes: 0
Views: 572
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