Scriptable command line access to SQL-Server from linux [closed]

Question

Does anyone know of a ready made¹ command line tool that would allow me to connect from a linux client to a sql-server?

I want to be able to run arbitrary queries, but most of the time I want to be able to take a database dump and then restore it.

This has to be scriptable as it will integrate in an automated build environment.

^{1_{FYI: My only other alternative is to write something. I'll probably use perl and DBI}}

Answer 1

I found tsql and while its main purpose may lie elsewhere it covers my needs.

It is included in the EPEL package. I installed it with this:
rpm -Uvh http://ftp.df.lth.se/pub/fedora-epel/6/x86_64/epel-release-6-7.noarch.rpm

FreeTDS(May 14, 2011)                                    FreeTDS(May 14, 2011)

NAME
       tsql - utility to test FreeTDS connections and queries

SYNOPSIS
       tsql   { -S servername [-I interface] | -H hostname -p port }
              -U username [-P password] [-o options]

       tsql   -C

DESCRIPTION
       tsql  is  a diagnostic tool provided as part of FreeTDS. It uses the TDS protocol directly to con-
       nect to Sybase or Microsoft SQL Servers, and allows the user to issue queries that test the  capa-
       bilities of FreeTDS.

       tsql  is  *not* a replacement for a complete isql, such as sqsh (www.sqsh.org).  It is designed to
       rely on the lowest level FreeTDS library, tdslib, as a way to isolate potential bugs in the proto-
       col implementation.

Answer 2

You might want to look at SQL Workbench/J

It's Java/JDBC based thus runs fine on Linux

It supports a GUI mode, interactive commandline usage (no GUI) and can be run in batch mode. It also supports exporting into various formats in order to take the "dump".

Answer 3

You can use sql-cli to connect to both on-premise and sql azure instance. It allows you to run one-off queries or run script files using .run command

Usage: mssql [options]

Options:

-h, --help                     output usage information
-V, --version                  output the version number
-s, --server <server>          Server to conect to
-u, --user <user>              User name to use for authentication
-p, --pass <pass>              Password to use for authentication
-o, --port <port>              Port to connect to
-t, --timeout <timeout>        Connection timeout in ms
-d, --database <database>      Database to connect to
-q, --query <query>            The query to execute
-v, --tdsVersion <tdsVersion>  Version of tds protocol to use [7_4, 7_2, 7_3_A, 7_3_B, 7_4]
-e, --encrypt                  Enable encryption
-f, --format <format>          The format of output [table, csv, xml, json]

Answer 4

Microsoft make a SQL Server ODBC driver for Linux, although it's binary only and only runs on certain 64 bit RHEL builds.¹ Anything that can consumbe ODBC (through the unixODBC driver suite) can use it. For scripting, you could use python, perl, or anything that plays with unixODBC to automate tasks.

The package also comes with versions of bcp and sqlcmd. Sqlcmd will support interactive querying or allow queries to be embedded in shell scripts.

1. Although you can bring it up on other builds by installing the relevant libraries - a process well known to people bringing Oracle up on Linux.

Answer 5

SqlTool is a JDBC CLI for interactive and automation uses. It does support exporting and importing in a fashion similar to Oracle's Sql*Loader (if you're familiar with that).