Solved

Powershell get db from list

Posted on 2014-11-25
11
85 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 84

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 84

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 84

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 84

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

792 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