Post reply

how to deploy 10 T-SQL scripts across 200 databases

  • July 25, 2017 at 12:37 pm

    #339128

    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,

  • July 25, 2017 at 12:50 pm

    #1952650

    I don't have POSH script handy, but Powershell is the way to go IMO.

  • July 25, 2017 at 12:54 pm

    #1952654

    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,

    SQLCMD would also do the job, I think.

    Maybe use Excel to generate all of the SQL statements to be executed, rather than manually typing/cutting/pasting.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • July 25, 2017 at 1:37 pm

    #1952674

    i run something like this that installs all my standard scripts on a given server.
    you are really asking how can i add an outer loop to this, so ii can do it on multipel servers + databases, ro the same server plus miltipel databases, right?

    #Set-ExecutionPolicy RemoteSigned
    foreach ($f in Get-ChildItem -path "F:\StandardTakeOver\" -Filter CUS_*.sql | sort-object)

    {

    $out = "J:\StandardTakeOver\OUTPUT\" + $f.name.split(".")[0] + ".txt" ;

    #invoke-sqlcmd -InputFile $f.fullname | format-table | out-file -filePath $out
    #Flags:
    # -S server\instancename
    # -E = trusted connection
    # -i input file
    # -I Enable Quoted Identifier: critical fro stored proc creations
    $f.name
    sqlcmd -S "(local)" -E -I -i $f.fullname | format-table #| out-file -filePath $out

    }

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 25, 2017 at 3:16 pm

    #1952695

    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,

    Are the databases all on the same machine?

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

  • July 28, 2017 at 6:54 pm

    #1953264

    Did you get what you needed from the answers?  Good question about the databases being on the same machine or instance too.  If on a different machine you could even use SSMS, but I like the idea of having some output to look at with the powershell or even sqlcmd.  

    If this is a one time event, then you could even do it manually.   If it is something that will happen with some frequency then go with powershell or the use of sqlcmd w/ a batch script.  Have some examples of both if needed.

  • July 28, 2017 at 9:00 pm

    #1953269

    Jeff Moden - Tuesday, July 25, 2017 3:16 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,

    Are the databases all on the same machine? They are on the same machine

  • July 28, 2017 at 9:58 pm

    #1953271

    Am curious as to whether this a one time event or not. Also wonder how you are going to decide which 200 databases to run the scripts against. Will it be all if the user databases or just some of them. You may need a filter to select which ones to use.

    I think the powershell script above is a good starting point.

  • July 28, 2017 at 10:17 pm

    #1953272

    nerdCat - Friday, July 28, 2017 9:58 PM

    Am curious as to whether this a one time event or not. Also wonder how you are going to decide which 200 databases to run the scripts against. Will it be all if the user databases or just some of them. You may need a filter to select which ones to use.I think the powershell script above is a good starting point.

    Hi,
    Its a recurring event where product scripts are needed to be applied on multiple databases in multiple environments

  • July 28, 2017 at 10:44 pm

    #1953273

    Cool. I am not home right now, but can help more if needed. Comments and questions above are all good.

    You can drive this from a table, excel spreadsheet, etc.

    The core of your script is going to be:

    For each script (or database)

    For each database (or script)

    run script and send output to a file or concatenate to file

    Powershell is pretty nifty. I've used spreadsheets and database tables to control items that would change.

    I think mgmt is asking for a good thing with the output.

  • July 29, 2017 at 8:48 am

    #1953282

    sqlguy80 - Friday, July 28, 2017 9:00 PM

    Jeff Moden - Tuesday, July 25, 2017 3:16 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,

    Are the databases all on the same machine? They are on the same machine

    Then you have no difficult hurdles.  Write your scripts, write a wrapper to step through the databases (you might want to create a special table to exclude databases and, of course, you won't be scripting to any of the system databases) and run through the scripts for each database with some indication of success, failure, and which database its being executed on and write that output to a table in a common table using 3 part naming.  Run the wrapper.  No need for PowerShell or anything else outside of T-SQL.

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

  • August 1, 2017 at 5:26 pm

    #1953687

    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.

  • August 2, 2017 at 8:42 am

    #1953785

    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?

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

  • August 2, 2017 at 10:17 am

    #1953814

    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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 2, 2017 at 5:57 pm

    #1953871

    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.

    True enough but that sounds like work.  Just create a script in the form of some dynamic SQL and loop through the databases with a USE command.

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

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

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