The Multifunctioning DBA

Apr 28 2009   10:15PM GMT

SQL Version Script



Posted by: Colin Smith
Tags:
Database
Database Administration
MS SQL
SQL Server

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’


 Comment on this Post

 
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 other members comment.

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: