Solved

Powershell get db from list

Posted on 2014-11-25
11
77 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 28

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 28

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

706 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