SQL Version Script

I needed a quick audit of some of the SQL Servers in my environment. We wanted to know what version and what SP each instance was running. I threw this together and it is pretty simple. It only checks for SQL Server 2000 SP4 and SQL Server 2005 RTM thru SP3.

New-PSDrive
i
-PSProvider
filesystem
-Root
Path
to
where
input
file
is

Clear-Content
“Path to results file”

$OUTFILE
=
path
to
outfile

$servers
=
Import-Csv
-Path
“i:\sqltab.txt”

$sqlquery1
=
“select @@version”

$sqlcatalog
=
“master”

foreach ($server
in
$servers)

{

$machine
=
$server.server

$instance
=
$server.instance

$torp
=
$server.torp

$ping
=
$server.ping

if ($instance
-eq
“NULL”)

{

$SqlServer
=
$server.server

$folder
=
$server.server

}

else

{

$SqlServer
=
“$machine\$instance”

$folder
=
“$machine-$instance”

}

$sqlcommandpath
=
“c:\Program Files\Microsoft SQL Server\90\Tools\binn\”

cd
$sqlcommandpath

$version
= ./sqlcmd.exe
-Q
“`”$sqlquery1`” -S $SqlServer -d $sqlcatalog -Y 255″

if ($version
-like
“*1399*”)

{$sqlversion
=
“SQL Server 2005 No Service Pack”}

if ($version
-like
“*2047*”)

{$sqlversion
=
“SQL Server 2005 SP1″}

if ($version
-like
“*3042*”)

{$sqlversion
=
“SQL Server 2005 SP2″}

if ($version
-like
“*4035*”)

{$sqlversion
=
“SQL Server 2005 SP3″}

if ($version
-like
“*2050*”)

{$sqlversion
=
“SQL Server 2000 SP4″}

echo
“`n######################################################################” >> $OUTFILE

echo
”         Server = $SqlServer                                ” >> $OUTFILE

echo
“            $sqlversion                                     ”>> $OUTFILE

echo
“######################################################################`n” >> $OUTFILE

If you have read my other posts then you know that sqltab is a csv file with all the information about my servers. If you have any questions about this or anything else head over to
http://sysadminsmith.com
and click on ‘Submit a Question’