Restoring a Windows MS SQL Server Database to Linux With Move SQLCMD

scottalanmiller

If you have a version of MS SQL Server that was running on Windows and is current enough to be able to be restored to a version of MS SQL Server running on Linux (at this time, that SQL Server 2012 and later on Windows going to SQL Server 2019 on Linux) then you will need to use the "WITH MOVE" T-SQL option in order to make the process work. There is little to know syntactical information on this anywhere.

The command used to execute the restore is the SQLCMD command. What happens is that your BAK file will have hard coded paths to files on Windows and will attempt to restore your files to these paths. But this will not work as the files do not exist on Linux. So we have to add the "WITH MOVE" option to the command to change the location of the files.

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -Q "RESTORE DATABASE [yourdatabasename] FROM DISK = N'/var/opt/mssql/yourdatabasefile.bak'  WITH MOVE 'db' TO '/var/opt/mssql/data/db.mdf', MOVE 'db_log' TO '/var/opt/mssql/data/db_log.ldf', FILE = 1, NOUNLOAD, REPLACE, STATS = 5"

So first off yes, the square brackets around the database name are supposed to be there. Keep those in.

Second, I'm assuming here that you kept the default location for your MS SQL Server data files which is /var/opt/mssql. If you are like me, you make a disk just for that and mount it at that location so it is the default path, but a unique block device. If you move the location, change this.

The list of files to move is tricky as you could have one, two, or many more. I think most everyone will have two: a main MDF file and a secondary LDF log file. I run this command and get an error as to the list of files that are not provided for and modify based on that. There is a command to look up that list first but, why bother when this one does it for you anyway.

JaredBusch

@scottalanmiller I have not had to do that before with a normal backup to a .bak and then restore. Not some an place move like it seems you are doing.

scottalanmiller

@jaredbusch said in Restoring a Windows MS SQL Server Database to Linux With Move SQLCMD:

@scottalanmiller I have not had to do that before with a normal backup to a .bak and then restore. Not some an place move like it seems you are doing.

Happens if going to a space with a different storage layout. If you are coming from Linux you are probably fine. But Windows injects the drive letter into the path (obviously) and so going from one machine to another that doesn't keep identical storage path names causes the issue.