Connecting SQL Server using Sqlcmd and Bash Script

Share This Blog

Abstract

Database Administering of MS SQL Server can be done using many tools and utilities. One of them is SQLCMD command line tool used in a non-graphical interface medium. Since SQL server is also available in Linux this command line tool (SQLCMD) is available Bash as well as PowerShell. Window Subsystem Linux (WSL) also can be used to run Bash scripts on Windows 10 Environment. With the power of scripting, repetitive tasks of the Database Administration can be automated and scheduled easily. In this blog we will perform some common database jobs using SQLCMD utility and Bash Scripting.

Prerequisites

Bash environment (Either on Linux or Window Subsystem for Linux). We are using WSL Ubuntu in this blog.
‘sudo’ privileges for installing sqlcmd utility.
MS SQL Server Database user with proper credentials.
SQL SERVER MANAGEMENT STUDIO (SSMS) for query editing and testing.

Objectives

Installing sqlcmd utility.
Connecting to SQL Server Database
Advance Query – Passing variables from command line
Storing single result to a Command-line Variable
Getting CSV File as Output
Automating Database Backups

Walk Through Objectives (Hands-On)

Objective 1: Installing sqlcmd utility

In this blog we will be using sqlcmd utility. It is a command-line utility for ad hoc, interactive execution of Transact-SQL statements and scripts and for automating Transact-SQL scripting tasks. To use sqlcmd interactively, or to build script files to be run using sqlcmd, users must understand Transact-SQL.

Full installation documentation can be found here!

Install on ubuntu as from official documentations.

su
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
sudo apt-get update
sudo apt-get install mssql-tools unixodbc-dev

If encounter error during installation, try this.

su
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
echo "deb [arch=amd64] https://packages.microsoft.com/ubuntu/18.04/prod bionic main" | sudo tee /etc/apt/sources.list.d/mssql-release.list
sudo apt update
sudo apt install msodbcsql17
sudo apt-get install mssql-tools unixodbc-dev

Verify your installation. The result will be like this.

sqlcmd -?

Objective 2: Connecting to SQL Server Database and querying data.

Syntax for Connecting to the SQL Server Database.

sqlcmd -S <Server> -U <Username> #Use -P to explicitly define password.
USE <DATABASE_NAME>
GO
<SQL QUERY>
GO

# use EXIT to return to command line

Inline query to get results from the database

sqlcmd -S <Server> -U <Username> -P <Password> -Q <”SQL Query”>

Get SQL Script file as input and output Results as text file.

sqlcmd -S 192.168.1.167 -U sa -P F987club$ -i SqlScript_input.sql -o Result_output.txt

Objective 3: Advance Query – Passing variables from command line

Here we will create an array having 4 table names. Then we will pass each table name to Query script for returning number of rows in that table.
Sql Script File: SqlScript_input.sql is as below. Here $(tablename) is the variable that will be referred in sqlcmd.

SELECT COUNT(*) FROM [ADVENTUREWORKSDW2017].[dbo].[$(tablename)];

Create Bash Script file names Variable.sh as given below

#!/usr/bin/bash
echo "---------Variable Passing Example---------"
# Declare an array of four tables
declare -a Tables=("DimAccount" "DimProduct" "DimReseller" "FactInternetSales")
# Return Count of rows of each table
for i in "${Tables[@]}"
do
echo "$i"
sqlcmd -S 192.168.1.167 -U sa -P "F987club$" -v tablename=${i} -i SqlScript_input.sql
done

Run the command ./Variable.sh to execute the script. Result is given Below:

Objective 4: Storing single result to a Command-line Variable

Store row count to a variable can be useful in ETL task, like take backup after certain row count.
Here the query result of single output (eg. count of rows) will be store to a variable. In general, there is no direct methods to get this done. We will take help of echo, head, awk and pipes (‘|’) to get this job done.

count=$(echo $(sqlcmd -S 192.168.1.167 -U sa -P "F987club$" -v tablename=DimAccount -i SqlScript_input.sql -h-1 -W -w 700) | awk '{print $1}')
echo $count

Objective 5: Getting CSV File as Output

Create file “sqlforcsv.sql” having below SQL Code

/*Sql Script
Data may contain comma inside it.
We must enclose them with double quotes("") before writing into CSV file.
*/
SELECT TOP 30
[AccountKey],
'"'+[AccountType]+'"' as "AccountType" ,
'"'+[AccountDescription]+'"' as "AccountDescription"
FROM [ADVENTUREWORKSDW2017].[dbo].[DimAccount];

Create file “WriteCSV.sh” Bash Script and run > ./WriteCSV.sh

#!/usr/bin/bash
#Create Headers(optional)
echo "AccountKey","AccountType","AccountDescription" > ResultDataOut.csv
#Write Results to a CSV File.
sqlcmd -S 192.168.1.167 -U sa -P "F987club$" -i "sqlforcsv.sql" -o "ResultData.csv" -h-1 -s"," -W -w 700
#Result contains (30 rows affected), so needed to remove this.
cat ResultData.csv | head -n-2 >> ResultDataOut.csv

Open the CSV file in Excel and verify data.

Objective 6: Automating Database Backups

Create SQL script to take Backup. There are two variables to pass, one is Database Name another is backup file name.

BACKUP DATABASE $(dbname) TO DISK='$(backup_filename)';

Timestamp will be added to the backup file name. All the backups will be stored in default backup location unless path is changed.
Backup location on Server can be found as: Right click of Database > Tasks > Back Up…

Bash Script to take backup of database.

#!/usr/bin/bash
clear

cat << backup
############################################
BACKUP DATABASE SQLSERVER
©ganeshm@prisoft.com
version 1.1 Autobackup
############################################
backup
echo -e '\n';
#Getting Timestamp
timestamp=$(date +%Y-%m-%d_%H%M)
#Getting Database name from command line argument
dbname=$1
#createing logs directory
if [ -d logs_mssql ]
then printf "\nlogs available at logs_mssql\n";
else
mkdir logs_mssql;
printf "\nlogs available in logs_mssql\n";
fi
#checking database name in Argument
if [ -z "$dbname" ]
then echo "NULL Argument" 2>&1 | tee "logs_mssql/logs_"$timestamp".txt"
exit 0
else
#-----------Backup_Script--------------#
printf "\nBacking up $dbname\n"
filename=$dbname'_'$timestamp
sqlcmd -S 192.168.1.167 -U sa -P "F987club$" -v backup_filename=$filename dbname=$dbname  -i sbackup_sqlscript.sql 2>&1 | tee "logs_mssql/logs_"$timestamp".txt"
exit 0
fi

Test the script using following syntax:

# ./<Scriptname> <DatabaseName>
#Example:
./backup_auto.sh AdventuresWorksDB2017

To Schedule the job Crontab is used. You can refer for crontab in internet.

export EDITOR=nano; #choose nano as default editor
crontab -l #list current crontab jobs
ps -ef | grep cron | grep -v grep #Check Crontab run status
/sbin/service cron start #start Cron Scheduler if not started
crontab -e # Edit Crontab Schedules file
# Add following line to the bottom of crontab file.
00 00 * * * echo $(/backup_sqlserver_auto.sh) > /test.txt

#This will run the task 12 AM every day.

Conclusion

Having Scripting access to MSSQL SERVER opens whole new opportunities of automating tasks, repetitive task etc. Suppose you have 10 servers to be updated with new tables and repeating this task daily. Such task can be done using Scripting. Creating multiple users across different server and different privileges can be done using scripts.