Powershell – Determine your Recovery Model in SQL Server 2008

Powershell

Well It’s not OFTEN I get to play with Powershell in SQL Server and I had an opportunity today.

I had to check on what Recovery Model a database was setup as.  There’s usually three, Simple, Full and Bulk-Logged.

I am not an SQL Server expert so I won’t go into the big details about what each model offers or compare the advantages and disadvantages of each.  Not my forte. 

This is just a simple one.  How do you QUICKLY tell what Recovery model is the database setup to use?

Within Powershell in SQL Server 2008 it’s a one liner.

GET-ITEM SQLSERVER:SQLSERVERNAMEINSTANCENAMEDatabasesDatabaseName | SELECT-OBJECT Recovery Model

That’s it!  Nothing more to it than that.  If you need to change this setting, in THEORY Powershell should be able to just “DO IT” but I recommend using ESTABLISHED METHODS when changing that SQL Database. 

For that Scenario just execute a T-SQL script like this

ALTER DATABASE Databasename

SET RECOVERY RecoveryModelType

(Where the RecoveryModelType is FULL, SIMPLE or BULK-LOGGED)

Cheers, The Power of Shell is in YOU

Sean
The Energized Tech

Leave a Reply