The answer shouldn’t surprise anybody at this point: version control!
Side-note: I really should try using SSMS Solutions/Projects like Kenneth Fisher advocates, but I just haven’t gotten a chance yet. It would probably help with organization.
Well, perhaps you’re still in for a surprise. Because, you see, as much as I love using GitHub and Gist for my blog code samples, much of that love is lost in the daily grind of fix-it-tickets, BI building blocks, report development, and performance troubleshooting that comprises my business as usual. So forgive me if I haven’t made the leap to Git like all the cool kids are doing. I’m talking about a locally stored (i.e. on my very own hard-drive), locally managed, only-ever-have-one-user, repository of SQL scripts. All it really needs to do is track changes on a daily (ish) basis.
That’s what good ol’ Apache SVN is for! Yes, boys & girls, it’s still around. Consider it one of the many enduring foundational technologies that we tend to take for granted, or forget about, in the ever-present onslaught of the “new and shiny”.
Eeww, yuck! A non-distributed VCS?? What are you, some sort of wild animal!? The impudence!
Okay, calm down there sparky. Remember my requirements? They’re only about 5 sentences up. Go back and read them again. Breathe. We good? Good.
Another reason it doesn’t need to be distributed or ‘cloudy’ or web-based is that these scripts often contain private company I.P., so data governance demands that they stay within company IT space. And sure, there are private repos and ways to keep the stuff locked-down within a GitHub or similar, but again I ask, why? The first and primary requirement of my VCS is that it stays the heck out of my way when I’m working. I’m sure someone will counterpoint me, and I’d love to hear it. But for now, we’re keepin’ it local.
Getting Set Up
The first step, if it’s not obvious, is to install VisualSVN Server — it already contains the binaries for Apache SVN, so you don’t need to worry about that. It’ll ask you where to stick your repos and your backups; you can see an example of mine here:
Once it’s done, let it start up the manager GUI. Next step is to create a new repo:
I called mine “SQL_Scripts”. Because, as my blog’s tagline says, “why yes, I do like underscores.” When you go thru the steps, it’s easiest to leave everything at the default settings — that way you don’t need to futz with permissions or anything.
One ‘gotcha’ to note: in a corporate domain setting, you should set the server name to your machine’s FQDN, e.g.
MYWORKSTATION.company.com. This may be done for you when you install, but I’m not 100% sure, so it’s worth checking — you can right-click on the
VisualSVN Server (local) node in the left pane and go to
Properties, to the
Network tab, and verify it.
Next, install Tortoise SVN, or your favorite SVN client. I chose Tortoise because it’s easy to use, includes some very useful File Explorer options (right-click menu goodies), and supports standard command-line interaction just like vanilla SVN. We’re going to use said CLI interaction in just a bit!
Import (or is it Export?)
I almost always have trouble remembering which option is for use with a non-empty folder of “here’s a bunch of files that I want to dump into the repo to start with”, vs. “here’s an empty folder where I want to pull down the contents of an existing repo”. Fortunately, Tortoise yells at you if you try to do the latter — which is
Export — into a non-empty folder. So we want to
Import. Assuming you have a folder where all your SQL scripts live already, right-clicky and say “Tortoise SVN .. Import.”
You can verify that it worked by switching back to your VisualSVN Server for a moment, refreshing it, and seeing that the repo’s contents are now.. your happy files!
But wait… the folder I
importedinto the repo doesn’t have the pretty little icon overlays showing me that it’s in SVN… Why?
Don’t panic. We have one more step to go.
Right-clicky again! On the same folder you imported into SVN. You now want to “SVN Checkout…”, which will essentially mark all your files as “versioned”, because it will see that they all match exactly what’s already in the repo (because you just imported them a few moments ago).
There’s an important ‘gotcha’ here, if you named your repo something other than the folder name that’s serving as your repository root. By default, it will try to pull the repo’s contents into a sub-folder of the same name as the repo. In the example below, that’d be “Workspace\PersonalScripts”, instead of just “Workspace”, which is where I want it to go. This has happened to me more than once. Check it out:
Pull the trigger, and you’ll see all your files getting ‘Versioned’, like so.
Yay? Yay. Now for the cool part, where you can stop thinking about all of this and just let the machines do their work.
Automatic Nightly Check-In
Windows Task Scheduler, specifically. Let’s be honest, if you’re reading this far, you’re not really a CLI jockey. And you’re certainly not a Linux geek — you could have had all this set up and done with about 5 lines of bash, I’m sure. Party on, Wayne. Us Garth’s still need a bit of help.
I’m not going to do a walk-thru screen-shot montage of Task Scheduler; it’s pretty idiot-proof if you read & understand the dialogs. Here’s the key bits, the commands that you’ll actually want to enter as the actions. We have two actions: first “add all new files to the repo”, followed by “commit everything new & changed”. Schedule it however often you’d like; mine happens nightly at 8pm, when I’m about 99.9% sure I won’t be touching work. Oh, make sure to set it to “Run whether user is logged on or not”.
The actions are both “Start a program”, and said program will be the SVN executable, which should be (by default) located at
C:\Program Files\TortoiseSVN\bin\svn.exe. Here are the arguments, respectively (1st action, then 2nd), subject to personalization of course!
add --depth infinity --quiet "C:\Users\Documents\Your\Scripts\Folder" --force --username YourUsername
commit --depth infinity --message "daily" "C:\Users\Documents\Your\Scripts\Folder" --force --username YourUsername
And that about does it! Now, without any need to remember any command-line syntax, or to touch a right-click menu again, your SQL scripts are being silently versioned and committed every night to your very own local repository. If you ever forget when or what you did last week or last month, or need to “blame” yourself (which is short-hand for “show me all the changes from this part of this file so I can see what led to its current state”)… now you can.
PS: If you’re using a fantastic 3rd party diff/compare tool like I am, be sure to plug it into the TortoiseSVN options for diff-viewing/comparison. While their built-in interface ain’t too bad, BeyondCompare and other similar tools are even better.
Thanks for reading!