You know, there is nothing like a painfully long set of SQL scripts that need to be applied to multiple databases to get the creative juices flowing. As I stared at a list of 29 scripts that needed to be applied to at least 2 different databases (it could be much worse), I thought to myself, “There has to be a better way”. SQL Server Management Studio (SSMS) is a great tool, but I do hate how it makes you login to the database with each script you open. What is even worse, when I tried to open all 29 scripts at once it somehow spawned 7 different instances of SSMS in no apparent logical manner. Time to do some research.
I have used SQLCMD in the past, and I knew this would be the source of my enlightenment. SQLCMD is the 2005 command line successor to OSQL for SQL Server 2000. It allows you to execute SQL scripts at the command line. Nice.
Without any further banter, I’ll explain how you too can shave potential hours off of your DBA tasks. Note, this is a SQL Server specific tactic. I’m sure there is a way to do it in Oracle as well, but you’ll have to find some other chum to feed you that.
- Pick a folder to consolidate your .sql scripts (ie C:\AutoDBA)
- Create a text file called RunDBScripts.bat with the following contents
REM == LOOP THROUGH ALL SCRIPTS AND RUN THEM, APPENDING TO LOG FILE
FOR %%f in (*.sql) DO sqlcmd -S %1 -d %2 -i %%f >> %1.%2.log
- Create a second file called RunDBScripts.SERVER.DATABASE.bat (replacing SERVER and DATABASE with your specifics)
RunDBScripts.bat SERVER DATABASE
- Execute the file created in step 3
That’s it, seriously. Try it out and you’ll be amazed and the time it can save you! You’ll also have a nice log file with the output results of the scripts.
The work is really done in the script created in step 2. You loop through all files based on the *.sql wildcard. For each file, it will issue the SQLCMD passing in the server, database, and file name. The >> pipes the output to a log file. Windows Authentication will be used by default, but you can also modify this to specify a username and password.
If anyone has any schnazzy additions to run from a UNC path or add a timestamp to the logfile name those would be worthwhile additions.
Thanks to these links for help in getting to this solution: