Syntax for altering all databases?

Question

Syntax for altering all databases?

cstewart-1126229

SSC Journeyman

Points: 79
More actions
August 14, 2009 at 1:24 pm

#208581

I have a SQL 2005 box that I'd like to convert all recovery models to SIMPLE.
ALTER DATABASE (?all?) SET RECOVERY SIMPLE GO

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

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

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

For quick and dirty stuff like that, GOOGLE the undocumented sp_MSForEachDB extended stored procedure. It's got a nasty cursor in it but it is convenient for this type of stuff.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

Just generate that statement for all the databases on the server.

select 'ALTER DATABASE '+name +' SET RECOVERY SIMPLE GO'

from sys.databases

You may want to filter out master, tempdb, etc.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

Bob's method eliminates the use of a cursor. If you need to "productionalize" something like that, we can do a little XML concatenation into a variable using his good code and then execute the variable.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cstewart-1126229 SSC Journeyman Points: 79 More actions · Answer 4

Understood. Thanks for the great responses. Actually, I've found some nifty uses after googling sproc sp_MSForEachDB.

Thanks again guys!

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

You're welcome, and good luck.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills