“Server=$SQLSERVER;Database=master;Integrated Security=True”
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = “select ‘Servername:
Step 2
Execute
the script shown below. [Refer 1.1]
./connect2 "HOME"
Note:
HOME is the server name. Please change it to your server name.
Result
Result
——
Version:Microsoft SQL Server 2000 – 8.00.2039 (Intel X86) …
Servername: HOME
Database: VixiaTrack
Database: vixiadata
Database: vixia
Database: TrackEquipment
Database: test
Database: tempdb
Database: Sales2
Database: Sales
Database: pubs
Database: Northwind
Database: msdb
Database: model
Database: master
Database: Legacy_Vixia
Database: abc3
Database: abc2
Database: abc
Fig 1.1
Step 3
Let us loop through a file and display it contents. Create the loop1.ps1
script shown below. [Refer Fig 1.2] Also, create the serverlist.txt file shown
below. [Refer Fig 1.3]
param (
[string] $filename
)
$computers = get-content $filename
foreach ($computer in $computers)
{
write-host $computer
}
Fig 1.3
Serverlist.txt
HOME
HOME\SQLEXPRESS
Fig 1.3
Let’s execute the loop1.ps1 script shown below. [Refer Fig 1.4]
./loop1 serverlist.txt
Fig 1.4
Step 4
Now let’s combine connect2.ps1 and loop1.ps1 to get the SQL Server
name, SQL Server version and all the databases on the server names listed in
serverlist.txt
Create connect3.ps1 as shown below. [Refer Fig 1.5]
param (
[string] $filename
)
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$computers = get-content $filename
foreach ($computer in $computers)
{
write-host “Details of the Server :” $computer
write-host “———————————–”$SqlConnection.ConnectionString = “Server=$computer;Database=master;Integrated Security=True”
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = “select ‘Servername: ‘+@@servername as Result union Select ‘Version: ‘+@@version as Result union select ‘Database:’
+name from sysdatabases as Result
order by Result desc ”
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
}
Fig 1.5
Step 5
Now, let’s execute the script shown below. [Refer Fig 1.6]
./connect3 serverlist.txt
Fig 1.6
Result:
Details of the Server : HOME
———————————–
19
Result
——
Version: Microsoft SQL Server 2000 – 8.00.2039 (Intel X86) …
Servername: HOME
Database: VixiaTrack
Database: vixiadata
Database: vixia
Database: TrackEquipment
Database: test
Database: tempdb
Database: Sales2
Database: Sales
Database: pubs
Database: Northwind
Database: msdb
Database: model
Database: master
Database: Legacy_Vixia
Database: abc3
Database: abc2
Database: abc
Details of the Server : HOME\SQLEXPRESS
———————————–
11
Version: Microsoft SQL Server 2005 – 9.00.2047.00 (Intel X86) …
Servername: HOME\SQLEXPRESS
Database: XMLTest
Database: VixiaTrack
Database: test
Database: tempdb
Database: msdb
Database: model
Database: master
Database: AdventureWorks
Database: admin
Now let’s
create a PowerShell script that does the same thing, using SQL Server SMO.
Step 1
Create
the connect4.ps1 PowerShell script shown below [Refer Fig 1.7]
param
(
[string] $filename
)
[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$computers = get-content $filename
foreach ($computer in $computers)
{
write-host “Details of the Server :” $computer
write-host “———————————–”
$Server = new-object (“Microsoft.SqlServer.Management.Smo.Server”) “$computer”
write-host “Server Version: ” $Server.Serverversion
write-host “Server Name: ” $Server.Information.VersionString
foreach($database in $Server.databases)
{
write-host “Database:” $database.name
}
}
Fig 1.7
Step2
Execute
the above created connect4.ps1 script, as shown below. [Refer Fig 1.8]
./connect4 serverlist.txt
Fig 1.8
Result
PS C:\ps> ./connect4 serverlist.txt
GAC Version Location
— ——- ——–
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\9…
Details of the Server : HOME
———————————–
Server Version:
Server Name: 8.00.2039
Database: abc
Database: abc2
Database: abc3
Database: Legacy_Vixia
Database: master
Database: model
Database: msdb
Database: Northwind
Database: pubs
Database: Sales
Database: Sales2
Database: tempdb
Database: test
Database: TrackEquipment
Database: vixia
Database: vixiadata
Database: VixiaTrack
Details of the Server : HOME\SQLEXPRESS
———————————–
Server Version:
Server Name: 9.00.2047.00
Database: admin
Database: AdventureWorks
Database: master
Database: model
Database: msdb
Database: tempdb
Database: test
Database: VixiaTrack
Database: XMLTest
Conclusion
Part IV
of this article illustrated how to use a PowerShell script to loop through the
content of a file and connect to different servers. It also illustrated how to
do the same using SQL Server SMO.
