Re: Finding and Identifing SQL Servers

Here's a bit of a hack to get you started. I've taken some of the
serverproperty values mentioned by Dimant and stuck them in a batch file that
accepts a servername. The batch file tries to open a trusted connection to
the server passed and dumps the results to {servername}.txt.

Please give this a good look and treat it as a demo of an approach that
might work for your circumstances.

From command line:
sqlcmd /L > servers.txt

Open servers.txt. Remove everything (including whitespace) so that only
server names exist.

In front of each server name add: call checksql.bat{space}

If servers.txt looks like this:
Servers:
DEV01
DEV01\SYSTEMCENTER
SQL01
SQL02

You should edit, so it looks like this:
call checksql.bat DEV01
call checksql.bat DEV01\SYSTEMCENTER
call checksql.bat SQL01
call checksql.bat SQL02

Now that the edit is complete, save servers.txt as queryservers.bat.

Next, save the following as checksql.bat. It needs to be in the same folder
you saved queryservers.bat. This is a one line batch file so whatever you're
editor (eg, notepad), make sure that Word Wrap or Wrap Lines is off.

sqlcmd -W -h-1 -o"%1.txt" -S%1 -E -Q"set nocount on; if charindex('sql
server 2005', @@version) > 0 begin select SERVERPROPERTY('ServerName') AS
[ServerName], replace(replace(@@version, char(13), ''), char(10), '') AS
[Server Information], SERVERPROPERTY('MachineName') AS [MachineName],
SERVERPROPERTY('InstanceName') AS [InstanceName],
SERVERPROPERTY('productversion') AS [ProductVersion],
SERVERPROPERTY('ProductLevel') AS [ProductLevel], SERVERPROPERTY('Edition')
AS [Edition], SERVERPROPERTY('Collation') AS [Collation],
SERVERPROPERTY('IsClustered') AS [IsClustered],
SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled],
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],
SERVERPROPERTY('LicenseType') AS [LicenseType], SERVERPROPERTY('NumLicenses')
AS [NumLicenses] end else begin select @@servername as [ServerName],
replace(replace(@@version, char(13), ''), char(10), '') as [Server
Information] ; end"

Finally, run queryservers.bat. You should end up with text files in the
directory from which you're running queryservers.bat.

Good luck! :)
.

Relevant Pages

  • Re: Finding and Identifing SQL Servers
    ... The batch file tries to open a trusted connection to ... the server passed and dumps the results to.txt. ... editor, make sure that Word Wrap or Wrap Lines is off. ... SERVERPROPERTY('productversion') AS, ...
    (microsoft.public.sqlserver.security)
  • Re: SPBackup utility failing
    ... generates a batch file that, when run on a server running Windows SharePoint ... backs up changed site collections using the Stsadm.exe tool. ... >> Mike Walsh, Helsinki, Finland ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: back not backing up
    ... and buy the best backup server software i can buy. ... run the batch file via the Task Scheduler. ... > lie in areas other than server management. ...
    (microsoft.public.windows.server.general)
  • RE: Windows 2003 Server Terminal Services
    ... I tried the first .bat idea, and though it worked, it left the batch file ... accessing applications which they should run. ... MCSE, CCEA, Microsoft MVP - Terminal Server ... Terminal Server role and the Terminal Services Licensing ...
    (microsoft.public.windows.terminal_services)
  • Re: A required privilege is not held by the client
    ... > I have a 2003 server that I am trying to backup with Veritas Backup Exec ... > and post backup commands within Veritas. ... which you run the batch file has insufficient privileges ...
    (microsoft.public.win2000.general)