USING POWERSHELL TO LIST SQLSERVER USER DATABASES WITH RECOVERY MODELS
One of op requested to list all the user defined databases where it has been configured with simple recovery model across ‘N’ servers.There are many ways to achieve the requirement.
You can query sys.databases with sqlcmd utility to list all the details
SQL
select name,recovery_model_desc from sys.databases where database_id > 4 and recovery_model_desc='SIMPLE'
SQL
Master..xp_cmdshell 'for /f %j in (c :\servers.txt ) do sqlcmd -S %j -Q "select name,recovery_model_desc from sys.databases where database_id > 4 and recovery_model_desc=''SIMPLE''" -E'
Download the code: -
Output:-
OR
Using Powershell
The function Get-RecoveryModel has three input parameters
- InputFile – List contains all the servers
- Recoverymodel – type of the recovery model that you want to do a search
- Database Flag – (TRUE OR FALSE – Include or exclude system databases )
PowerShell Script
<# .SYNOPSIS USING POWERSHELL TO LIST SQLSERVER USER DATABASES WITH SIMPLE RECOVERY .DESCRIPTION The function call requires three parameters Inputfile, RecoveryModel(Full,simple,bulklogged) and Database flag ($TRUE=System Database,$FALSE=User Database) .EXAMPLE 1.PS:\>Get-RecoveryModel -InputFile F:\PowerSQL\Server.txt -RecoveryModel "SIMPLE" -DatabaseFlag $FALSE 2.PS:\>Get-RecoveryModel -InputFile F:\PowerSQL\Server.txt -RecoveryModel "FULL" -DatabaseFlag $TRUE .NOTES Author : Powershellsql@gmail.com .LINK http://sqlpowershell.wordpress.com/ #> Function Get-RecoveryModel { param( $InputFile, $RecoveryModel, $DatabaseFlag ) [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null ForEach ($instance in Get-Content $InputFile) { $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance $dbs=$s.Databases| where {$_.RecoveryModel -like "$RecoveryModel" -and $_.IsSystemObject -eq $DatabaseFlag } $dbs | select @{Name="ServerName"; Expression = {$Instance}}, Name, RecoveryModel } }
Function call:-
PS:\>Get-RecoveryModel -InputFile F:\PowerSQL\Server.txt -RecoveryModel "SIMPLE" -DatabaseFlag $FALSE PS:\>Get-RecoveryModel -InputFile F:\PowerSQL\Server.txt -RecoveryModel "FULL" -DatabaseFlag $TRUE
Converting the output to HTML
$OutputFile = "F:\PowerSQL\RecoveryModel.htm" $ServerList = "F:\PowerSQL\Server.txt" $a = "" $a = $a + "BODY{background-color:peachpuff;}" $a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}" $a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}" $a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:palegoldenrod}" $a = $a + "" Function Get-RecoveryModel { param( $InputFile, $RecoveryModel, $DatabaseFlag ) [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null ForEach ($instance in Get-Content $InputFile) { $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance $dbs=$s.Databases| where {$_.RecoveryModel -like "$RecoveryModel" -and $_.IsSystemObject -eq $DatabaseFlag } $dbs | select @{Name="ServerName"; Expression = {$Instance}}, Name, RecoveryModel } } Get-RecoveryModel -InputFile $ServerList -RecoveryModel "SIMPLE" -DatabaseFlag $FALSE|ConvertTo-HTML -head $a -body "<H2>Database Recovery Model Information</H2>" | Out-File $OutputFile
Output:-
