• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 514
  • Last Modified:

T-SQL script to convert all user DBs recovery model

Hello,

Does anyone have a t-sql script to convert all user databases recovery model? ( either way, Simple2Full or Full2Simple )
There is an article but it requires multiple execution of the script.
I wonder if there is a script that can achieve this at one shot.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105646

Thank you for your help.
0
JOSHUABT
Asked:
JOSHUABT
2 Solutions
 
jogosCommented:
How much times you will execute that?  Is it worth it?
Changing recovery model normaly is also followed with a full backup or your going to FULL followed by a trn backup will let this fail.....

But for the challenge try sp_MSForEachDB , use ? for the db_name. Not tested and also no test integrated on the current recovery model .

GO
declare @cmd1 ad nvarchar(1000)
set @cmd1 =
'IF '?' not in  NOT IN(''master'',''tempdb'',''msdb'',''model'')
BEGIN
 ALTER DATABASE ? SET RECOVERY SIMPLE
END 
'
exec sp_msforeachdb @command1 = @cmd1
GO

Open in new window


0
 
JOSHUABTAuthor Commented:
Thank you for your help.
It worked with a minor error. Even though it's excluded, it still gives an error message on 'tempdb'. To avoid the error, I had to use EXEC function as described below :

http://consultingblogs.emc.com/jamesrowlandjones/archive/2008/07/15/sql-server-database-recovery-model-how-do-you-set-yours.aspx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now