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:angel: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'|||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|||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?|||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.|||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?|||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.|||I use sqlmaint.exe wrapped in a .cmd file|||I prefer DBArtisan for Oracle and SQL Server, just costs a boatload of $$$.|||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 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 ?|||Any Tutorials site you recommend?
It sounds bit advanced to me?
Thanks again guys.|||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|||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.