HowTo Install MSSQL 2017 (14.0.3008.27-1) on NethServer 7.4
Info:
- https://www.microsoft.com/en-us/sql-server/sql-server-downloads
- https://docs.microsoft.com/de-de/sql/linux/quickstart-install-connect-red-hat
Download the Microsoft SQL Server Red Hat repository configuration file (all in one line):
curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo
Run the following command to install SQL Server:
yum install -y mssql-server
After the package installation finishes, run mssql-conf setup and follow the prompts. Make sure to specify a strong password for the SA account (Minimum length 8 characters, including uppercase and lowercase letters, base 10 digits and/or non-alphanumeric symbols):
/opt/mssql/bin/mssql-conf setup
Once the configuration is done, verify that the service is running:
systemctl status mssql-server
To allow remote connections (LAN), open the SQL Server port on the firewall. The default SQL Server port is TCP 1433:
config set myservice service status enabled TCPPort 1433 access green
signal-event firewall-adjust
To upgrade the mssql-server package, if needed, execute the following command:
yum update mssql-server
To remove the mssql-server package, if needed, follow these steps:
yum remove mssql-server
Removing the package will not delete the generated database files. If you want to delete the database files use the following command:
rm -rf /var/opt/mssql/
To be able to connect to SQL Server on Linux from command line
Download the Microsoft Red Hat repository configuration file:
curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo
If you had a previous version of mssql-tools installed, remove any older unixODBC packages:
yum remove unixODBC-utf16 unixODBC-utf16-devel
Run the following commands to install mssql-tools with the unixODBC developer package.
yum install mssql-tools unixODBC-devel -y
To update to the latest version of mssql-tools run the following commands:
yum check-update
yum update mssql-tools
Add /opt/mssql-tools/bin/ to your PATH environment variable:
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
Connect to SQL Server with sqlcmd
On your Linux box, open a command terminal. Run sqlcmd with parameters for your SQL Server name (-S), the user name (-U), and the password (-P).
The following command connects to the local SQL Server instance (localhost) on Linux:
sqlcmd -S localhost -U SA -P '<My-PassWord-From-Setup>'
To connect to a remote instance, specify the machine name or IP address for the -S parameter:
sqlcmd -S 192.168.xxx.xxx -U SA -P '<My-PassWord-From-Setup>'
List your Databases with:
1> SELECT Name from sys.Databases
2> GO
Name
master
tempdb
model
msdb
(4 rows affected)
Backup and restore SQL Server databases
In the following example sqlcmd connects to the local SQL Server instance and takes a full backup of a user database called demodb.
Backup a database (all in one line):
sqlcmd -S localhost -U SA -Q "BACKUP DATABASE [master] TO DISK = N'/var/opt/mssql/data/master.bak' WITH NOFORMAT, NOINIT, NAME = 'master-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
Backup the transaction log (all in one line):
sqlcmd -S localhost -U SA -Q "BACKUP LOG [demodb] TO DISK = N'/var/opt/mssql/data/demodb_LogBackup.bak' WITH NOFORMAT, NOINIT, NAME = N'demodb_LogBackup', NOSKIP, NOREWIND, NOUNLOAD, STATS = 5"
Restore a database (all in one line):
sqlcmd -S localhost -U SA -Q "RESTORE DATABASE [demodb] FROM DISK = N'/var/opt/mssql/data/demodb.bak' WITH FILE = 1, NOUNLOAD, REPLACE, NORECOVERY, STATS = 5"
Restore the transaction log (all in one line):
sqlcmd -S localhost -U SA -Q "RESTORE LOG demodb FROM DISK = N'/var/opt/mssql/data/demodb_LogBackup.bak'"
Backup with SQL Server Management Studio (SSMS)
The following steps walk through taking a backup with SSMS:
Start SSMS and connect to your server in SQL Server 2017 on Linux.
In Object Explorer, right-click on your database, Click Tasks, and then click Back Up...
In the Backup Up Database dialog, verify the parameters and options, and click OK.
SQL Server completes the database backup.
Restore with SQL Server Management Studio (SSMS)
The following steps walk you through restoring a database with SSMS:
In SSMS right-click Databases and click Restore Databases....
Under Source click Device: and then click the ellipses (...).
Locate your database backup file and click OK.
Under Restore plan, verify the backup file and settings. Click OK.
SQL Server restores the database.