Changing Recovery Model

Question

Changing Recovery Model

apat

SSCertifiable

Points: 5929
More actions
April 28, 2010 at 11:57 am

#218334

Hi All,
I have 200 databases residing on one Server. How can I change all the databases to the same recover model in one shot? let's say I want to change them all to Full recover model, what is the easiest and fastest way?
Thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

explore the use of sp_MSforeachdb

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Don't forget to set up log backups for all of the databases if you switch them to full recovery.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

george sibbald SSC Guru Points: 104210 More actions · Answer 3

Run the following and then cut and paste the results into another window and run it

select 'alter database ['+name+'] set recovery full' from sys.sysdatabses where dbid > 4

---------------------------------------------------------------------

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

This is where powershell comes in very handy. Right-click on the database node in object explorer and open powershell - once there you can issue the following:

PS> Get-ChildItem | % {$_.RecoveryModel = 3; $_.Alter()}

The above will loop through each database and change the recovery model to simple. To change to full, use 1 - bulklogged, use 2.

Also take note of the previous responses. If you change to full, you need to perform a full backup and then implement frequent transaction log backups.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs