how to deploy 10 T-SQL scripts across 200 databases

Question

how to deploy 10 T-SQL scripts across 200 databases

Viewing 6 posts - 16 through 20 (of 20 total)

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

premkumardr Say Hey Kid Points: 708 More actions · Answer 1

Luis Cazares - Wednesday, August 2, 2017 10:17 AM
Jeff Moden - Wednesday, August 2, 2017 8:42 AM
premkumardr - Tuesday, August 1, 2017 5:26 PM
sqlguy80 - Tuesday, July 25, 2017 12:37 PM
Hi ,
Can any one suggest an efficient way where we can apply about 10 T-SQL scripts on about 200 databases and show the management a log file that the scripts were applied correctly?
We cannot use any third party tools.
Thanks,
I would suggest you to use central management server for ease the deployment across different servers and databases. You need to make sure the database name remains the same across the enterprise.
That's a nice thought but what does it take to setup CMS to recognize the 200 databases that are differently named on the Same server that the OP has?
With some basic scripting and time to understand the xml, he could use a poor man's version of CMS by registering servers and grouping them using a .regsrvr file. Then the queries can be run for the entire group. Although, I'm not sure I would suggest that for all those databases.

This is a workable solution which is followed across many enterprise environments.
Follow the link to setup the central management server CMS

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

Again, I agree that CMS is fine for administering multiple servers but this problem (according to the original post) has nothing to do with multiple servers. It has to do with running 10 scripts across 200 databases on the same server. How does CMS help you in that area?

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

barsuk SSChampion Points: 12280 More actions · Answer 3

at my previous job we run it in a few ways:
If all databases are located on the same server and scripts are relatively small, then you can run master..sp_MSForeachdb.
For multiple servers and hundreds of databases we have the following:
On the monitoring server we created a stored prod using sqlcmd:
usp_sqlcmd:
exec master.dbo.xp_cmdshell ''sqlcmd -S @s-2 -d @d -U UserID -P Password -i C:\ScriptDirectory\ ' + @Script + '''
You place all your scripts to be executed in C:\ScriptDirectory\Script.sql
You will need to generate a list with Server Names and Database Names, providing that UserIDs and Passwords are the same for all servers.
And then you will need to populate the following script(s) and run from the monitoring server:
'exec master.dbo.usp_sqlcmd ''' + servername + ''',''' + DBName + ''', ''Script.sql''''
So essentially we could have had 300 lines(against 300 databases) which you can run sequentially in 1 window or copy\paste in many windows as you wish.
You also can execute it inside the job, but you would like to have some sort of control, so that is why I was running it in 1 window as time was never an issue.
Sorry, I don't have those scripts in front of me...

dataoverride SSC Enthusiast Points: 117 More actions · Answer 4

Jeff Moden - Wednesday, August 2, 2017 8:08 PM
Again, I agree that CMS is fine for administering multiple servers but this problem (according to the original post) has nothing to do with multiple servers. It has to do with running 10 scripts across 200 databases on the same server. How does CMS help you in that area?

Hi
You could always register each database in you registered servers list, using the Default Database option.
This is something we practice for running quick MI across multiple client databases hosted on the same server.

Cheers
Steve.

RTaylor2208 SSChampion Points: 13298 More actions · Answer 5

A basic approach is to write this into a batch file along these line, (PS untested as I just threw this together)

SET LOCAL ENABLEEXTENSIONS
ECHO OFF
REM Get list of databases to a file, filter using the statement after -Q
sqlcmd -E -S MyServer -d master -Q "SELECT name FROM sys.databases WHERE Name like '%'" > DBLIst.txt

REM For every DB in the file execute the scripts
for /f %%a IN (DBList.txt) do sqlcmd -E -S MyServer -d %%a -i ".\scripts\Script1.SQL" -h -1 -s "," > .\logs\%%a_Script1.log
for /f %%a IN (DBList.txt) do sqlcmd -E -S MyServer -d %%a -i ".\scripts\Script2.SQL" -h -1 -s "," > .\logs\%%a_Script2.log
for /f %%a IN (DBList.txt) do sqlcmd -E -S MyServer -d %%a -i ".\scripts\Script3.SQL" -h -1 -s "," > .\logs\%%a_Script3.log
for /f %%a IN (DBList.txt) do sqlcmd -E -S MyServer -d %%a -i ".\scripts\Script4.SQL" -h -1 -s "," > .\logs\%%a_Script4.log
for /f %%a IN (DBList.txt) do sqlcmd -E -S MyServer -d %%a -i ".\scripts\Script5.SQL" -h -1 -s "," > .\logs\%%a_Script5.log

MCITP SQL 2005, MCSA SQL 2012

JakDBA SSCommitted Points: 1610 More actions · Answer 6

sqlguy80 - Tuesday, July 25, 2017 12:37 PM
Hi ,
Can any one suggest an efficient way where we can apply about 10 T-SQL scripts on about 200 databases and show the management a log file that the scripts were applied correctly?
We cannot use any third party tools.
Thanks,

Use sp_MSforeachdb if they are on the same database server.

@JayMunnangi