I am not able to restore a database backup (db.bak).
Given the following Transact-SQL:
1> RESTORE DATABASE db
2> FROM DISK = '/var/opt/mssql/db.bak' ;
3> GO
Results in:
Msg 5133, Level 16, State 1, Server mbü-lubuntu, Line 1
Directory lookup for the file "D:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA\db.mdf" failed with the operating
system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Server mbü-lubuntu, Line 1
File 'db' cannot be restored to 'D:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA\db.mdf'. Use WITH MOVE to
identify a valid location for the file.
Msg 5133, Level 16, State 1, Server mbü-lubuntu, Line 1
Directory lookup for the file "D:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA\db_log.ldf" failed with the
operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Server mbü-lubuntu, Line 1
File 'db_log' cannot be restored to 'D:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA\db_log.ldf'. Use WITH MOVE to
identify a valid location for the file.
Msg 3119, Level 16, State 1, Server mbü-lubuntu, Line 1
Problems were identified while planning for the RESTORE statement. Previous
messages provide details.
Msg 3013, Level 16, State 1, Server mbü-lubuntu, Line 1
RESTORE DATABASE is terminating abnormally.
I am even wondering, why the agent is looking for a Windows Path (D:\Program Files...) in a Linux program?
/var/opt/mssql/mssql.conf:
[EULA]
accepteula = Y
[sqlagent]
enabled = true
[filelocation]
defaultbackupdir = /var/opt/mssql/data/
defaultdatadir = /var/opt/mssql/data/
defaultdumpdir = /var/opt/mssql/data/
defaultlogdir = /var/opt/mssql/data/
Thanks!
Probably because the database was created and backed up on Windows. The database remembers where it came from, and tries to restore to the same place.
You probably need to use the WITH MOVE clause of the restore statement, as the error message points out.