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:
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.
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'