Question

How can I collect database/server information across multiple MSSQL versions.

SQL Server, Scripting, Stored procedures, Database

I would like to collect database info(backups, databases, logins, etc) as well as server information ( MSSQL version, patch level, etc).
I would like to use one server to collect information from all of my servers.
I have MSSQL 7, 2000, & 2005 on Windows servers 2000 & 2003.

I am also new to MSSQL & would like to learn some scripting,
Is there a site/tutorial/documentation that I can use for learning?

Thanks in advance for any help,
Angie

Many people setup a single management server and consolidate this information into the server on a schedule (daily, hourly, weekly, etc depending on the information gathered).

The easiest way to do this is to setup your management server and setup linked servers from the management server to all your other servers. This will then allow you to access the tables on the remote systems to get there info.

All the information you are looking for can be found in the master or msdb databases. The database information and login information can be found in the master database. For SQL 7 and 2000 you'll want to look into the sysdatabases table. For SQL 2005 you'll want to look into the sys.databases DMV.

For the login information again you'll be looking in the master database. For SQL 7 and 2000 you'll want to look into the sysxlogins table. For SQL 2005 you'll want to look into the sys.server_principals DMV.

For the backup information you'll be looking in the msdb database. Depending on the data you are looking for you'll find it all in the tables which start with Backup in all the versions.

To get your build numbers from the remote machines you'll need to use dynamic SQL to call the SERVERPROPERTY system function. The below code will return the Product Version is the form of Major.Minor.Build from the remote system.

exec RemoteServer.master.dbo.sp_executesql 'SELECT SERVERPROPERTY(''ProductVersion'')'

If you want want to use linked servers to gather this information you could write a SSIS package which loops through a list of SQL Servers gathering this info and loading it into the management server. This however would end up being a more complex solution than the T/SQL solution.

What's the end result that you are looking for?

AngieJo:;The end result it to maintain a list of servers with version info, backup info, & contact info.
We have about 300 servers to administer.
As far as the scripting part, I want to be able to make the same update to a database that lives on 70 different servers (same gold database renamed & pushed to other servers).

Thanks for you input,
AngieJo