SMO, Powershell, & Failed Logins

Question

Background

• At my current organization, we've got a SQL Agent Job running a Powershell script, originally based on Script All Server Level Objects to Recreate SQL Server.
  • This job writes a bunch of scripts that can be used in the event we need to do disaster recovery.
  • Our version queries a separate database that lists out the servers that should be checked.
  • Prior to this job, I hadn't used Powershell though I've had other scripting experience (i.e. Python).
• We also have all of our servers setup, using the SQL Server Agent, to email the DBAs when we have failed logins.
• One of our development servers has a couple of databases on it that are taken offline for some unknown (to me) business purpose.
• When our SQL Agent Job runs, it always generates failed login alerts when it comes to those offline databases on that one development server. I've been asked to see if there's someway to have the script skip checking databases that are offline to prevent these false alerts.
• I've traced the source of the alerts back to the function below. I believe this bit, if($database.Status -eq 'Normal') was intended to make sure the database is online but it doesn't work.
• Based on some other reading online, I tried replacing if($database.Status -eq 'Normal') with if ($database.IsAccessible), but I still got the failed login alert email when running the script.

#Function to write out Database scripts
function ScriptOutDBObjects($serverObject, $objectType)
{
    foreach ($database in $srv.Databases)
    { 
        if($database.Status -eq 'Normal')
        {
            $objectPath = $scriptPath + $objectType +'\'
            if (!(Test-Path -Path $objectPath))
            {
                New-Item -ItemType Directory -Path $objectPath
            }
            $scriptingOptions.FileName = $objectPath + $database.Name.Replace(':','').Replace('\', '_') +'.sql'
            $database.Script($scriptingOptions)
        }
    }
}

• The job is using the Powershell type, though I get the same results when running the code from the Powershell ISE on my local computer testing things out.

Question

Will Microsoft.SqlServer.Management.Smo always try to open the database? If so, is there some flag or something I can pass it to make it not do so?

Answer 1

The answer to almost all questions about Powershell/SMO is to use SMOs object model less, and TSQL more. EG

$dbs = Invoke-Sqlcmd "select name from sys.databases where state_desc = 'ONLINE'" 

foreach ($db in $dbs)
{
  $dbName = $db.Name
  #. . . 
}

Answer 2

The best method of doing what you are doing is to use dbatools - its free, opensource, very well maintained and used at tons of places - so well tested as well.

For what you are doing if I understand the gist is - simplifying disaster recovery with dbatools

Come back with more questions if you have. Above will help you achieve your goal to script out entire configuration or selected stuff from your server for a DR scenario.

e.g

# This will write hashed passwords to disk
Export-DbaLogin -SqlInstance workstation\sql2016 -Path C:\temp\logins.sql | Invoke-Item