5

I need to work with a SQL server instance, however my workspace runs Linux and the database backup that was given to me was running on Windows.

I succesfully managed to install SQL Server on my Linux environment, and I'm using DBeaver to access it, the main problem I have is when I try to restore this .bkp file in my SQL Server instance.

I'm trying to restore the database with this SQL command I read in an answer in this site, however I need the Logical Data File Name and the Logical Data Log. So I ran the next SQL command.

RESTORE DATABASE FILELISTONLY
FROM DISK = '/var/tmp/(redacted).bkp';

I get SQL Error 5133

SQL Error [5133][S0001]: Directory lookup for the file "D:\SQL2012\DATA\(redacted).mdf" failed with the operating system error 2(The system cannot find the file specified.).

Is it possible to restore this file? If so what am I doing wrong?

Thanks in advance.

  • Can you just get it from the windows sql server using sp_helpdb dbname -- change it to your dbname ? – Kin Shah May 25 '18 at 15:15
  • @Kin Unfortunately I do not have access to the Windows database. – Gabriel I. May 25 '18 at 15:18
  • What is the version of SQL that you got the backup from and what is the version you are restoring to? – Shaulinator May 25 '18 at 17:03
3

Depending on what build you are using .. if you are getting an error then it might be a bug that you need to report to Microsoft.

You can still give the undocumented - sp_restore_filelistonly a try but it is just a wrapper as Brent says in his answer :

EXEC sp_restore_filelistonly
    @device_type = 'DISK',
    @backup_path = '/var/tmp/(redacted).bkp';

If above does not work, then for sure open a bug report with Microsoft.

It works for me - I have tested (Microsoft SQL Server 2017 (RTM-CU4) (KB4056498) - 14.0.3022.28 (X64) Feb 9 2018 19:39:09 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 16.04.3 LTS)) and for me it works using both (restore filelistonly and using undocumented sp)

  • restore filelistonly from disk = '/var/opt/mssql/backup/dbname_(FULL)_20170108_020026.bak'

enter image description here

  • Hello, the command worked. It's output was D:\SQL2012\DATA\foo.mdf and E:\SQL2012\LOG\foo.ldf. Which then I used them in the Database Restore command: RESTORE DATABASE foo FROM DISK = '/var/tmp/foo.bkp' WITH MOVE 'D:\SQL2012\DATA\foo.mdf' TO '/var/opt/mssql/data/foo.mdf', MOVE 'E:\SQL2012\LOG\foo.ldf' TO '/var/opt/mssql/data/foo_log.ldf'; But I get another error: SQL Error [3234] [S0002]: Logical file 'D:\SQL2012\DATA\foo.mdf' is not part of database 'foo'. Use RESTORE FILELISTONLY to list the logical file names.. foo is where the filename is. What am I doing wrong? – Gabriel I. May 28 '18 at 15:03
  • 1
    This is because the backup is mixed of different dbs ... might be appended. You should take the backup with init and seperate backups of different dbs to their own files to avoid situations like the one u are running into. – Kin Shah May 28 '18 at 15:11
  • Unfortunately I do not have access to the original DB (this backup was given to me because I needed to port the data to a MySQL database). Thanks for your time. I'll keep asking for a Sql dump. – Gabriel I. May 28 '18 at 15:17
1
  1. Navigate to your Linux box, either with Remote Desktop Connection, a putty session, or some other method of your choice.

  1. Run SQLCMD to connect to the SQL Service in a bash window.

sqlcmd -S localhost -U SA

You can replace the -S and -U parameters if you need.

The following example connects to the local SQL Server instance with the SA user. Enter the password when prompted, or specify the password by adding the -P parameter.


  1. Once you are in SQLCMD, at the >1 prompt, try this:

RESTORE DATABASE FILELISTONLY
FROM DISK = '/var/tmp/(redacted).bkp';

This should give you a list of the files for you to restore. Now you can try this command in SQLCMD still, substituting values as applicable.

RESTORE DATABASE YourDB
FROM DISK = '/var/tmp/(redacted).bkp'
WITH MOVE '(redacted).mdf' TO '/var/opt/mssql/data/YourDB.mdf',
MOVE '(redacted).ldf' TO '/var/opt/mssql/data/YourDB_Log.ldf'
GO

This can also be done through the GUI.

MSDN Source: Restore your database on Linux

MS SQL Tips from Graham Okely that has great additional information on Linux Backups and SQLCMD.

SO answer related to this one.

  • Seems like OP has tried using filelistonly and thats where the error is coming. What different are you suggesting ? – Kin Shah May 25 '18 at 15:23
  • @Kin I will need a response back from OP if this works or not. There are inconsistencies with their post. The answer they linked is not a filelistonly command but a full restore. The primary difference however, is I cannot find any article stating you can use T-SQL in a SSMS window (or DBeaver) to restore a Linux backup. Every article I see uses SQLCMD, OP does not specify how they are running the command but it appears they are not using SQLCMD to do so. – Shaulinator May 25 '18 at 15:28
  • @Shaulinator I'm using a DBM to input commands to the server instance (DBeaver) since I use a Linux distro in my work. – Gabriel I. May 25 '18 at 15:29
  • @GabrielI. Can you putty / ssh into the Linux server and run the commands locally? – Shaulinator May 25 '18 at 15:30
  • 1
    @GabrielI. you should file a bug report with MS since restore filelistonly should not throw the error. – Kin Shah May 25 '18 at 16:59
0

Your syntax is wrong. From the documentation:

RESTORE FILELISTONLY   
FROM <backup_device>   
[ WITH   
 {  
--Backup Set Options  
   FILE = { backup_set_file_number | @backup_set_file_number }   
 | PASSWORD = { password | @password_variable }   

Note that you're using RESTORE DATABASE FILELISTONLY which is invalid syntax. Remove the word DATABASE and try again.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.