internal/functions/Get-AllDatabaseInfo.ps1
|
function Get-AllDatabaseInfo { # Using the unique tags gather the information required Param($Instance, $Tags) #clear out the default initialised fields $Instance.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Server], $false) $Instance.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Database], $false) $Instance.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Login], $false) $Instance.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Agent.Job], $false) $Instance.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.StoredProcedure], $false) $Instance.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Information], $false) $Instance.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Settings], $false) $Instance.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.LogFile], $false) $Instance.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.DataFile], $false) # set the default init fields for all the tags # Server Initial fields $ServerInitFields = $Instance.GetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Server]) $ServerInitFields.Add("VersionMajor") | Out-Null # so we can check versions $Instance.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Server], $ServerInitFields) # Database Initial Fields $DatabaseInitFields = $Instance.GetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Database]) # Stored Procedure Initial Fields $StoredProcedureInitFields = $Instance.GetDefaultInitFields([Microsoft.SqlServer.Management.Smo.StoredProcedure]) # Information Initial Fields # Settings Initial Fields $SettingsInitFields = $Instance.GetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Settings]) # Login Initial Fields $LoginInitFields = $Instance.GetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Login]) # Log File Initial Fields $LogFileInitFields = $Instance.GetDefaultInitFields([Microsoft.SqlServer.Management.Smo.LogFile]) # Data File Initial Fields $DataFileInitFields = $Instance.GetDefaultInitFields([Microsoft.SqlServer.Management.Smo.DataFile]) # Configuration cannot have default init fields :-) $configurations = $false # Set up blank ConfigValues object for any config we need to use in the checks $ConfigValues = [PSCustomObject]@{} # Using there so that if the instance is not contactable, no point carrying on with gathering more information switch ($tags) { 'AsymmetricKeySize' { $asymmetrickey = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'asymmetrickeysizeexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.asymmetrickeysize.excludedb').Value } 'AutoClose' { $autoclose = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'autoclose' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.database.autoclose').Value $ConfigValues | Add-Member -MemberType NoteProperty -Name 'autocloseexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.autoclose.excludedb').Value } 'AutoShrink' { $autoshrink = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'autoshrink' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.database.autoshrink').Value $ConfigValues | Add-Member -MemberType NoteProperty -Name 'autoshrinkexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.autoshrinke.excludedb').Value } 'ValidDatabaseOwner' { $owner = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'validdbownername' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.validdbowner.name').Value $ConfigValues | Add-Member -MemberType NoteProperty -Name 'validdbownerexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.validdbowner.excludedb').Value } 'InvalidDatabaseOwner' { $owner = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'invaliddbownername' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.invaliddbowner.name').Value $ConfigValues | Add-Member -MemberType NoteProperty -Name 'invaliddbownerexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.invaliddbowner.excludedb').Value } 'DatabaseCollation' { $collation = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'wrongcollation' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.database.wrongcollation').Value } 'SuspectPage' { $suspectPage = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'suspectpageexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.suspectpage.excludedb').Value } 'VirtualLogFile' { $vlf = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'maxvlf' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.database.maxvlf').Value $ConfigValues | Add-Member -MemberType NoteProperty -Name 'vlfexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.vlf.excludedb').Value } 'LogFileCount' { $logfilecount = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'logfilecount' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.database.logfilecount').Value $ConfigValues | Add-Member -MemberType NoteProperty -Name 'logfilecountexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.logfilecount.excludedb').Value } 'AutoCreateStatistics' { $autocreatestats = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'autocreatestats' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.database.autocreatestatistics').Value $ConfigValues | Add-Member -MemberType NoteProperty -Name 'autocreatestatsexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.autocreatestats.excludedb').Value } 'AutoUpdateStatistics' { $autoupdatestats = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'autoupdatestats' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.database.autoupdatestatistics').Value $ConfigValues | Add-Member -MemberType NoteProperty -Name 'autoupdatestatsexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.autoupdatestats.excludedb').Value } 'AutoUpdateStatisticsAsynchronously' { $autoupdatestatsasync = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'autoupdatestatsasync' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.database.autoupdatestatisticsasynchronously').Value $ConfigValues | Add-Member -MemberType NoteProperty -Name 'autoupdatestatsasyncexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.autoupdatestatisticsasynchronously.excludedb').Value } 'Trustworthy' { $trustworthy = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'trustworthyexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.database.trustworthyexcludedb').Value } 'DatabaseStatus' { $status = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'excludereadonly' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.database.status.excludereadonly').Value $ConfigValues | Add-Member -MemberType NoteProperty -Name 'excludeoffline' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.database.status.excludeoffline').Value $ConfigValues | Add-Member -MemberType NoteProperty -Name 'excluderestoring' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.database.status.excluderestoring').Value $ConfigValues | Add-Member -MemberType NoteProperty -Name 'statusexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.database.statusexcludedb').Value } 'QueryStoreEnabled' { $qs = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'qsenabledexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'database.querystoreenabled.excludedb').Value } 'QueryStoreDisabled' { $qs = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'qsdisabledexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'database.querystoredisabled.excludedb').Value } 'CompatibilityLevel' { $compatibilityLevel = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'compatexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'database.compatibilitylevel.excludedb').Value } 'GuestUserConnect' { $guestUserConnect = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'guestuserexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'database.guestuser.excludedb').Value } 'RecoveryModel' { $recoverymodel = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'recoverymodeltype' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.recoverymodel.type').Value $ConfigValues | Add-Member -MemberType NoteProperty -Name 'recoverymodelexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.recoverymodel.excludedb').Value } 'PseudoSimple' { $pseudoSimple = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'pseudosimpleexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.database.pseudosimpleexcludedb').Value } 'ContainedDBAutoClose' { $containedDbAutoClose = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'contdbautocloseexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.database.contdbautocloseexclude').Value } 'ContainedDBSQLAuth' { $containedDbSqlAuthUsers = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'contdbsqlauthexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.database.contdbsqlauthexclude').Value } 'PageVerify' { $pageverify = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'pageverifyexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.database.contdbsqlauthexclude').Value $ConfigValues | Add-Member -MemberType NoteProperty -Name 'pageverify' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.pageverify').Value } 'FKCKTrusted' { $trusted = $true $ConfigValues | Add-Member -MemberType NoteProperty -Name 'fkcktrustedexclude' -Value ($__dbcconfig | Where-Object Name -EQ 'policy.database.fkcktrustedexclude').Value } Default { } } #build the object $testInstanceObject = [PSCustomObject]@{ ComputerName = $Instance.ComputerName InstanceName = $Instance.DbaInstanceName Name = $Instance.Name ConfigValues = $ConfigValues MajorVersion = $Instance.VersionMajor Databases = $Instance.Databases.Foreach{ [PSCustomObject]@{ Name = $psitem.Name SqlInstance = $Instance.Name Owner = @(if ($owner) { $psitem.owner }) ServerCollation = @(if ($collation) { $Instance.collation }) Collation = @(if ($collation) { $psitem.collation }) SuspectPage = @(if ($suspectPage) { (Get-DbaSuspectPage -SqlInstance $Instance -Database $psitem.Name | Measure-Object).Count }) ConfigValues = $ConfigValues AsymmetricKeySize = @(if ($asymmetrickey) { ($psitem.AsymmetricKeys | Where-Object { $_.KeyLength -lt 2048 } | Measure-Object).Count }) AutoClose = @(if ($autoclose -or $containedDbAutoClose) { $psitem.AutoClose }) AutoCreateStatistics = @(if ($autocreatestats) { $psitem.AutoCreateStatisticsEnabled }) AutoUpdateStatistics = @(if ($autoupdatestats) { $psitem.AutoUpdateStatisticsEnabled }) AutoUpdateStatisticsAsync = @(if ($autoupdatestatsasync) { $psitem.AutoUpdateStatisticsAsync }) AutoShrink = @(if ($autoshrink) { $psitem.AutoShrink }) VLF = @(if ($vlf) { ($psitem.Query("DBCC LOGINFO") | Measure-Object).Count }) LogFileCount = @(if ($logfilecount) { ($psitem.LogFiles | Measure-Object).Count }) Trustworthy = @(if ($trustworthy) { $psitem.Trustworthy }) Status = @(if ($status) { $psitem.Status }) IsDatabaseSnapshot = @(if ($status) { $psitem.IsDatabaseSnapshot }) # needed for status test Readonly = @(if ($status) { $psitem.Readonly }) # needed for status test QueryStore = @(if ($qs) { $psitem.QueryStoreOptions.ActualState }) CompatibilityLevel = @(if ($compatibilitylevel) { $psitem.CompatibilityLevel }) ServerLevel = @(if ($compatibilitylevel) { [Enum]::GetNames('Microsoft.SqlServer.Management.Smo.CompatibilityLevel').Where{ $psitem -match $Instance.VersionMajor } }) GuestUserConnect = @(if ($guestUserConnect) { if ($psitem.EnumDatabasePermissions('guest') | Where-Object { $_.PermissionState -eq 'Grant' -and $_.PermissionType.Connect }) { $true } } ) RecoveryModel = @(if ($pseudoSimple -or $recoverymodel) { $psitem.RecoveryModel }) PseudoSimple = @(if ($pseudoSimple) { '' -eq (($psitem.Query('Select last_log_backup_lsn from sys.database_recovery_status where database_id = DB_ID()')).last_log_backup_lsn) }) ContainmentType = @(if ($containedDbAutoClose -or $containedDbSqlAuthUsers) { $psitem.ContainmentType }) ContainedDbAutoClose = @(if ($containedDbAutoClose) { if (($psItem.ContainmentType -ne "NONE") -and ($null -ne $psItem.ContainmentType) -and $psitem.AutoClose) { $true } else { $false } } ) ContainedDbSqlAuthUsers = @(if ($containedDbSqlAuthUsers) { if ($psItem.ContainmentType -ne "NONE" -and ($null -ne $psItem.ContainmentType)) { ($psitem.Users | Where-Object { $_.LoginType -eq "SqlLogin" -and $_.HasDbAccess -eq $true } | Measure-Object ).Count } } ) PageVerify = @(if ($pageverify) { $psitem.PageVerify }) ForeignKeys = @(if ($trusted) {$psitem.Tables.ForeignKeys | Where-Object {-not $_.NotForReplication} | Select-Object Name, Parent, @{l='Database';e={$_.Parent.Parent.Name}}, IsChecked } ) Constraints = @(if ($trusted) {$psitem.Tables.Checks | Where-Object {(-not $_.NotForReplication) -and $_.IsEnabled} | Select-Object Name, Parent, @{l='Database';e={$_.Parent.Parent.Name}}, IsChecked } ) } } } return $testInstanceObject } |