Change the SQL recovery model in bulk with the PowerShell

Sometimes you need to configure settings on your SQL server in bulk, like e.g. the recovery model. In my case, we needed to change all databases to the Full recovery model, due to the usage of Always-On. Scince SQL 2012 there is a great way to do this with the PowerShell.

First open a Powershell and see if the SQL PowerShell module is loaded.

Get-Module SQLPS

If it isn't loaded, do so by using

Import-Module SQLPS

For scripts you should check if the module is loaded before you import it like this:

if ((Get-Module "SQLPS" -ea silentlycontinue) -eq $null)
{
    Import-Module SQLPS -DisableNameChecking
}

Now you can change to the SQL server by simply entering SQLSERVER:

Use ls or dir to see what options you have

Change the 'directory' with cd SQL. This way you go to the SQL server configuration. Do so as well for the servers host name and the SQL server instance, until you you enter the databases directory. Here you'll find all the databases hosted by the SQl server instance you choose. Now we can run the following script to change the recovery model in bulk.

foreach ($db in ls) {
  if ($db.RecoveryModel -ne "Full") {
    $db.RecoveryModel = "Full"
    $db.Alter()
  }
}