how to deploy 10 T-SQL scripts across 200 databases

  • 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 don't have POSH script handy, but Powershell is the way to go IMO.

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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!

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

  • 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.

  • 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

  • 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.

  • 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

  • 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.

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

  • 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.

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

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