Solved

Powershell get db from list

Posted on 2014-11-25
11
79 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
  • 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 83

Accepted Solution

by:
oBdA earned 500 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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 83

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 83

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 83

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now