Best way to deploy 100+ sql files.

Question

Best way to deploy 100+ sql files.

SQLisAwe5oMe

SSChampion

Points: 14047
More actions
November 27, 2015 at 10:12 am

#313388

We have 100+ sql files, while deploying we want to create one master.sql and inside this master.sql we want to call all these .sql files
what is the good approach to do that in SQl Server?
Please advise. Thanks in advance.
Regards,
SQLisAwe5oMe.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Keep the files separate. It's an advantage that you'll realize later. Just make a batch file that executes the scripts in the order that you want them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 2

Hi Jeff,

If you don't mind, can you please elaborate on it, or provide steps...

I am not that good with .bat files or scripts.

Regards,
SQLisAwe5oMe.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 3

A batch file is nothing more than a series of commands you can execute at a command prompt. Similarly, a stored procedure is a series of T-SQL statements that are executed. Both can get involved, but the basics are the same. Without getting too fancy, batch files work at the system level and procedures work at the database level.

There are many sites out there that deal with batch files, but what you want to do it pretty simple. If you want to execute the T-SQL scripts, first determine the order and then write commands to execute them in the appropriate sequence.

The sqlcmd and osql utilities are the two options I know of to to this, but osql is targeted for termination in a future version.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 4

Okay Thanks Jeff, appreciates it.

Regards,
SQLisAwe5oMe.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 5

Is the schema same for all the databases that you would be deploying on?

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 6

SQLisAwe5oMe

SSChampion

Points: 14047

November 27, 2015 at 2:25 pm

#1842686

Yes, its the same database.

Regards,
SQLisAwe5oMe.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 7

I have used Red Gate's Multiscript[/url] , awesome tool if you want to deploy a single or set of scripts across databases which have same schema. You can also control if you want these to execute in parallel or serial order. Worth every penny, you can do a 14 day trial just to see if this works for you.

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 8

batch file is capable of executing all .sql files in a folder which includes sub directory

@echo off

setlocal enabledelayedexpansion

set /p servername=Enter DB Servername :

set /p dbname=Enter Database Name :

set /p spath=Enter Script Path :

set hr=%time:~0,2%

if "%hr:~0,1%" equ " " set hr=0%hr:~1,1%

set logfilepath= %spath%\output_%date:~-4,4%%date:~-10,2%%date:~-7,2%_%hr%%time:~3,2%%time:~6,2%.log

set cmd='dir %spath%\*.sql /b/s'

FOR /f %%G IN (%cmd%) DO (

echo ******PROCESSING %%G FILE******

echo ******PROCESSING %%G FILE****** >> %logfilepath%

SQLCMD -S%servername% -E -d%dbname% -b -i%%G >> %logfilepath%

IF !ERRORLEVEL! NEQ 0 GOTO :OnError

)

GOTO :Success

:OnError

echo ERROR ERROR ERROR

echo One\more script(s) failed to execute, terminating bath.

echo Check output.log file for more details

EXIT /b

:Success

echo ALL the scripts deployed successfully!!

EXIT /b

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 9

Thanks John for providing the script.

Regards,
SQLisAwe5oMe.

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 10

johnwalker10

SSCrazy Eights

Points: 9124

November 27, 2015 at 10:07 pm

#1842701

I am glad you resolve your problem :smooooth:

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

SQLisAwE5OmE (11/27/2015)
Thanks John for providing the script.

Do you understand how it works and how to control the execution order of the scripts using John's method?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 12

Hi Jeff,

I did not test the script yet, but i kind of got an idea...i need to enter the server name, db name and the path details.

In regards to the execution order, i am not sure where it specify that.

If you dont mind explain it, please do so. Thnx.

Regards,
SQLisAwe5oMe.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 13

SQLisAwE5OmE (11/28/2015)
Hi Jeff,
I did not test the script yet, but i kind of got an idea...i need to enter the server name, db name and the path details.
In regards to the execution order, i am not sure where it specify that.
If you dont mind explain it, please do so. Thnx.

John's batch file is written to process each and every files with the .sql extension in a given path, including subdirectories. The command to generate the list of files to process is this one:

set cmd='dir %spath%\*.sql /b/s'

If you want to control the order in which they're executed (and you probably need to so) then a simple DOS sort on the dir command is the way to go.

I use a simple naming convention for my script files. I number them, but any ascending sort order will work. For example, the first one is named 001_tables.sql and the second is named 002_procedurea.sql and so on. The descriptive part of the filename is really for your benefit; the numbers are what you'll focus on in the script.

The DOS sort for the name is /on. There are others, but this is probably the one you want here. Your new command would then become:

set cmd = 'dir %spath%\*.sql /b /s /on';

I also keep my script files in a single directory, which means I don't need the /s from the command line. If you happen to have any directories in your path that contain ".sql" in the name, then you'll want to exclude them like this:

set cmd = 'dir %spath%\*.sql /a-d /b /s /on';

You're probably getting the idea that there are more than a couple of command line switches for the dir command and you're right. You can open a DOS window and fire the following to get a list of them.

dir /?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 14

SQLisAwE5OmE (11/28/2015)
Hi Jeff,
I did not test the script yet, but i kind of got an idea...i need to enter the server name, db name and the path details.
In regards to the execution order, i am not sure where it specify that.
If you dont mind explain it, please do so. Thnx.

The best thing to do here would be for me to encourage a little self study because this stuff is going to come up a whole lot over your career. Ed Wagner started to touch on that with typing a command followed by /? to get the options for the command but there's a whole world of help available to you for these various DOS commands. To survive, Google is definitely your friend but you can also click on the Windows Start Button (or however the hell they do it in "Metro" (2012+)), click on "Help and Support", and then do a search for "command reference overview" for some tips and a whole array of very useful command line commands. Spend some time reading up on them because, even with Powershell coming about, these commands can be very powerful and they're not going away any time soon.

There's also another "system" known as WMIC that you might want to take a dive into that can do some incredible things without have to write even a single loop like that which is frequently required in PowerShell.

... and it's all FREE! Comes with Windows!

SQLCMD is how you run SQL Server commands/scripts from the Command Line. That would be in "Books Online" for SQL Server.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)