SQL Server - Linux

Tip

We have Microsoft SQL Server running in Kubernetes, so no need to use Docker…

Docker

docker pull microsoft/mssql-server-linux:2017-latest

sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<Cognito123>' -p 1433:1433 --name sql1 -d microsoft/mssql-server-linux:2017-latest

Tip

The password for this database is <Cognito123> not Cognito123.

View logs:

docker logs -f f71a08c580422335b498953bd0e6a9

Run sqlcmd:

docker exec -it sql1 "bash"
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<Cognito123>'

Restore Backup

Tip

We have Microsoft SQL Server running in Kubernetes, so no need to use Docker (unless you want to)…

Create a folder in your Docker container for the backup file:

docker exec -it sql1 "bash"
mkdir /var/opt/mssql/backup

From your command prompt (exit Docker), copy the SQL Server backup file to the Docker folder:

docker cp ~/Downloads/DocRecord.bak sql1:/var/opt/mssql/backup/

List the logical file names and paths inside the backup. This is done with the RESTORE FILELISTONLY Transact-SQL statement:

docker exec -it sql1 "bash"
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "<Cognito123>" -Q "RESTORE FILELISTONLY FROM DISK = '/var/opt/mssql/backup/DocRecord.bak'"

If you need to, drop the existing database:

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "<Cognito123>" -Q "DROP DATABASE DocRecord"

Restore the backup:

docker exec -it sql1 "bash"
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "<Cognito123>" -Q "RESTORE DATABASE DocRecord FROM DISK = '/var/opt/mssql/backup/DocRecord.bak' WITH MOVE 'DocRecord' TO '/var/opt/mssql/data/DocRecord.mdf', MOVE 'DocRecord_log' TO '/var/opt/mssql/data/DocRecord_log.ldf'"

Ubuntu 18.04

Install sqlcmd and the Microsoft ODBC Driver for SQL Server:

sudo -
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

apt-get update
ACCEPT_EULA=Y apt-get install msodbcsql17
ACCEPT_EULA=Y apt-get install mssql-tools

From Install SQL Server and create a database on Ubuntu

Tip

I couldn’t get this working, so switched to using Docker (see above).

Tip

I had to force install on Ubuntu 18.04 using these instructions, How do I install MS SQL for ubuntu 18.04 LTS?:

apt-get download mssql-server
sudo dpkg --force-depends -i mssql-server_14.0.3025.34-3.deb
systemctl restart mssql-server.service
vim /var/lib/dpkg/status
# search for 'Package: mssql-server' and change the depends line from
...hostname, openssl (>= 1.0.1), openssl (<= 1.1.0), python (<= 2.7.0)...
# to
...hostname, openssl (>= 1.0.1), openssl (< 1.1.1), python (<= 2.7.0)...

Ubuntu 16.04

Set-up command line tools:

cd ~/bin/
ln -s /opt/mssql-tools/bin/sqlcmd .

Commands

Status:

systemctl status mssql-server

sqlcmd:

sqlcmd -S localhost -U SA -P 'Your Password'

Restore

Warning

When you do a SQL Server backup, be sure to select Media Options, Overwrite all existing backup sets (or find some other way to restore the latest backup)!

# Copy the backup file to the SQL Server backup folder
sudo mv ~/Downloads/MyDB.bak /var/opt/mssql/backup/

sqlcmd -S localhost -U SA -P 'Your Password'

# Restore 'MyDB' backup
RESTORE DATABASE MyDB
FROM DISK = '/var/opt/mssql/backup/MyDB.bak'
WITH MOVE 'MyDB' TO '/var/opt/mssql/data/MyDB.mdf',
MOVE 'MyDB_Log' TO '/var/opt/mssql/data/MyDB_Log.ldf'
GO

# Verify
SELECT Name FROM sys.Databases
GO

USE MyDB
SELECT * FROM MyTable
GO