Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Automate SQL Server Administration Tasks Using the Windows Command Prompt


By:   |   Last Updated: 2019-01-22   |   Comments (3)   |   Related Tips: More > Database Administration

Problem

Microsoft batch commands have been an existence since the DOS days. With the growth of Windows, most of the commands are enhanced and continue to be an important part of the Windows administration toolset.  If I do not know PowerShell, can I still automate some SQL Server administrations tasks with native Windows commands?  For example, if I want to deploy or execute multiple scripts across multiple server instances, what can I do?

Solution

Before we start the solution, we need to give a brief introduction of the Windows command that we need to use for this.  The command is: FOR.

In Windows 8.1 or Windows 10, we can start a command prompt window by running cmd.exe and in the window, type:

help FOR

We will get the following:

variable

We can first do a quick test by running the following command:

For %v in (hello, world) DO @echo %v			

We will get this:

hello world

So it is easy to do something like the following: say I want to get the version information of two SQL Server instances.  The code would be:

for %s in (.\sql2016,  .\sql2014) do sqlcmd -S %s -Q "set nocount on; select @@version"
sql server

Now we will use a few cases to see how we can use the command line commands/tools for real DBA tasks.

Case 1: Execute Full SQL Server Backups of All Databases on an Instance

We can of course write T-SQL, with dynamic SQL statements, to execute full backups of all databases on a server, but arguably for logic simplicity, we may rely on command line tools to do this task.

The logic is as follows:

  1. Use SQL Server's own tool BCP.exe to dump database names to a text file
  2. Use FOR /f to parse the text file from step 1 and then call the sqlcmd.exe to do the backup

The following command will be run from a regular windows command window. It will query against one of my local named instance .\sql2016, and then dump the query result to a csv file as "c:\test\db.csv".

bcp "select [email protected]@Servername, name from sys.databases where name <> 'tempdb'" queryout "c:\test\db.csv" -S .\sql2016 -T -c -t "|"
			
network packet size

If I open this c:\test\db.csv, I will see the following:

adventure works

[TP_W520] is my local computer name, so the csv file has basically two columns, separated by a vertical bar |.

The first column has the SQL Server instance name, i.e. TP_W520\SQL2016, and the second column has the database name.

To read this file c:\test\db.csv, we can use the following command to verify:

for /f "tokens=1,2 Delims=|" %x in (c:\test\db.csv) do @echo %x %y 			

Now we can do the backup with the following command (assuming the backup folder is c:\mybackup\):

Set BackupPath=C:\MyBackupSet MyDate=%Date:~10,4%%Date:~4,2%%Date:~7,2%
for /f "tokens=1,2 Delims=|" %%x in (c:\test\db.csv) do sqlcmd -S %%x -Q "backup database %%y to disk='%BackupPath%full_%%y_%MyDate%.bak' with init, compression"
			

We can put all these lines into a batch (or cmd) file, and run the batch file. Once the batch execution finishes, we can check the c:\MyBackup\ folder, and see the backup files generated there:

my backup

The second line needs some explanation, it is actually to "cook" the internal environment %Date% into a YYYYMMDD format value. In my environment, %Date% value is like the following:

test

%Date:~m,n% is like a T-SQL substring function, it means from mth position (0 based, i.e. first character is at 0th position), get the following n characters.

So for %Date:~10,4%, it will retrieve 2019. To verify, we can run the following commands:

echo date

Case 2: Run all SQL Server T-SQL Script Files in a Folder Against Multiple SQL Server Instances

There are many cases when we need to run one or multiple scripts under a folder against multiple SQL Server instances. To demonstrate this, we will do the following, under c:\Deployment\ folder, I will have two *.sql scripts as follows:

deployment

The two scripts are:

--01-create_table.sql
use tempdb
if object_id('dbo.t', 'U') is not null
 drop table dbo.t;
go
create table dbo.t (id int identity primary key, c varchar(120));
go
--02-insert_table.sql
use tempdb
insert into dbo.t (c) values ('hello world');

Assume I need to run the two scripts in order, i.e. 01_xxx.sql should be run before 02_xxx.sql against two SQL Server instances on my local computer, i.e. localhost\sql2014 and localhost\sql2016, I can use the following command line:

for /f %x in ('dir /B /S /O:N c:\deployment\*.sql') do (for %s in (localhost\sql2014, localhost\sql2016) do (sqlcmd -S %s -i %x))
			

This is nested FOR command lines, the first FOR is to list all *.sql files in C:\Deployment folder, sorted by the name, DIR /O:N means "Order by Name".

deployment

In each SQL Server instance, I can see the result as shown below:

local host

Case 3: Upload portions of a CSV file to a SQL Serve Table

It is pretty easy to use bcp or bulk insert to do the data import, however, if we only want a partial import, i.e. importing some columns of a csv file to a few columns of a target table, we have to use a format file to define the source or target columns we want to use (or skip). You can see details about this topic from here or here.

But in many cases for such partial imports, we may just do it via a simple command line. To demonstrate this, let’s first create a csv file and then a table as follows.

The csv file, c:\test\employee.csv is as follows:

employee number

We have a table as follows:

use tempdb
if object_id('dbo.Employee', 'U') is not null
 drop table dbo.Employee;
go
create table dbo.Employee (
id int identity primary key,
EmployeeNum int,
Position varchar(30),
FirstName varchar(50), 
LastName varchar(50),
LogDate DateTime default getdate());

So assume we only need to import EmployeeNum, FirstName, LastName and Position columns into the dbo.Employee table, we can do so as follows:

for /f "skip=1 tokens=1,2,3,5 delims=, " %e in (c:\test\Employee.csv) do sqlcmd -S .\sql2016 -d tempdb -Q "set nocount on; insert into dbo.Employee (EmployeeNum, FirstName, LastName, Position) Values (%e, '%f', '%g', '%h')"

The command skips the first line, i.e. the header line of the csv file by using skip=1, and it then picks column 1, 2, 3 and 5 (i.e. EmployeeNum, FirstName, LastName, Position) from the csv file (skipping the 4th column, i.e. Gender column) by using tokens=1,2,3,5 and then puts them into the Value clause of the Insert SQL statement using %e, %f, %g and %h.

After running this command, I can see the screen result is like this:

employee

And checking the table, we can see the data:

employee table

Of course, this method may not be the best way if you need to insert thousands of records as this method is basically doing multiple inserts instead of using the more efficient bulk insert. But it is really convenient when the workload is not big and you can type out the command quickly and see each insert executed.

Summary

In this tip, we have demonstrated how we can do some common DBA tasks by just using native Windows commands and SQL Server native tools (bcp.exe and sqlcmd.exe). Actually, Windows OS already has an abundance of useful command line tools, such as service control/management utility, sc.exe, multiple file process utility forfiles.exe, and many others. Another very useful and new cross-platform utility provided by Microsoft is mssql-scripter, actually Microsoft has released a command line cross-platform query tool called mssql-cli as well.

I am a big fan of PowerShell, but I still find many existing Windows command tools can be valuable in handling scenarios that may otherwise need specific knowledge or skills, for example, if I want to see who is currently logged into a remote server that I need to reboot, I will just use the Query User command, simple and robust.

When we combine all these existing tools via batch files, we can achieve lots of things without needing to reinvent another tool via either PowerShell or CLR functions.

Next Steps

As a DBA, we do not work with SQL Server alone, we actually work with the whole environment where our SQL Server instances reside. Windows OS commands are in fact the basic essential tools that we can use to manage the environment. So to be able to use these tools, and especially those that help us most for DBA tasks, this should be a must have skill for DBAs.

You can review the following links to get more familiar with various Windows commands.



Last Updated: 2019-01-22


get scripts

next tip button



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, January 28, 2019 - 10:33:56 AM - jmoden Back To Top

I've not read through this entire article, but I will.  My initial thought is that it's really nice to see someone realize how useful the command propt is for SQL Server instead of using PowerShell for everything.  Heh... now... if we could just teach people that xp_CmdShell isn't the security risk people think it is when used properly. ;)


Wednesday, January 23, 2019 - 2:15:38 PM - jeff_yao Back To Top

Thanks @Kenneth for your kind words. To make good use of the "old" stuff is always fun, and actually native batch scripting (with the help of various existing command line utilities) is more capable than we may be aware of, while there is almost no learning curves (even compared to learning Powershell)


Wednesday, January 23, 2019 - 10:13:16 AM - Kenneth Igiri Back To Top

 Really eye-opening, Jefferey. Thanks


Learn more about SQL Server tools