Restore a SQL Server database to Linux

For this posting, we have an on-premises virtual machine with SQL Server vNext on Ubuntu. SQL Server vNext is up and running, and we're using the SQL Server Management Studio for vNext to both backup our source database and then restore the database to Ubuntu.  Since all of the tasks were performed via T-SQL scripts, they could just as easily been run from SQurrill SQL, sqlcmd, or any other tool.
(  see Use SSMS to connect to Linux SQL Server v.Next
   and Use SQuirrel SQL to Connect to Linux SQL Server v.Next  )

Overview

Overall, the process was straight forward, especially since we were using SSMS. File and folder permissions was the one area that caused us the most problems.

First, back up the database

We used an older version of AdventureWorks for this task.  We explicitly used the NO_COMPRESSION option for this demo - to keep it simple.  

       
    BACKUP DATABASE [AdventureWorks2012] 
       TO  DISK = N'H:\Data\AdventureWorks2012.bak' 
      WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks2012-Full Database Backup'
        , SKIP, NOREWIND, NOUNLOAD, NO_COMPRESSION,  STATS = 10, CHECKSUM
    GO   
 

Next, change Permissions to Read-only

Copy/move the backup file to your Ubuntu virtual machine. To keep permissions simple, just copy the file to the Downloads folder on Ubuntu. Once the file has been moved, adjust the file permissions so that the file can be read.  For demo purposes, the quickest approach is to change the "Others" section and change Access to "Read-only".

File and folder permissions can fail the restore, so if you use another folder, just make sure all folder and file permissions allow access.

Test the file HEADER and FILELIST

To verify we could read the backup file, we ran both RESTORE HEADER and RESTORE FILELISTONLY.

       
      USE [master]
      RESTORE HEADERONLY
        FROM  DISK =  N'C:\home\user\Downloads\AdventureWorks2012.bak' 
 
      USE [master]
      RESTORE FILELISTONLY
       FROM  DISK = N'C:\home\user\Downloads\AdventureWorks2012.bak'

       
 

On one of our earlier failed restore attempts, it was clear there was a problem with the backup file. From further examination of the backup, it was clear the backup file had issues:

If you are not sure of the actual Ubuntu file location, use properties:

Swap backslash/forward slash

Notice the different slash orientation: 

Ubuntu:                 /home/user/Downloads/AdventureWorks2012.bak

SSMS:               C:\home\user\Downloads\AdventureWorks2012.bak

Restore Database to Linux

To control the process, we used the MOVE option, and reviewed all target folder permissions to ensure that our restore would be successful.

       
 USE [master]
 RESTORE DATABASE AdventureWorks2012 
  FROM  DISK = N'C:\home\user\Downloads\AdventureWorks2012.bak'
 
  WITH  FILE = 1
, MOVE N'AdventureWorks2012_Data' TO N'C:\media\user\Data\SQLServer\AdventureWorks2012_Data.mdf'   
, MOVE N'AdventureWorks2012_Log'  TO N'C:\media\user\Data\SQLServer\AdventureWorks2012_Log.ldf'   
, NOUNLOAD,  STATS = 5

GO