sql backup from list and exclude any OFFLINE databases

 

 i'm cleanup of old databases , marking them offline. want backup script exclude offline
databases. how add log shows dbs processed versus offline , not backed up. script
in using 2 databases 1 online , other offline testing.

 thanks.

$assemblyinfo = [system.reflection.assembly]::loadwithpartialname("microsoft.sqlserver.smo")  if ($assemblyinfo.getname().version.major -ge 10)  {  # sql server version 2008 or later, load these other assemblies  [system.reflection.assembly]::loadwithpartialname('microsoft.sqlserver.smoextended') | out-null  [system.reflection.assembly]::loadwithpartialname('microsoft.sqlserver.sqlwmimanagement') | out-null  }    $s = new-object ("microsoft.sqlserver.management.smo.server") "(local)" #this can "servername\instancename"    $bkdir = "c:\backup\" #we define folder path variable  $dbstobackup = @(“stagingdatabase","testing")     try      {    $dbs = $s.databases  foreach ($db in $dbs)  {  if($dbstobackup -contains $db.name)   {  $dbname = $db.name  $dt = get-date -format yyyymmddhhmm  $dbbackup = new-object ("microsoft.sqlserver.management.smo.backup")  $dbbackup.action = "database"  $dbbackup.database = $dbname  $dbbackup.devices.adddevice($bkdir + "\" + $dbname + "_db_" + $dt + ".bak", "file")    write-host $dbbackup.status  $dbbackup.sqlbackup($s)         }    }      }      catch [exception]      {        echo $_.exception|format-list -force        }

you have check masked value:

$srv.databases['issue'].status -band [microsoft.sqlserver.management.smo.databasestatus]::offline

or:

$srv.databases['issue'].status.hasflag([microsoft.sqlserver.management.smo.databasestatus]::offline)

the status "typeof" - [microsoft.sqlserver.management.smo.databasestatus]

if change status have perform refresh of database see it:

$srv.databases['issue'].refresh()

---

\_(ツ)_/

Windows Server  >  Windows PowerShell