The ability to export a script of database objects has for a long time been a feature of SQL Server Management Studio. With the new OSS cross platform tooling from Microsoft we can now have ths feature everywhere. mssql-scripter allows you to export schema and or data from SQL Server from pretty much any terminal

I do a lot of my SQL Server sandbox stuff on macOS using a Docker image of SQL Server 2017 with VS Code and the mssql plugin as my editor. With the new mssql-scripter tool I can now export SQL scripts of my database objects and data directly from the VS Code terminal.

For example here is my VS Code window listing all the databases in my Docker hosted SQL Server 2017 server…

From here I can open the terminal in VS Code and run the mssql-scripter tool to generate a new SQL Script called InitialDb.sql in my working directory…

Once this has run I get my new script file and inside is the SQL Script to create that database and all it’s objects…

This tool supports a great deal of arguments for specifying what you want to script from the database. For example we can Script Schema only, data only, Schema and Data then we can go down to exactly what types of objects to script for example Statistics, logins etc….

To get a full list of what mssql-scripter supports at the console run…

mssql-scripter –help

Here’s a small set of the arguments that –help lists…

Instructions for installing mssql-scripter on each platform can be found on their GitHub installation page

One of the new less publicized features in SQL Server 2017 is STRING_AGG. This new feature can take an expression of string values and concatenate them with a specified separator.

There’s not much more to say about it other than that really, so lets look at an example. Imagine we have a user table and we want to provide our application with a comma separated list of all the usernames in the table…

CREATE TABLE dbo.[User]
(
    Id INT IDENTITY PRIMARY KEY,
    Username NVARCHAR(20)
)

INSERT INTO dbo.[User](Username)
VALUES 
    ('LukeCage'),
    ('JessicaJones'),
    ('BruceWayne')

SELECT STRING_AGG(ISNULL(Username,'Not Specified'),' , ') 
FROM dbo.[User]

This kind of concatenation has always been a bit of a pain before SQL Server 2017, usually achieved using xml methods or variables and coalesce, now it couldnt be any easier.

SQL Server 2017 is the first version of SQL Server that will also run natively on Linux and on macOS via Docker. There has never been a quicker way to get SQL Server installed and running in a development environment than using this new Docker image.

This is a video of the whole process which on a slow connection and an old MacBook air takes 4 minutes and 7 seconds (This is amazing!)

Let’s run through these steps, you’ll need to have Docker installed to follow along.

1. Change the Docker settings to use 4gb of RAM as this is a requirement for SQL Server. To do this right click the Docker icon in the menu bar, click preferences and on the advanced tab change RAM to 4gb. Once set click the apply & restart button then wait for Docker to restart.

   

2. Run the following command to pull down the SQL Server Docker image

   docker pull microsoft/mssql-server-linux

3. Now we just need to run this image. Let’s also mount a volume so any data we create gets persisted between container restarts this is done by including the -v argument in the Docker run command…

   Docker run -e ‘ACCEPT_EULA=Y’ -e ‘SA_PASSWORD=(Str0ngP4ss)’ -p 1433:1433 -v sqlvolume:/var/opt/mssql -d microsoft/mssql-server-linux

Let’s break down what this command is doing…

• -e ‘ACCET-EULA=Y’ : This accepts the EULA and without this SQL Server will not start. This is normally done in the UI For the SQL Server installer but in this case as there is no installer it’s done in the Docker Run command.
• -e ‘SA_PASSWORD=(Str0ngP4ss)’ = : Here we specify the SA account password that we will use to connect to this server.
• -p 1433:1433 : This publishes port 1433 on the container to port 1433 on the host to allow us to connect to SQL Server.
• -v sqlvolume : This tells our container to mount a volume so we can store files in that path and they will be stored on the host to survive restarts of the container (By default containers persist no data and will start in a clean state every time)

At this point we now have a SQL Server instance running in our new container. On macOS the best ways to connect to this are either via VS Code wth the MSSQL extension or SqlCmd for a command line option. Whichever tool you choose you just need to point it to localhost with a username of sa and a password of whatever you specified in your Docker Run command.

For example if you have sqlcmd installed from the link above you can run the following command to query our new SQL Server 2017 instance running inside of docker.

sqlcmd -S localhost -U sa -P “(Str0ngP4ss)” -Q “SELECT TOP 10 Name FROM sys.Objects”