Resolving directory lookup error on SQLServer backup restore

Author: Steven Neiland
Published:

A few weeks ago I decided to try migrate a SQL Server database of a personal project to a vagrant box running the new linux compatible version of SQL Server. I managed creating the vagrant box pretty easily but when I tried creating the database by restoring from a backup using the following command:

RESTORE DATABASE mydatabase
FROM DISK = '/path/to/mydatabase.bak'

I received this error:

Error: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL\DATA\mydatabase.mdf" failed with the operating system error 2(The system cannot find the file specified.).

Looking at the error you will note that the restore command is making the assumption that I am restoring the database to the same windows operating system and file structure.

Solution: Restore the database files to specific locations

In order to fix this issue we need to determine what the logical names of all files being restored in the backup are and then specify the correct locations for those files in the new files system.

First run this command to view the restore information of the files.

RESTORE FILELISTONLY FROM DISK = '/path/to/mydatabase.bak'

We get back a whole lot of data but we are only interested in two specific data points 'LogicalName' and 'PhysicalName'. In this case I received back the following.

LogicalName PhysicalName
mydatabase_Data C:\Program Files\Microsoft SQL Server\MSSQL\DATA\mydatabase.mdf
mydatabase_Log C:\Program Files\Microsoft SQL Server\MSSQL\DATA\mydatabase.ldf

Since the data directory for SQL Server on my vagrant box is located at '/var/opt/mssql/data/' all that is needed is to update the restore command to include the WITH MOVE options to specify where these files now need to be located like this:

RESTORE DATABASE mydatabase
FROM DISK = '/path/to/mydatabase.bak'
WITH MOVE 'mydatabase_Data' TO '/var/opt/mssql/data/mydatabase.mdf',
MOVE 'mydatabase_Log' TO '/var/opt/mssql/data/mydatabase.ldf'

Reader Comments

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain advertisments or appear to be created for the purpose of link building, will not be published.

Archives Blog Listing