April 4, 2011 at 2:54 pm
I'm looking for a list of DBs across multiple servers and it seems that there should be a fairly easy way with a vbs script or similar to pass in a list of instances/servers and get out a list of dbs by server, but I can't seem to find anything.
Of course, I could do sp_databases on each SQL server, but that seems pretty unpleasant.
How do you guys track this? I'd really like to do this in some sort of fairly automated way so that I can look for the latest data, since the developers will likely move things around without telling me, and I don't want to go looking for a DB across dozens of servers each time I need to find it.
Thanks in advance.
April 4, 2011 at 4:06 pm
Personally what I would do is make a small db on all servers for your admin stuff. Then have a job that populates a table in this db with the database names each night, and marks them as new, or deleted. Not a hard script to write, but one that lets each server track its own databases.
Then you can easily pull everything into a central server from each other server and look for changes.
Here are a couple links that might help you:
http://sev17.com/2008/11/inventory-sql-server-databases-with-powershell/
http://www.mssqltips.com/tip.asp?tip=1759
http://www.sqlservercentral.com/articles/Administration/70129/
April 4, 2011 at 4:23 pm
If you don't know what the servers are in your environment, you can also start here to get info on doing a server discovery.
http://sqlvariant.com/wordpress/index.php/2010/11/finding-sql-servers-with-powershell-part-2/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2011 at 5:52 am
Take a look at the "Central Management Server" feature on SSMS 2008 and newer. It allows you to run a query on multiple servers at the same time (properly configured of course). It's under "Registered Servers" section.
April 5, 2011 at 11:44 am
Wow guys, I can't thank you enough. The 2nd link from Steve is exactly what I was hoping for.
April 5, 2011 at 11:49 am
Philip Millwood-419646 (4/5/2011)
Wow guys, I can't thank you enough. The 2nd link from Steve is exactly what I was hoping for.
Awesome - glad we could help.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply