Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Powershell get db from list

Posted on 2014-11-25
11
Medium Priority
?
89 Views
Last Modified: 2014-12-03
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.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
#Import CSV
$servers = Import-Csv -Path "C:\servers.csv"
foreach ($sv in $servers)
{
$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $servers
$SMOserver.Databases | select $servers,Name | Format-Table > c:\sql_db.txt
}"  

Thanks in advanced.
0
Comment
Question by:systemgruppen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 29

Expert Comment

by:becraig
ID: 40464449
Do you get any output at all or are you facing a particular error ?

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
0
 

Author Comment

by:systemgruppen
ID: 40464498
Nothing happens - no errors and no c:\sql_db.txt
0
 
LVL 85

Accepted Solution

by:
oBdA earned 1500 total points
ID: 40464533
Is servers.csv really a csv, or just a file with one server name per line?
Try this; unlike SMO, this should work from any Windows client:
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:\Temp\Servers.csv | Get-SqlDatabases | Format-Table | Out-File C:\sql_db.txt

Open in new window

0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 29

Expert Comment

by:becraig
ID: 40464552
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}
}

Open in new window

0
 

Author Comment

by:systemgruppen
ID: 40466354
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
0
 
LVL 85

Expert Comment

by:oBdA
ID: 40466675
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):
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

Open in new window

0
 

Author Comment

by:systemgruppen
ID: 40468856
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,"ReportServerTempDB"
Doc1.docx
0
 
LVL 85

Expert Comment

by:oBdA
ID: 40468955
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

Open in new window

0
 

Author Comment

by:systemgruppen
ID: 40468989
The output is like this now:
Server,"Database"
SERVER1,"master"
SERVER1,"tempdb"
SERVER1,"model"
,""
SERVER2,"master"
SERVER2,"tempdb"
SERVER2,"model"
SERVER2,"msdb"
0
 
LVL 85

Expert Comment

by:oBdA
ID: 40468997
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?
0
 

Author Comment

by:systemgruppen
ID: 40470078
I did not delete anything besides the servernames - not any of the quotes, so the output is as the one I pasted in.
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question