Changing Recovery Mode

• Jon-183221

  SSCommitted

  Points: 1908

  More actions

  May 26, 2005 at 7:30 am

  #109716

  Hi,

  I have many database running in Full recovery mode. I want to change them all to Simple mode. I know this probably sounds a strange request (they arent production database) and I know I could do this manually, but I'm a techie (not a very good one cos I cant sort this out) and love to try and do things in a different way.

  Any advice on this would be greatly appreciated.

   Jon

• David.Poole

  SSC Guru

  Points: 76108

  More actions

  May 26, 2005 at 8:27 am

  #561222

  DECLARE @sDBName SysName

  SET @sDBName=''

  WHILE @sDBName IS NOT NULL

  BEGIN

  SELECT @sDBName = MIN(Name)

  FROMMaster.dbo.sysdatabases

  WHEREName>@sDBName

  AND Name NOT IN ('Master','Model','MSDB','TEMPDB','DISTRIBUTION')

  AND DATABASEPROPERTY(Name,'IsTruncLog')=0

  IF @sDBName IS NOT NULL

  exec sp_dboption @sDBName,'trunc. log on chkpt.',true

  END

  LinkedIn Profile

• Nikhil Khimani

  SSC Enthusiast

  Points: 174

  More actions

  May 27, 2005 at 5:53 am

  #561415

  Yet another way to skin a cat ...

   

  exec sp_MSforeachdb "if '?' not in ('master','model','tempdb','distribution') begin alter database '?' set recovery SIMPLE WITH NO_WAIT end"

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 27, 2005 at 7:21 am

  #561455

  yet another... appropriate for a developer but not production...this sets autoclose as well as the recovery,so that enterprise manager opens quickly, because the databases are not closed.

   

  declare

  @isql varchar(2000),

  @dbname varchar(64)

  declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')

  open c1

  fetch next from c1 into @dbname

  While @@fetch_status <> -1

   begin

   select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'

   select @isql = replace(@isql,'@dbname',@dbname)

   print @isql

   exec(@isql)

   select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'

   select @isql = replace(@isql,'@dbname',@dbname)

   print @isql

   exec(@isql)

   fetch next from c1 into @dbname

   end

  close c1

  deallocate c1

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Jon-183221

  SSCommitted

  Points: 1908

  More actions

  May 27, 2005 at 7:30 am

  #561467

  Thanks everyone.