Change the Recovery Model of a SQL Server database with the PowerShell SQL PSProvider

I recently set out to change the recovery model of a SQL Server database with PowerShell. There seems to be lots of information available on how to accomplish this task with PowerShell through SMO (SQL Server Management Objects) and using T-SQL wrapped inside the Invoke-Sqlcmd cmdlet. I even found lots of information about how to view the recovery model with the PowerShell SQL Server PSProvider, but when it came to actually changing the recovery model via the PSProvider, there was little if any information about how to accomplish that task.

The workstation used in this blog article is running Windows 8.1 with PowerShell version 4 which is the default version of PowerShell that ships with Windows 8.1. It has the SQL 2014 Management Tools installed. The server which is named SQL02 is running Windows Server 2012 R2, PowerShell version 4 is also the default version that ships with that operating system. The server is running SQL Server 2008 R2.

SQL Server 2012 and higher uses a PowerShell module and since I have the SQL 2014 management tools installed on the workstation, it has the SQLPS module installed. I’ll need to explicitly import the SQLPS module since the module auto-loading functionality that was introduced in PowerShell version 3 doesn’t work with providers (it doesn’t auto-load the module when I try to access the SQL PSDrive, although if I used one of the cmdlets in the module first, the SQL PSDrive would then be available automatically):


Notice the warning about unapproved verbs in the previous output. There are two cmdlets in the SQLPS module that were created back in the snap-in days (Encode-SqlName and Decode-SqlName). I honestly don’t know why they don’t fix this since all they need to do is change to approved verbs and create aliases for the old names for backwards compatibility.

Now to query the settings for the ProGet database on SQL02 using the SQL PSProvider:


As you can see in the previous results, the recovery model of the ProGet database is Full.

Setting the recovery model of a database to Simple is literally simple using the PSProvider:


The one-liner shown in the previous example is written in such a way that more than one database could be changed by simply modifying the Where-Object portion of the command. Notice that the actual word ‘Simple’ was used to change the recovery model. It’s necessary to call the Alter method to actually apply the changes. The Refresh method is also used so that the changes show up in your current PowerShell session:


Now I’ll enter a one-to-one remoting session to SQL02 so the commands are actually executed as if I were logged directly into the SQL02 server:


Since SQL02 is running SQL Server 2008 R2, it uses a PowerShell snap-in instead of a module. Before I can use the SQL PSDrive, I’ll first need to load the SQL Provider snap-in:


A slightly modified version of the same command that I previously ran to check the recovery model of the ProGet database is now run directly on the server:


When I try to set the recovery model back to Full using the same syntax that I previously used, it generates an error:


Exception setting “RecoveryModel”: “Specified cast is not valid.”
At line:4 char:5
+ $_.RecoveryModel = ‘Full’
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], SetValueInvocationException
+ FullyQualifiedErrorId : ExceptionWhenSetting

When I first tried this, it was on a machine that was still running PowerShell version 2 so I thought that maybe it was a PowerShell version 2 related issue, but that’s not the case since this machine is running PowerShell version 4. It appears that the PowerShell snap-in that’s included with SQL Server 2008 R2 doesn’t have the enumeration to convert the database recovery model name to the corresponding number.

Taking a look at MSDN, you’ll find what numbers correspond to what database recover model names (1 = Full, 2 = Bulk Logged, and 3 = Simple).

Using the number instead of the name allows the command to complete successfully:


As you can see, the database recovery model has been changed back to Full:


Whether or not this is being accomplished directly on the server or from your workstation doesn’t matter, the version of the SQL management tools that is being used to manage the database settings is what matters and if you want to ensure backwards compatibility, use the number that corresponds to the database recovery model instead of the actual name.


1 Comment

  1. Allen White (@SQLRunr)

    The problem you had with SQL 2008 R2 existed in RTM, but was fixed in SP1, I think.


Leave a Reply

%d bloggers like this: