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

20 pts.
Tags:
Database
Scripting
SQL Server
Stored Procedures
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

<pre>exec RemoteServer.master.dbo.sp_executesql ‘SELECT SERVERPROPERTY(”ProductVersion”)’</pre>

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

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following