functions/Get-DbaDatabase.ps1
|
FUNCTION Get-DbaDatabase { <# .SYNOPSIS Gets SQL Database information for each database that is present in the target instance(s) of SQL Server. .DESCRIPTION The Get-DbaDatabase command gets SQL database information for each database that is present in the target instance(s) of SQL Server. If the name of the database is provided, the command will return only the specific database information. .PARAMETER SqlInstance SQL Server name or SMO object representing the SQL Server to connect to. This can be a collection and recieve pipeline input to allow the function to be executed against multiple SQL Server instances. .PARAMETER SqlCredential PSCredential object to connect as. If not specified, current Windows login will be used. .PARAMETER NoUserDb Returns all SQL Server System databases from the SQL Server instance(s) executed against. .PARAMETER NoSystemDb Returns SQL Server user databases from the SQL Server instance(s) executed against. .PARAMETER Status Returns SQL Server databases in the status passed to the function. Could include Emergency, Online, Offline, Recovering, Restoring, Standby or Suspect statuses of databases from the SQL Server instance(s) executed against. .PARAMETER Access Returns SQL Server databases that are Read Only or all other Online databases from the SQL Server intance(s) executed against. .PARAMETER Owner Returns list of SQL Server databases owned by the specified logins .PARAMETER Encrypted Returns list of SQL Server databases that have TDE enabled from the SQL Server instance(s) executed against. .PARAMETER RecoveryModel Returns list of SQL Server databases in Full, Simple or Bulk Logged recovery models from the SQL Server instance(s) executed against. .PARAMETER NoFullBackup Returns databases without a full backup recorded by SQL Server. Will indicate those which only have CopyOnly full backups .PARAMETER NoFullBackupSince DateTime value. Returns list of SQL Server databases that haven't had a full backup since the passed iin DateTime .PARAMETER NoLogBackup Returns databases without a Log backup recorded by SQL Server. Will indicate those which only have CopyOnly Log backups .PARAMETER NoLogBackupSince DateTime value. Returns list of SQL Server databases that haven't had a Log backup since the passed iin DateTime .NOTES Author: Garry Bargsley (@gbargsley), http://blog.garrybargsley.com dbatools PowerShell module (https://dbatools.io, clemaire@gmail.com) Copyright (C) 2016 Chrissy LeMaire This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program. If not, see <http://www.gnu.org/licenses/>. You should have received a copy of the GNU General Public License along with this program. If not, see <http://www.gnu.org/licenses/>. .LINK https://dbatools.io/Get-DbaDatabase .EXAMPLE Get-DbaDatabase -SqlServer localhost Returns all databases on the local default SQL Server instance .EXAMPLE Get-DbaDatabase -SqlServer localhost -NoUserDb Returns only the system databases on the local default SQL Server instance .EXAMPLE Get-DbaDatabase -SqlServer localhost -NoSystemDb Returns only the user databases on the local default SQL Server instance .EXAMPLE 'localhost','sql2016' | Get-DbaDatabase Returns databases on multiple instances piped into the function #> [CmdletBinding(DefaultParameterSetName = "Default")] Param ( [parameter(Position = 0, Mandatory = $true, ValueFromPipeline = $True)] [Alias("ServerInstance", "SqlServer")] [object[]]$SqlInstance, [System.Management.Automation.PSCredential]$SqlCredential, [Alias("SystemDbOnly")] [parameter(ParameterSetName = "NoUserDb")] [switch]$NoUserDb, [Alias("UserDbOnly")] [parameter(ParameterSetName = "NoSystemDb")] [switch]$NoSystemDb, [parameter(ParameterSetName = "DbBackuOwner")] [string[]]$Owner, [parameter(ParameterSetName = "Encrypted")] [switch]$Encrypted, [parameter(ParameterSetName = "Status")] [ValidateSet('EmergencyMode', 'Normal', 'Offline', 'Recovering', 'Restoring', 'Standby', 'Suspect')] [string]$Status, [parameter(ParameterSetName = "Access")] [ValidateSet('ReadOnly', 'ReadWrite')] [string]$Access, [parameter(ParameterSetName = "RecoveryModel")] [ValidateSet('Full', 'Simple', 'BulkLogged')] [string]$RecoveryModel, [switch]$NoFullBackup, [datetime]$NoFullBackupSince, [switch]$NoLogBackup, [datetime]$NoLogBackupSince ) DynamicParam { if ($SqlInstance) { return Get-ParamSqlDatabases -SqlServer $SqlInstance[0] -SqlCredential $SqlCredential } } BEGIN { $databases = $psboundparameters.Databases $exclude = $psboundparameters.Exclude if ($NoUserDb -and $NoSystemDb) { Write-Warning "You cannot specify both NoUserDb and NoSystemDb" continue } } PROCESS { foreach ($instance in $SqlInstance) { try { $server = Connect-SqlServer -SqlServer $instance -SqlCredential $sqlcredential } catch { Write-Warning "Failed to connect to: $instance" continue } if ($NoUserDb) { $inputobject = $server.Databases | Where-Object { $_.IsSystemObject } } if ($NoSystemDb) { $inputobject = $server.Databases | Where-Object { $_.IsSystemObject -eq $false } } if ($databases) { $inputobject = $server.Databases | Where-Object { $_.Name -in $databases } } if ($status) { $inputobject = $server.Databases | Where-Object { $_.Status -eq $status } } if ($Owner) { $inputobject = $server.Databases | Where-Object { $_.Owner -in $Owner } } switch ($Access) { "ReadOnly" { $inputobject = $server.Databases | Where-Object { $_.ReadOnly } } "ReadWrite" { $inputobject = $server.Databases | Where-Object { $_.ReadOnly -eq $false } } } if ($Encrypted) { $inputobject = $server.Databases | Where-Object { $_.EncryptionEnabled } } if ($RecoveryModel) { $inputobject = $server.Databases | Where-Object { $_.RecoveryModel -eq $RecoveryModel } } # I forgot the pretty way to do this if (!$NoUserDb -and !$NoSystemDb -and !$databases -and !$status -and !$Owner -and !$Access -and !$Encrypted -and !$RecoveryModel) { $inputobject = $server.Databases } if ($exclude) { $inputobject = $inputobject | Where-Object {$_.Name -notin $exclude } } if ($NoFullBackup -or $NoFullBackupSince) { if($NoFullBackup) { $dabs = (Get-DbaBackuphistory -SqlServer $server -LastFull -IgnoreCopyOnly).Database } else { $dabs = (Get-DbaBackuphistory -SqlServer $server -LastFull -IgnoreCopyOnly -Since $NoFullBackupSince).Database } $inputobject = $inputObject | where-object {$_.name -notin $dabs -and $_.name -ne 'tempdb'} } if ($NoLogBackup -or $NoLogBackupSince) { if($NoLogBackup) { $dabs = (Get-DbaBackuphistory -SqlServer $server -LastLog -IgnoreCopyOnly).Database } else { $dabs = (Get-DbaBackuphistory -SqlServer $server -LastLog -IgnoreCopyOnly -Since $NoLogBackupSince).Database } $inputobject = $inputObject | where-object {$_.name -notin $dabs -and $_.name -ne 'tempdb' -and $_.RecoveryModel -ne 'simple'} } if ($null -ne $NoFullBackupSince) { $inputobject = $inputobject | Where-Object {$_.LastBackupdate -lt $NoFullBackupSince} } elseif ($null -ne $NoLogBackupSince) { $inputobject = $inputobject | Where-Object {$_.LastBackupdate -lt $NoLogBackupSince} } $defaults = 'ComputerName', 'InstanceName', 'SqlInstance','Name', 'Status', 'RecoveryModel', 'CompatibilityLevel as Compatibility', 'Collation', 'Owner', 'LastBackupDate as LastFullBackup', 'LastDifferentialBackupDate as LastDiffBackup', 'LastLogBackupDate as LastLogBackup' if ($NoFullBackup -or $NoFullBackupSince -or $NoLogBackup -or $NoLogBackupSince) { $defaults += ('Notes') } foreach ($db in $inputobject) { $Notes = $null if ($NoFullBackup -or $NoFullBackupSince) { if (@($db.EnumBackupSets()).count -eq @($db.EnumBackupSets() | Where-Object{$_.IsCopyOnly}).count -and (@($db.EnumBackupSets()).count -gt 0) ) { $Notes = "Only CopyOnly backups" } } Add-Member -InputObject $db -MemberType NoteProperty BackupStatus -value $Notes Add-Member -InputObject $db -MemberType NoteProperty ComputerName -value $server.NetName Add-Member -InputObject $db -MemberType NoteProperty InstanceName -value $server.ServiceName Add-Member -InputObject $db -MemberType NoteProperty SqlInstance -value $server.DomainInstanceName Select-DefaultView -InputObject $db -Property $defaults } } } } |