Post reply

Changing Recovery Mode

  • 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

  • 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

  • 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"

  • 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!

  • May 27, 2005 at 7:30 am

    #561467

    Thanks everyone.

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

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