2

I am trying to backup a database from SqlServer 2014 (in Windows 10 OS) and restore it to SqlServer 2017 (in Ubunutu 18.04 Digital Ocean).

I'm used the following SQL Command to Backup:

USE [master]
GO
BACKUP DATABASE [SampleDb] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup\SampleDb.bak' WITH CHECKSUM, COPY_ONLY, FORMAT, INIT, STATS = 10;

I'm trying to use the following SQL Command to Restore:

RESTORE DATABASE [SampleDb] FROM DISK = '/home/faizan/SampleDb.bak' WITH CHECKSUM, MOVE 'SampleDb_Data' TO '/home/faizan/SampleDb_Data.mdf', MOVE 'SampleDb_Log' TO '/home/faizan/SampleDb_log.ldf', RECOVERY, REPLACE, STATS = 10;

However, I'm getting the following error in my Ubuntu server when I try to restore the database: Error message

When I ran the following Command to check the status of the .mdf and .ldf files,

RESTORE FILELISTONLY FROM DISK = '/home/faizan/SampleDb.bak'

I get the following result: enter image description here

I am an ubuntu newbie and I don't see an mdf file here at all. And when I try to restore without the mdf file I get an error.

So how do I restore this Mssql backup in ubuntu when the .mdf file is missing?

(Extra note: The SampleDb.mdf file is present in Windows 10, in C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA, it just doesnt get transfered to Ubuntu)

Ahmed Faizan
  • 446
  • 5
  • 12
  • 22
  • 2
    In your posting, you repeated the same code for `BACKUP` for `RESTORE`. Please post your actual restore code. – Dai Sep 11 '19 at 08:17
  • Anyway - the problem is likely because your `~` directory (`/home/faizan/`) is not accessible by SQL Server (SQL Server tends to run under its own user/login account with minimal privileges). Try moving the files to an accessible location like `/var/tmp`. – Dai Sep 11 '19 at 08:19
  • 1
    `bak ' WITH CHECKSUM, MOVE ...`? That isn't a `RESTORE` command. – Thom A Sep 11 '19 at 08:21
  • 1
    I recommend *against* putting the database files in the home directory of a **user**. Put them in the `/var/opt/mssql/data` directory; which is the default location, or a folder than **isn't** a user folder that the `mssql` user/group has access to – Thom A Sep 11 '19 at 08:56

1 Answers1

2

This issue is not really Ubuntu-specific, it would appear also on Windows boxes.

There is one small thing to fix in your RESTORE script. The logical name of MDF file is according to a screenshot is SampleDb but not a SampleDb_Data.

Therefore adjusted command is:

RESTORE DATABASE [SampleDb] FROM DISK = '/home/faizan/SampleDb.bak' 
WITH CHECKSUM
, MOVE 'SampleDb' TO '/var/opt/mssql/data/SampleDb_Data.mdf'
, MOVE 'SampleDb_Log' TO '/var/opt/mssql/data/SampleDb_log.ldf'
, RECOVERY, REPLACE, STATS = 10;

Update: As Larnu remarked, another thing to consider is the location of the database files. MSSQL user should have read/write access and /var/opt/mssql/data is a default location for such files.

Alexander Volok
  • 5,630
  • 3
  • 17
  • 33
  • I am getting an error when I try that: Msg 3634, Level 16, State 1, Server DropletOfFaizan, Line 1 The operating system returned the error '2(The system cannot find the file specified.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on '/home/faizan/SampleDb_Data.mdf'. Msg 3156, Level 16, State 5, Server DropletOfFaizan, Line 1 File 'SampleDb' cannot be restored to '/home/faizan/SampleDb_Data.mdf'. Use WITH MOVE to identify a valid location for the file. Msg 3634, Level 16, State 1, Server DropletOfFaizan, Line 1 – Ahmed Faizan Sep 11 '19 at 08:33
  • The operating system returned the error '2(The system cannot find the file specified.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on '/home/faizan/SampleDb_log.ldf'. Msg 3156, Level 16, State 5, Server DropletOfFaizan, Line 1 File 'SampleDb_log' cannot be restored to '/home/faizan/SampleDb_log.ldf'. Use WITH MOVE to identify a valid location for the file. Msg 3119, Level 16, State 1, Server DropletOfFaizan, Line 1 Problems were identified while planning for the RESTORE statement. Previous messages provide details. – Ahmed Faizan Sep 11 '19 at 08:33
  • Msg 3013, Level 16, State 1, Server DropletOfFaizan, Line 1 RESTORE DATABASE is terminating abnormally. – Ahmed Faizan Sep 11 '19 at 08:33
  • 2
    Because you're trying to put the database files in your home directory, @AhmedFaizan. That's the wrong place to put a database file, and I very much doudt the `mssql` user/group has access to your home directory. – Thom A Sep 11 '19 at 08:57
  • 1
    @AhmedFaizan, my script fixed the wrong logical name issue. However, there is another thing to fix. And @Larnu is right, the service account of mssql just has no access to your home folder, consider to follow his recommendation and restore files to `/var/opt/mssql/data` – Alexander Volok Sep 11 '19 at 09:05
  • I have put the backup in /var/opt/mssql/data, changed the urls in the SQl Command and now I get this error. Logical file 'SampleDb_Data' is not part of database 'SampleDb'. Use RESTORE FILELISTONLY to list the logical file names When I check the FILELIST ONLY, there is no MDF file at all. How to fix this? – Ahmed Faizan Sep 12 '19 at 06:02
  • This issue was already fixed in my initial answer. The MDF file has to be placed using `MOVE 'SampleDb' TO= 'your_path\SampleDb_Data.mdf'` – Alexander Volok Sep 12 '19 at 06:23
  • 1
    Thanks to @AlexanderVolok, this is the command I used RESTORE DATABASE [SampleDb] FROM DISK = '/var/opt/mssql/data/SampleDb.bak' WITH CHECKSUM, MOVE 'SampleDb' TO '/var/opt/mssql/data/SampleDb.mdf', MOVE 'SampleDb_Log' TO '/var/opt/mssql/data/SampleDb_log.ldf', RECOVERY, REPLACE, STATS = 10; (moved the backup file to mssql data, and gave file permissions in ubuntu using command found here :https://smallbusiness.chron.com/give-permissions-ubuntu-33174.html) – Ahmed Faizan Sep 12 '19 at 06:32
  • Good to heard that it was resolved, half of the gratitude to be shared with @Larnu, because he pointed on permissions of the folders – Alexander Volok Sep 12 '19 at 06:37