Batch Scripting SQLCMD–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I wouldn’t do this anymore, but I ran across a post where someone couldn’t use PowerShell in their organization. A poor decision, IMHO, for the Microsoft platform, but it is a restriction. In this case, the user wanted to get a batch file to run a SQLCMD script. This post shows how.

Two Files

Let’s suppose I have two files in a folder. In this case, I have a version.sql file that contains this:

select @@version

The other is my batch file, which I’ll name runsql.cmd. In this file, I’ll do a few things. First, suppress the code with this:

@@echo off

That’s just a good habit, though you might leave this out until things are working. Now, I will use a loop to get a list of files with an extension. I use the FOR loop in this way:

for %%x in (*.sql) do (

This gets a list of all .sql files in the current folder. For each one, we will process all statements inside the parenthesis. The open is on the line above, the close will be below.

The next lines are my sqlcmd call and the various items I need. In my case, I’ll get the instance name as a parameter and use trusted authentication.

  sqlcmd -S "%1" -E -i %%x

I could use other parameters (%2, %3, etc.) to get a user and password if I wanted to. Instead, I’ll get the instance as a parameter, and then pass the filename in to sqlcmd with the –i parameter.

When I run this, with my single .sql file, I see this:

2019-10-15 10_55_48-cmd

Easy to do, and I could add other .sql files in here if I wanted them to run.


This was a quick post to write in answer to someone asking a question. I knew about the %1, %2, and searched to find a quick SO post on getting filenames into a variable. It actually took about 5 minutes to research and test (and post) and then about 10 minutes to write this up.

You could do this, showing some knowledge of learning and creating a solution. For extra credit, how can I capture output of this?

Posted in Blog | Tagged , , | Leave a comment

Learning from Exercise

A long time ago I decided to run every day. I did for a bit, in fact, I had a neat milestone along the way and continued on for 1564 days. That was quite a memorable period in my life, and one that I look back upon fondly. I’ve aged a bit, and while I could do it again, I also have decided there are other things that matter more to me, so I have moved on in life.  I learned a lot from that time, lessons about myself and life that continue to help me today. Some of them even apply to work and my career, little nuggets of wisdom that help me find ways to find success in my life.

Apart from some better fitness, one of the main benefits for me was the sense of accomplishment. I had no bad days where I would think I wished I had run. None. That seems obvious, but it also provided me some comfort that I had moved things forward in my life. I took that lesson back to work, where I sometimes have bad days. I have days where nothing goes right, or I have people that take over my day and schedule.

I know there are also a number of things that I need to do at work, which aren’t that important, or even aren’t that critical, but they provide a measure of satisfaction that I’ve moved things forward. These days that is often getting things scheduled for the newsletter. In the past it might be doing a little space extrapolation (though I’d just look at this in SQL Monitor today). Low value items, but I get some accomplishment in the midst of otherwise stressful times when I get something small done that is on my list.

The other thing that came from the running streak was the feeling of getting away from other parts of my life. Whether with family or work, I might be overwhelmed or stressed or otherwise out of sorts in my day. Taking 20-30 minutes for myself was a way of resetting my day, and it was a welcome break. While exercise is my thing, and even today it’s a priority around work, I’ve had other crutches that help me cope. I love to read, so taking 5 minutes to escape in a book every day is something that feels similar to exercise.

I know other friends that might play a short game, play with a pet. Having some other consistent, distracting activity is a good release when things are tough. No matter how busy we are, or what’s broken, taking 5, 10, 15 minutes away can help refresh you and get you ready to go back into the trenches and deal with others.

I’m sure jobs exist that can’t spare the time, but I’ve never had one. I haven’t always realized it until later, but I wish someone had told me to take 10 minutes away in a crisis to reset myself and then come back. I know I’ll remember that the next time I end up being stressed during an IT problem at work.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

Posted in Editorial | Tagged , | Leave a comment

True HA and DR

For many years, SQL Server administrators have tried to ensure their systems run smoothly by executing DBCC commands against their production databases. As workloads have grown, this has become an issue with resource contention with production users. Backups have had the same issue, and the smart DBAs have offloaded this work to another server. With the more recent versions of SQL Server, we could move backups and DBCC checks to secondary systems.

The problem with this has been licensing. Microsoft has required a secondary node (often referred to as passive, which Allan Hirt notes is incorrect) to be separately licensed if any activity is performed, which includes DBCC. We could have a secondary system receiving data as a part of Software Assurance licensing, but no activity on this system. A common misunderstanding, and a long and often raised complaint by customers.

That has changed as of Nov 1, 2019. Microsoft posted a blog that now lets us use those secondary systems for a number of purposes. We can now run DBCC, log and full backups, and monitor resource usage data on a secondary system without requiring licensing. What’s more, we can now have multiple secondary systems that are not separately licensed for both HA and DR purposes.

Perhaps best of all, this isn’t limited to SQL Server 2019. This is in effect for all supported SQL Server systems. With SQL Server 2012 in Extended Support, I would assume this would apply there. The caveat is that you need Software Assurance (SA) on the licensed systems. You don’t get this for free, but many organizations get SA to allow them to upgrade, and this will be a welcome addition to the value for SA.

I think this should have been the way that licensing worked forever, as I’d argue DBCC isn’t a workload, but a verification that things are working appropriately. SQL Server should detect this stuff automatically to me, but since it doesn’t, this isn’t any active workload. Neither are backups. Finally, Microsoft agrees.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

Posted in Editorial | Tagged | Leave a comment

New HA Licensing Benefits

Microsoft announced recently that they were changing the way licensing works for HA and DR situations. I think this is a great change, and the summary is:

  • You can have up to 3 other un-used secondaries for free
  • You can a sync on-prem replica, async on prem replica, and a remote DR replica.
  • You can run DBCC, backups, monitor resource data on the secondaries

This is a huge change, mostly because most of us expected these would be acceptable uses already, but they weren’t. And many of us had to go ask for more licensing money.

The Caveat

There is a caveat here, actually two. You need:

  • Software assurance
  • A supported version of SQL Server

Those two things mean you need to be under a support contract, but also that this benefit isn’t just SQL Server 2019, but back ported to all supported versions.

That’s good new.

Posted in Blog | Tagged , , , | Leave a comment