Getting My List of Databases as an array?

Question

Post reply

Getting My List of Databases as an array?

Lowell

SSC Guru

Points: 323518
More actions
March 24, 2014 at 9:17 am

#289725

Fairly new to powershell, but not to programming.
I've got the script below, that i thought was going to create a array that i could lop through.
when i write out the contents, i'm getting the # of elements as an initial value, like below:
VERBOSE: Database Name: 3
VERBOSE: Database Name: CommonSecurity
VERBOSE: Database Name: dbWarden
VERBOSE: Database Name: ReportServerTempDB
would someone be kind enough to offer an example or point out what i'm doing wrong?
###################################################################################################
# All servers In SIMPLE Recovery, thus needing FULL/DIFF strategy.
###################################################################################################
function Get-DbListSimpleRecovery($svr)
{
$SqlQuery = "select [name] from master.sys.databases where recovery_model_desc = 'SIMPLE' and database_id > 4 order by [name]"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$results = @()
foreach ($Row in $DataSet.Tables[0].Rows)
{
$results += $Row.name;
}
return $results
}
#$WhichServer = "LOWELL-DEV"
#if blank, uses the default intance of the lcoal server
$dbs = Get-DbListSimpleRecovery($WhichServer)
foreach($db in $dbs)
{
write-verbose -Message "Database Name: $db" -verbose
}
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

bruce 1565 SSCrazy Points: 2574 More actions · Answer 1

Try this change in the function for the SqlAdapter line.

$SqlAdapter.Fill($DataSet) | Out-Null

Lowell SSC Guru Points: 323518 More actions · Answer 2

we that certainly was not obvious, but your solution was effective!

thanks!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

I have to sk... why are you using PoSH instad of just T-SQL for this? It's an easy task in T-SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 4

excellent question Jeff;

here's the reason, specifically:

Redgate SQL Backup v7.6 is not deleting backups the way i expect it to, and i'm running grievously short on disk space.

I'm setting the job via the SQL Backup GUI to delete files older than 7 days, but the files remain in place regardless.

I figured i'd play with creating a powershell that looks for a *.sqb backup file, matching a specific naming convention(ie contains the database name AND contains the word FULL AND contains a datestring greater than what was already moved to the backup /tape server...basically 7 days or older.

I figured i'd hit old backups with THAT hammer before the backup occurs as a step in a job, to indirectly guarantee i have some disk space for the backup.

also i'm changing the script to backup one database at a time, instead of in astring of backups, to better restart when a job might fail; instead of one step, for 5 databases, 5 steps, with each step backup un a specific database.