Wednesday, September 9, 2009

Discover DEVELOPMENT databases set to FULL Recovery Model

Sometimes DBAs forget to set databases on DEVELOPMENT servers to SIMPLE recovery after a restore from PRODUCTION.

Here's a short PowerShell script that uses Sql Server Management Objects (SMO) to discover this condition.

Save it as get-full.ps1 on your machine and execute using the example code on Line 2.

Load a text file with your list of DEVELOPMENT servers like so...


Save the file on your machine to C:\Input\DevServers.

A csv file will be produced listing the server, database, and recovery model of the databases set to FULL recovery.

1:  # find databases in full recovery on DEVELOPMENT servers
2:  #.\get-full
4:  [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
6:  $start = get-date
7:  write-host "Start: " $start
9:  @(foreach ($svr in get-content "C:\Input\DevServers.txt")
10:  {
11:  $s = New-Object "Microsoft.SqlServer.Management.Smo.Server" $svr
12:  trap {$s; continue } $s.databases | ? {$_.RecoveryModel.ToString() -eq 'Full'} | select parent, name, recoverymodel
13:  }
14:  ) | export-csv -noType C:\Output\DEVELOPMENT_DBs_in_FULL_RECOVERY.csv
16:  $end = get-date
17:  write-host "End: " $end