IF logic comparison with -iin or -icontains falls through

Question

I'll be the first to admit I'm a hack. My code looks terrible and I'm sure there are better ways. Please be gentle.

My goal is to create an automated script to do test restores of a database, and then run checks against said database. The first step is selecting a random 'user' database on the server.

I've tried setting both items as strings and even trimming the resulting database name, no luck. No matter what database is chosen, it falls thru the check. What am I doing wrong here guys?

Thanks

#Get a count on the number of databases on the server
$db_count = sqlcmd -S localhost -d MASTER -h -1 -Q "SET NOCOUNT ON Select count(*) from sysdatabases"
            
# get a random number between 4 and the number of databases (system db's number 1-4)
$rand_db_num = Get-Random -Minimum 4 -Maximum $db_count
"Our random number is $rand_db_num"

# don't want these databases to be chosen
[string]$data = 'HOLDING','DBA','SSISDB','ReportServer','SQLDBA'
            
# Getting the database name that matches the random number
[string]$db_name = sqlcmd -S localhost -d Master -h -1 -Q "SET NOCOUNT ON Select name from sysdatabases where dbid = $rand_db_num"

#$db_name = $db_name.Trim()

"Checking to see what the number $rand_db_num database is: $db_name"

            
If ($db_name -icontains $data){
            
         "chosen database name is $db_name"
                
         }ELSE {
            
            "Let's try this again"
         }

It's the other way around: If ($data contains $db_name){...} — Theo
Commented on my phone... contains should of course be -contains — Theo
Thanks for the input guys. I made the change and the logic still falls through. — Alonzo Hess
"the logic still falls through"??? ` 'DBA' -in 'HOLDING','DBA','SSISDB','ReportServer','SQLDBA' → True 'FOO' -in 'HOLDING','DBA','SSISDB','ReportServer','SQLDBA' → False — iRon

DBADon DBADon · Accepted Answer · 2022-07-28T15:18:55

You have a really good start on this but you are over complicating it. Databases are great at sorting data and giving you the results you want, so limit the databases directly in the query:

$query = "SET NOCOUNT ON SELECT name FROM sysdatabases WHERE name NOT IN ('master','tempdb','model','msdb','HOLDING','DBA','SSISDB','ReportServer','SQLDBA')"
$dbs = sqlcmd -S localhost -d MASTER -h -1 -Q $query

Moving that logic to the query really simplifies things by giving you just the results you want. So, the validation checks are no longer needed:

$db = Get-Random -InputObject $dbs -Count 1
$db

IF logic comparison with -iin or -icontains falls through

1 Answers