Command Line
Part of a (hopefully ongoing) series of articles about using command-line utilities with SQL Server.
Robocopy is one of, if not the, best life-saving/greatest-thing-since-sliced-bread command line utilities ever to come from Microsoft. If you're not using it already, what are you waiting for?
Of course, being a Microsoft product, it's not exactly perfect. ;) Specifically, it sets the ERRORLEVEL to a non-zero value even if the copy is successful. This causes a problem in SQL Server job steps, since non-zero ERRORLEVELs report as failed.
You can work around this by having your SQL job go to the next step on failure, but then you can't determine if there was a genuine error. Plus you still see annoying...
I've set up a new category on my blog where I plan to post some articles about using command-line utilities to perform various tasks for, or in support of, SQL Server. I'll go over the basics like bcp, osql, DOS commands, and hopefully go more in-depth on some other utilities from the Unix/Linux world that are incredibly useful. I'm hoping to even post a full case study where an existing monolithic, mission-critical VB app is replaced with command-line utilities (depends upon workload and office politics though) I won't post all of them in the main SQL Team blog page, so you...
As Damian pointed out, you can do a lot of cool things with DOS command-line functions. Lately I'm particularly enamored of the for command.
The DOS for command works like a for...next loop in Basic, C/C++/C#, Java(Script) etc., only you can have it traverse a filespec (like dir *.txt would), a directory tree, or even the contents of a file. To modify Damian's example a little, you could actually execute each individual SQL script with the following:
for %a in (*.sql) do osql -E -Sserver <%a
The %a is a DOS replaceable parameter, and it holds the complete file name of each file in the *.sql...