A Minimalist Guide to Microsoft SQL Server 2017 on Ubuntu Linux

$ mssql-cli CREATE DATABASE restored; ALTER DATABASE restored SET SINGLE_USER WITH ROLLBACK IMMEDIATE; RESTORE DATABASE restored FROM DISK = '/home/mark/db.bak' WITH REPLACE, RECOVERY, MOVE 'WorldCup2014' TO '/home/mark/restored.mdf', MOVE 'WorldCup2014_log' TO '/home/mark/restored.ldf'; With the database recovered I can switch to the restored database and select out the three countries the model predicted to top the 2014 World Cup as well as their probabilities at winning..USE restored; SELECT TOP 3 country, win FROM Predictions ORDER BY win DESC; +———–+——————-+ | country | win | |———–+——————-| | Brazil | 0.453437174610376 | | Argentina | 0.127798660739543 | | Germany | 0.106981061567489 | +———–+——————-+ Export to CSV If you want to dump the output of a SELECT statement to CSV files and the output is made up of nothing more than integers, fractional numbers and strings that dont require escaping then the sqlcmd can be of good use..The following will set the environment variable for the database password for the sqlcmd..$ export SQLCMDPASSWORD=$MSSQL_CLI_PASSWORD The following will dump the predictions to a CSV file..$ sqlcmd -S localhost -U SA -d WorldCup2014 -s',' -W -h-1 -Q "SET NOCOUNT ON; SELECT * FROM Predictions" > out.csv Export to SQL In early 2017, work began on mssql-scripter, a Python-based CLI tool that could export SQL Server databases to plain-text SQL files..The following will install the tool and set the environment variable for the database password so you dont need to enter it each time you use the tool..$ pip install mssql-scripter $ export MSSQL_SCRIPTER_PASSWORD=$MSSQL_CLI_PASSWORD The following will dump out the World Cup 2014 database..$ mssql-scripter –server localhost –database WorldCup2014 –user SA –schema-and-data > dump.sql A lot of effort has gone in to ensure as much state of the database as possible is preserved in the .sql dump file..This is the first 30 lines of the above commands output..$ head -n30 dump.sql USE [master] GO /****** Object: Database [WorldCup2014] Script Date: 8/12/18 7:37:35 AM ******/ CREATE DATABASE [WorldCup2014] CONTAINMENT = NONE ON PRIMARY ( NAME = N'WorldCup2014', FILENAME = N'/var/opt/mssql/data/WorldCup2014.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ) LOG ON ( NAME = N'WorldCup2014_log', FILENAME = N'/var/opt/mssql/data/WorldCup2014_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB ) GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [WorldCup2014].[dbo].[sp_fulltext_database] @action = 'enable' end GO ALTER DATABASE [WorldCup2014] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [WorldCup2014] SET ANSI_NULLS OFF GO ALTER DATABASE [WorldCup2014] SET ANSI_PADDING OFF GO ALTER DATABASE [WorldCup2014] SET ANSI_WARNINGS OFF GO ALTER DATABASE [WorldCup2014] SET ARITHABORT OFF GO ALTER DATABASE [WorldCup2014] SET AUTO_CLOSE OFF GO ALTER DATABASE [WorldCup2014] SET AUTO_SHRINK OFF GO ALTER DATABASE [WorldCup2014] SET AUTO_UPDATE_STATISTICS ON Plotting Data with Jupyter Notebook You can source SQL Server-stored data for analysis in Jupyter Notebook via the pymssql library..The following will install Pandas, Jupyter Notebook, Matplotlib as well as pymssql..$ pip install jupyter matplotlib pandas pymssql Ill set a password for the Notebook server and then launch it.. More details

Leave a Reply