My company utilizes a midnight installer process that rebuilds a database every night for a {QA, Prod} environment. But sometimes we the developers need to rebuild our databases from these scripts. Previously I would open up the query window. Assemble all the scripts from each file into one file. Copy and paste all the sql scripts details into the query window and execute. Bleh, slow and I hated it. But now if I need to execute a bunch of sql scripts I can do so in a batch file.
- First I want to open up a “run as” admin command prompt.
- Start –> “Search Program and Files”, type in “cmd” this should auto focus on cmd.exe found.
- Then hit “ctrl” + “shift” + “enter” and it will open up a command prompt in administration mode. You can tell because the command prompt will say “Administrator.”
- Normally I just opened up sql query and copy and pasted the single scripts that run the create database and constraints/foreign key scripts. But now I need to add all the stored procedures. In our company we keep a static database of that product with all of our stored procedures to keep them all in once location. With one table “About” for versioning the set of stored procedures/functions etc. So I will browse to my current TFS workspace location. In my case: “cd C:\srs_source\Team\Product\Main\Databases\ProductCode\Database\Programmability\Stored Procedures”.
- Now I use the command line “dir /b > myFile.cmd” (dir /b list directory with the format I want) and the greater than sign “>” (writes the command output to a file, instead of the command prompt window).http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/redirection.mspx?mfr=true
- Open up “myFile.cmd” in Notepad or I use Notepad++
- Remove the “myFile.cmd” from the list
- Place cursor on first line and hit “ctrl” + “h” for Replace window prompt.
- In Find what type: “\r\n”
- In Replace with: type: “\r\nsqlcmd –E –n –i” (Update) I now use something like “\r\nsqlcmd -d <databaseName> –E –i” i.e. “\r\nsqlcmd -d Mpi_Data –E –i”
- Hit the button “Replace All”
- This will replace everything but the first line. At the bottom you will have a empty command “sqlcmd –E –n –I” just copy this to the first line and you are set to run your batch command.
- We are always certain all our sql scripts have the “USE [ProductCode]” for each of our “CREATE PROCEDURES”at the top of each .sql so from this point we can just run it on the command line “myFile.cmd” and it will add these stored procedures to the correct database.
