systemgruppen
asked on
Powershell get db from list
Hi
I would like to have a powershell script, the takes the contest(SQL server names) of an csv file, and then list all the databases in an output csv file, but I cant get it to work.
The script I use is this one:
"Import-Module sqlps –DisableNameChecking
[System.Reflection.Assembl y]::LoadWi thPartialN ame("Micro soft.SqlSe rver.SMO") | out-null
#Import CSV
$servers = Import-Csv -Path "C:\servers.csv"
foreach ($sv in $servers)
{
$SMOserver = New-Object ('Microsoft.SqlServer.Mana gement.Smo .Server') -argumentlist $servers
$SMOserver.Databases | select $servers,Name | Format-Table > c:\sql_db.txt
}"
Thanks in advanced.
I would like to have a powershell script, the takes the contest(SQL server names) of an csv file, and then list all the databases in an output csv file, but I cant get it to work.
The script I use is this one:
"Import-Module sqlps –DisableNameChecking
[System.Reflection.Assembl
#Import CSV
$servers = Import-Csv -Path "C:\servers.csv"
foreach ($sv in $servers)
{
$SMOserver = New-Object ('Microsoft.SqlServer.Mana
$SMOserver.Databases | select $servers,Name | Format-Table > c:\sql_db.txt
}"
Thanks in advanced.
ASKER
Nothing happens - no errors and no c:\sql_db.txt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you do have server and instance name in a csv and need to use smo:
import-csv file.csv | % {
$sqlsrv = $_.server; $inst = $_.instance
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "$sqlsrv\$inst"
foreach($sqlDatabase in $sqlServer.databases) {$sqlDatabase.name | out-file c:\$sqlsrv .txt -append}
}
ASKER
Hi oDbA
The script works very fine - so thank you.
Its from a csv file, with one server per line, and I've changed the output to a csv file.
Is it possible to insert an empty line between the servers and is it also possible to put the servername in one column and the databasename in the next?
Thanx in advanced
The script works very fine - so thank you.
Its from a csv file, with one server per line, and I've changed the output to a csv file.
Is it possible to insert an empty line between the servers and is it also possible to put the servername in one column and the databasename in the next?
Thanx in advanced
The question about the servers.csv was whether it is a real csv, that is, content lines with several columns separated by a delimiter, or a simple text file with (only) one server per line.
If the former: does the file contain a header line, and if not, please specify how many columns there are and which one contains the server name (or post a short sample).
Since the function returns an array of custom objects, a simple Export-Csv should create separate columns.
This now adds an empty line after each name list (and assumes a simple text file for the server names):
If the former: does the file contain a header line, and if not, please specify how many columns there are and which one contains the server name (or post a short sample).
Since the function returns an array of custom objects, a simple Export-Csv should create separate columns.
This now adds an empty line after each name list (and assumes a simple text file for the server names):
Function Get-SqlDatabases {
[CmdletBinding()]
Param(
[Parameter(ValueFromPipeline=$True, ValueFromPipelineByPropertyName=$True, Position=0)]
[string]$Server
)
Process {
Try {
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $($Server); Database = master; Initial Catalog = master; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select name from sys.databases"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$RowCount = $SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
If ($RowCount -gt 0) {
$DataSet.Tables[0].Rows | Select-Object -Property `
@{Name="Server"; Expression={$Server}},
@{Name="Database"; Expression={$_.Name}} | Write-Output
}
} Catch {
If (-Not ($ErrorActionPreference -eq [System.Management.Automation.ActionPreference]::SilentlyContinue)) {
$_.Exception.ErrorRecord | Write-Error
}
}
}
}
Get-Content C:\Servers.csv | Get-SqlDatabases | % {
$_; "" | Select-Object -Property @{Name="Server"; Expression={""}}, @{Name="Database"; Expression={""}}
} | Export-Csv "C:\sql_db.csv" -NoTypeInformation
ASKER
Is is just a plain csv file with no header - you can see the attached example, and below here is the output when I run the script:
Server,"Database"
SERVER1,"master"
,""
SERVER1,"tempdb"
,""
SERVER1,"model"
,""
SERVER1,"msdb"
,""
SERVER1,"ReportServer"
,""
SERVER1,"ReportServerTempD B"
Doc1.docx
Server,"Database"
SERVER1,"master"
,""
SERVER1,"tempdb"
,""
SERVER1,"model"
,""
SERVER1,"msdb"
,""
SERVER1,"ReportServer"
,""
SERVER1,"ReportServerTempD
Doc1.docx
Sorry, this should work as advertised now:
Function Get-SqlDatabases {
[CmdletBinding()]
Param(
[Parameter(ValueFromPipeline=$True, ValueFromPipelineByPropertyName=$True, Position=0)]
[string]$Server
)
Process {
Try {
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $($Server); Database = master; Initial Catalog = master; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select name from sys.databases"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$RowCount = $SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
If ($RowCount -gt 0) {
$DataSet.Tables[0].Rows | Select-Object -Property `
@{Name="Server"; Expression={$Server}},
@{Name="Database"; Expression={$_.Name}} | Write-Output
}
} Catch {
If (-Not ($ErrorActionPreference -eq [System.Management.Automation.ActionPreference]::SilentlyContinue)) {
$_.Exception.ErrorRecord | Write-Error
}
}
}
}
Get-Content C:\Servers.csv | % {
Get-SqlDatabases $_; "" | Select-Object -Property @{Name="Server"; Expression={""}}, @{Name="Database"; Expression={""}}
} | Export-Csv "C:\sql_db.csv" -NoTypeInformation
ASKER
The output is like this now:
Server,"Database"
SERVER1,"master"
SERVER1,"tempdb"
SERVER1,"model"
,""
SERVER2,"master"
SERVER2,"tempdb"
SERVER2,"model"
SERVER2,"msdb"
Server,"Database"
SERVER1,"master"
SERVER1,"tempdb"
SERVER1,"model"
,""
SERVER2,"master"
SERVER2,"tempdb"
SERVER2,"model"
SERVER2,"msdb"
Well, that's what you requested, isn't it? (Though there should be quotes around the server names as well, but I'm assuming that these went away when you anonymized the server names.)
Is it possible to insert an empty line between the servers and is it also possible to put the servername in one column and the databasename in the next?
ASKER
I did not delete anything besides the servernames - not any of the quotes, so the output is as the one I pasted in.
First I would have to look at smo though there are other options.
Second you keep overwriting the file so only the last DB info will be present.
| Format-Table > c:\sql_db.txt
needs to be changed to
| Format-Table >> c:\sql_db.txt