Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2007
    Posts
    8

    Question Unanswered: Best MSSQL management tools

    Hi everyone.

    I manage 100+ databases spread across the country. When it comes to perform an update or offsite backup, it is a nightmare. I have to repeat the same code and the same process for 100+ times.

    Is there any tools other than MS Enterprise manager we can use to perform this type of maintenance works in a batch manner? For example, we place a update sql file within the program, and the program does the rest (of course, we predefine the login credential for each databases)

    Cheers~

    gmefmax

  2. #2
    Join Date
    Nov 2005
    Posts
    122
    You can use the osql command line tool for SQL Server 2000 or the sqlcmd command line tool for SQL Server 2005.

    Example:
    osql -S myservername -d myDBname -U myuser -P mypwd -Q "backup database myDBname to disk='g:\backup\myDBname070821.bak'

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    You could also write your own. Essentially a wrapper for osql that would allow you to select which instances and which scripts to apply (and also in which order).

    It's not impossible, but I admit that I'm not up to the challenge right now.

    Regards,

    hmscott
    Have you hugged your backup today?

  4. #4
    Join Date
    Aug 2007
    Posts
    8
    I talked to few guys and it seems I have write my own software in order to do this type of work. I just wondering how other people in the industry manage large number of databases across the country. Any Idea?

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I don't really manage servers, but when I do need to execute the same script against many different server/databases, I use sqlcmd combined with some batch files that make use of the FOR keyword, looping over the values in a .txt file, calling sqlcmd for each.

    basically you specify the server/db/credentials in an external .txt file and then loop over each value using FOR.

  6. #6
    Join Date
    Aug 2007
    Posts
    8
    Thanks Jezemine. I tried it and only works for SQL2005, a lot of databases I manage are still using version 7 (YES! It still using SQL7.) Any other ideas?

  7. #7
    Join Date
    Nov 2005
    Posts
    122
    Quote Originally Posted by gmefmax
    I tried it and only works for SQL2005, a lot of databases I manage are still using version 7 (YES! It still using SQL7.) Any other ideas?
    Then use osql.exe instead.

  8. #8
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I use sqlmaint.exe wrapped in a .cmd file

  9. #9
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I prefer DBArtisan for Oracle and SQL Server, just costs a boatload of $$$.

  10. #10
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    I actually wrote a script which reads from a table, all the server names\instances, and then loop through each server\instace, log into each one (with a service acocunt), and run whatever command you want.

  11. #11
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by Reghardt
    I actually wrote a script which reads from a table, all the server names\instances, and then loop through each server\instace, log into each one (with a service acocunt), and run whatever command you want.
    I've pretty much been able to do with DOS cmd scripting what I did with UNIX kshell, a bit more clumsy though.

    Anybody use PowerShell yet for scripting ?

  12. #12
    Join Date
    Aug 2007
    Posts
    8
    Any Tutorials site you recommend?
    It sounds bit advanced to me?
    Thanks again guys.

  13. #13
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by PMASchmed
    I've pretty much been able to do with DOS cmd scripting what I did with UNIX kshell, a bit more clumsy though.

    Anybody use PowerShell yet for scripting ?

    I keep meaning to get into powershell, but haven't yet.

    ps combined with SMO would be a nice combo:

    http://www.google.com/search?q=smo+powershell

  14. #14
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by gmefmax
    Any Tutorials site you recommend?
    It sounds bit advanced to me?
    Thanks again guys.
    I have a book called "Windows NT Shell Scripting" by Tim Hill published by New Riders, it's a bit old (1998) but it has very good examples that come in handy.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •