USING SQL & POWERSHELL – Different Methods to List Databases WITH Recovery Models

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

  1. InputFile – List contains all the servers
  2. Recoverymodel – type of the recovery model that you want to do a search
  3. 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  
    https://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:-
 
 

Advertisements

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to USING SQL & POWERSHELL – Different Methods to List Databases WITH Recovery Models

  1. aditya says:

    Hello sir,
    iam trying to use the SQL to get the db recovery model across multiple servers.I am not trying with power shell now.i have 5 servers in the server.txt file.once this is successful i will try for more servers. right now when i run
    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’

    i get the result for 5 servers as NULL.
    see below,i have just copied for 2 servers. do we need to enable cmd shell on all the servers in scope ??
    NULL
    C:\windows\system32>sqlcmd -S Instance1
    NULL
    C:\windows\system32>sqlcmd -S instance 2
    NULL

    • Hi Aditya,

      You don’t need to enable XP_CMDSHELL on all the servers. You just need to enable it on the source machine where you want to execute the SQL.

      Try to give everything in the same line
      Its a one line statement.
      If you break it to multiple lines it won’t return you anything.

      Prashanth

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s