sql-server-keeping-it-simple-default-to-simple-recovery-model

sql-server-keeping-it-simple-default-to-simple-recovery-model.ps1

Import-Module SQLPS -DisableNameChecking

#replace this with your instance name
$instanceName = "server\instance";
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName;
$server.ConnectionContext.ConnectTimeout = 2200;
$server.ConnectionContext.StatementTimeout=2200;

$databases = $server.Databases;

foreach ($database in $databases) {
    Try
    {
        if ($database.RecoveryModel -ne 'Simple')
        {
            $database.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple; 
            $database.Alter();
            #additonal cleanup
            #shrink the database, mimic SHRINKDATABASE, 15%
            $database.Shrink(15, [Microsoft.SqlServer.Management.Smo.ShrinkMethod]'NoTruncate');
            $database.Shrink(15, [Microsoft.SqlServer.Management.Smo.ShrinkMethod]'TruncateOnly');
            #truncate the log, 1MB
            foreach ($logFile in $database.LogFiles) {
                $logFile.Shrink(1,[Microsoft.SqlServer.Management.Smo.ShrinkMethod]'TruncateOnly');
            }
        }
    }
    Catch 
    { 
        $err = $_.Exception
        while ( $err.InnerException )
        {
            $err = $err.InnerException;
        };
        $exception = $err.Message;

        #Inform someone...
        Write-Host $exception;
    }
}