Restoring mssql .bak file Linux mssql server

Posted Leave a comment

To restore a mssql database backup to a linux host, using default microsoft install locations:

/opt/mssql-tools/bin/sqlcmd -S hostname -U user -P Password! -Q "RESTORE DATABASE [mydatabase] FROM DISK = N'/tmp/backupStandard.bak' WITH FILE = 1, NOUNLOAD, REPLACE, NORECOVERY, STATS = 5"

If you see an error like this

The backup set holds a backup of a database other than the existing 'mydatabase' database.  
Msg 3013, Level 16, State 1, Server ms-sql, Line 1  
RESTORE DATABASE is terminating abnormally.  

Use something like the following. It’s caused by creating an empty database to restore to, WITH REPLACE just makes tsql replace the table.

/opt/mssql-tools/bin/sqlcmd -S localhost -U user -P Hunter2! -Q "RESTORE DATABASE [database] FROM DISK='/path/to/backup.bak' WITH REPLACE.