what is the Best way to run huge script on production server ?

Question

what is the Best way to run huge script on production server ?

nagkarjun1

Ten Centuries

Points: 1020
More actions
August 8, 2012 at 1:20 pm

#259812

what is the Best way to run huge script on production server ? (code drops)
The script has several alter procedures to run against production server.
Do I need to execute the entire script at once or need to execute each alter procedure one by one.
Please suggest a best way to do this task...
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

I would suggest a single script with a go between each batch. It is far less prone to error than using a whole bunch of files and trying to remember which one(s) you have already executed. Just make sure that your scripts are able to run more than once without causing any issues.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 2

If you are running ALTER statements or drop and creates it might be more manageable to run each one as a separate script..perhaps using sqlcmd. You can then output to a file and capture any errors

Just a thought

Graeme

Scott D. Jacobson SSCertifiable Points: 6149 More actions · Answer 3

Why not use something like SQL Delta[/url] or Redgate's own SQL Compare[/url]?

xia.wu.1983 SSC Enthusiast Points: 128 More actions · Answer 4

A better practice is to print out the result of each alter statement and pipe all output into a log, so easy to find out which procedure is causing error if any.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

nagkarjun1 (8/8/2012)
what is the Best way to run huge script on production server ? (code drops)
The script has several alter procedures to run against production server.
Do I need to execute the entire script at once or need to execute each alter procedure one by one.
Please suggest a best way to do this task...
Thanks

Do they all have to go or none go? Also, step 1 for this should be to make a backup of the database or at least the original stored procedures especially if you're not managing revision control with something like SVN.

--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)

Scott D. Jacobson SSCertifiable Points: 6149 More actions · Answer 6

I have to second Jeff's sentiment. You should also be using some kind of version control on the code. Whatever tool you're comfortable with is fine. I like to be tool agnostic so whatever works best for you.

CarlaAbanes Right there with Babe Points: 782 More actions · Answer 7

i would suggest using Redgate's SQL Compare, its a great tool especially for this scenario where you need to run a batch of alter scripts. If you dont have this tool now, i would suggest running the alter scripts one by one so you would know which one is successful and which one failed.

Cheers! 🙂
[/url]

Jared SSC Guru Points: 61793 More actions · Answer 8

Carlaabanes (8/13/2012)
i would suggest using Redgate's SQL Compare, its a great tool especially for this scenario where you need to run a batch of alter scripts. If you dont have this tool now, i would suggest running the alter scripts one by one so you would know which one is successful and which one failed.

If the script is already written, what difference does this make? You are also assuming that the script is all from 1 database and that all changes are needed. Don't get me wrong... SQL Compare is a really useful tool. However, if you already have a script or have been compiling one based on approved changes, what benefit does it give you?

Jared
CE - Microsoft

Sergiy SSC Guru Points: 110208 More actions · Answer 9

If you have a single script ready you may just run it from Command Shell using sqlcmd tool (2008 replacement for osql)

If you actually have a bunch of scripts you may run them all using a loop:

FOR /F "delims=" %%i IN ('DIR /A-D /b /ON *.sql') DO (

sqlcmd -U UserName -P Password -S Servername -d Database -i "%%i" -I

)

Make sure that your scripts are numbered in the same order as you want them to be executed.

_____________
Code for TallyGenerator