This post is part 2 of the Powershell for DBA – Working with INI Files and describes a simple method and then a more complex method to exclude databases from subsequent processing within a Powershell script.

Simple Method

The simple method requires you to pass in a string parameter that has a list of comma delimited database names to be excluded from processing. The list is then compared to each database name retrieved from an ADO.NET query. In the example below, we are retrieving the database name and status for each database and comparing it to the database exclude list parameter. I could have altered the SQL query text to exclude the database directly in the query but I wanted to show you how to do the exclude in Powershell plus modifying the SQL query text may introduce string size issues if you have a lot of databases to exclude.

The PS statement below performs the filtering out of excluded databases.

if ($ExcludeList -notcontains $DbName)

Complex Method

This method uses a Compare-Array function to produce an Array of Database Names that is the result of Array 1 (All DBs) minus Array 2 (Exclude DBs)

The resultant Database Name Array is then used for subsequent processing, the same technique can be used for excluding tables or other named objects accessible from Powershell.

The Compare-Array function looks like below.

First we retrieve the Exclude list of comma delimited database names from the ServerExcludeDBs field in an INI file (see post Powershell for DBA – Working with INI files on how to use INI files)

[string[]] $ExcludeDBsarray = $ServerExcludeDBs.split(“,”)

Next let’s get a list of databases on the SQL Server.

$dbs = $SQLConn.Databases

Put the databases in a string array, in this case I only want the accessible database that are not Read Only.

$DBAccessibleListArray = $dbs | Where-Object { $_.IsAccessible -eq $True -and $_.DatabaseOptions.ReadOnly -eq $false}

Next I compare the DB Accessible list array with the Exclude DB array

[Object] $DBToProcessList = Compare-Arrays   $DBAccessibleListArray   $ExcludeDbsArray

The $DBToProcessList.Left statement then returns the result of $DBAccessibleListArray minus $ExcludeDbsArray. The below Return statement returns the resultant array from within a PS function.

return $DBToProcessList.left

If you want to return only the items that matched in value between Array 1 and Array 2 then you could run this statement below i.e. return items in Array 2 that are included in Array 1.

return $DBToProcessList.equal

Now his method comes into play where you want handle a restart process for PS script. For example as you process each database you would write the database name to a Processed DB text file. On a restart of the PS script (in the case of a failure) rather than processing all databases again you would read in the Processed DB text file and combine it with the Excluded DB array so that you can generate the “Databases To Process” array to continue the processing (loop through) from the last processed database.

You download the Compare-Array function (here)