Post reply

script to set all user database to simple recovery model

  • August 23, 2011 at 7:11 am

    #242512

    get me a script to set all user database to simple recovery model

    i tried the below......... it is changing for system databases also

    exec sp_msforeachdb 'alter database ? set recovery simple'

  • August 23, 2011 at 7:17 am

    #1371971

    just a day or so ago someone asked for the same thing;

    see this topic for the discussion and a working solution:

    http://www.sqlservercentral.com/Forums/FindPost1162705.aspx

    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!

  • January 8, 2014 at 9:44 am

    #1678477

    I really liked that script, but, being an efficiency fanatic, I thought I would improve it just a little. Instead of building the SQL statement and then using replace to alter it, my version just builds the correct sql statement the first time. Here it is:

    USE MASTER

    declare

    @isql varchar(2000),

    @dbname varchar(64),

    @logfile varchar(128)

    declare c1 cursor for

    SELECT d.name, mf.name as logfile--, physical_name AS current_file_location, size

    FROM sys.master_files mf

    inner join sys.databases d

    on mf.database_id = d.database_id

    where recovery_model_desc <> 'SIMPLE'

    and d.name not in ('master','model','msdb','tempdb')

    and mf.type_desc = 'LOG'

    open c1

    fetch next from c1 into @dbname, @logfile

    While @@fetch_status <> -1

    begin

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

    print @isql

    --exec(@isql)

    select @isql='USE ' + @dbname + ' checkpoint'

    print @isql

    --exec(@isql)

    select @isql='USE ' + @dbname + ' DBCC SHRINKFILE (' + @logfile + ', 1)'

    print @isql

    --exec(@isql)

    fetch next from c1 into @dbname, @logfile

    end

    close c1

    deallocate c1

  • January 8, 2014 at 11:26 am

    #1678520

    Todd Erickson (1/8/2014)

    I really liked that script, but, being an efficiency fanatic, I thought I would improve it just a little. Instead of building the SQL statement and then using replace to alter it, my version just builds the correct sql statement the first time. Here it is:

    USE MASTER

    declare

    @isql varchar(2000),

    @dbname varchar(64),

    @logfile varchar(128)

    declare c1 cursor for

    SELECT d.name, mf.name as logfile--, physical_name AS current_file_location, size

    FROM sys.master_files mf

    inner join sys.databases d

    on mf.database_id = d.database_id

    where recovery_model_desc <> 'SIMPLE'

    and d.name not in ('master','model','msdb','tempdb')

    and mf.type_desc = 'LOG'

    open c1

    fetch next from c1 into @dbname, @logfile

    While @@fetch_status <> -1

    begin

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

    print @isql

    --exec(@isql)

    select @isql='USE ' + @dbname + ' checkpoint'

    print @isql

    --exec(@isql)

    select @isql='USE ' + @dbname + ' DBCC SHRINKFILE (' + @logfile + ', 1)'

    print @isql

    --exec(@isql)

    fetch next from c1 into @dbname, @logfile

    end

    close c1

    deallocate c1

    I'd have to test it but I don't believe that all those concatenations will necessarily be more efficient.

    --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)

  • January 8, 2014 at 12:14 pm

    #1678542

    if you want to keep it simple and just change recovery model for all user databases run this, copy results to another window and run

    select 'alter database ['+name+'] set recovery simple' from master.sys.databases where database_id > 4 and state_desc = 'online'

    avoid ms_foreachdb, use cursors (there i said it on a thread with Jeff on it). ms_foreachdb creates a cursor in the background anyway, its unsupported, difficult soon as you want to skip some databases, and has been shown to skip databases in error.

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

  • January 8, 2014 at 12:42 pm

    #1678550

    george sibbald (1/8/2014)

    if you want to keep it simple and just change recovery model for all user databases run this, copy results to another window and run

    select 'alter database ['+name+'] set recovery simple' from master.sys.databases where database_id > 4 and state_desc = 'online'

    avoid ms_foreachdb, use cursors (there i said it on a thread with Jeff on it). ms_foreachdb creates a cursor in the background anyway, its unsupported, difficult soon as you want to skip some databases, and has been shown to skip databases in error.

    Just to be sure, I don't object to cursors for this type of thing (even if they are unnecessarily complicated for such a thing). 😉

    --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)

  • January 8, 2014 at 1:38 pm

    #1678582

    I know, I just couldn't resist it ! 😉

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

  • March 7, 2015 at 8:15 am

    #1782351

    Thank you! Very useful

  • March 7, 2015 at 5:12 pm

    #1782367

    george sibbald (1/8/2014)

    I know, I just couldn't resist it ! 😉

    BWAAA-HAAAA! True sign of being retired. 🙂 Do everything as simple as possible. I love it. Thanks, George.

    --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)

  • May 5, 2017 at 8:23 am

    #1941320

    We do not want to generate a line for databases that are already in simple recovery mode.  Add an 'OR' statement if you use bulk_logged
    select 'alter database ['+name+'] set recovery simple' from master.sys.databases where database_id > 4 and state_desc = 'online' AND recovery_model_desc = 'full;'

    I use your script all the time and have to add that AND recovery_model_desc = 'Full;' part so I thought others might like the addition.

    'nix

  • May 5, 2017 at 11:20 am

    #1941354

    another option would be to add - another option would be to add - and recovery_model_desc != 'simple'

    saves the 'or'

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

  • July 25, 2018 at 6:23 am

    #1998925

    Todd Erickson - Wednesday, January 8, 2014 9:44 AM

    I really liked that script, but, being an efficiency fanatic, I thought I would improve it just a little. Instead of building the SQL statement and then using replace to alter it, my version just builds the correct sql statement the first time. Here it is:USE MASTERdeclare@isql varchar(2000),@dbname varchar(64),@logfile varchar(128)declare c1 cursor for SELECT d.name, mf.name as logfile--, physical_name AS current_file_location, sizeFROM sys.master_files mfinner join sys.databases don mf.database_id = d.database_idwhere recovery_model_desc <> 'SIMPLE'and d.name not in ('master','model','msdb','tempdb') and mf.type_desc = 'LOG'open c1fetch next from c1 into @dbname, @logfileWhile @@fetch_status <> -1beginselect @isql = 'ALTER DATABASE ' + @dbname + ' SET RECOVERY SIMPLE'print @isql--exec(@isql)select @isql='USE ' + @dbname + ' checkpoint'print @isql--exec(@isql)select @isql='USE ' + @dbname + ' DBCC SHRINKFILE (' + @logfile + ', 1)'print @isql--exec(@isql)fetch next from c1 into @dbname, @logfileendclose c1deallocate c1

    Hi Loved the script used it quite  few times now. I had to change it to add some quotes to deal with spaces and increase the @dbname to (128) to handle long db names in SQL 
    Hope it helps someone.


    USE MASTER
    declare
     @isql varchar(2000),
     @dbname varchar(128),
     @logfile varchar(128) declare c1 cursor for
     SELECT d.name, mf.name as logfile--, physical_name AS current_file_location, size
     FROM sys.master_files mf
      inner join sys.databases d
      on mf.database_id = d.database_id
     where recovery_model_desc <> 'SIMPLE'
     and d.name not in ('master','model','msdb','tempdb')
     and mf.type_desc = 'LOG' 
     open c1
     fetch next from c1 into @dbname, @logfile
     While @@fetch_status <> -1
      begin
      select @isql = 'ALTER DATABASE "' + @dbname + '" SET RECOVERY SIMPLE'
      print @isql
      --exec(@isql)
      select @isql='USE "'+ @dbname +'" checkpoint'
      print @isql
      --exec(@isql)
      select @isql='USE "' + @dbname + '" DBCC SHRINKFILE ("' + @logfile + '", 1)'
      print @isql
      --exec(@isql)
      
      fetch next from c1 into @dbname, @logfile
      end
     close c1
     deallocate c1

  • September 7, 2018 at 5:24 am

    This was removed by the editor as SPAM

  • September 7, 2018 at 6:22 am

    #2004837

    It would appear that the purpose of all this scripts is to shrink the log file for any database not in the SIMPLE recovery model.  I see several shortcomings in all of this...
    1.  No where does it keep track of which databases have been affected.
    2.  For those databases affected, all transaction log chains have been broken.
    3,  Because of #1, there is no way to correctly reestablish #2.
    3.  No where have the log file growth patterns been checked for best practices and there is no history of usage to determine what the sizes should be set to.  If the current settings are the initial default settings, then all of this is going to make performance issues worse.

    About the only place that I can see using such a script would be when you copy a prod database to a Dev or Test environment and then that concerns me just as much as if it were used in a Prod environment.  Insofar as backups are concerned, there is strong merit in treating Dev and Test environments just like Prod.  There have been several times that a Developer or Tester has accidently wiped out large portions of data with either a Delete, Truncate, or an overwrite of some sort and if Point-In-Time backups weren't available, we'd have lost a huge amount of work.

    Consider never using such scripts as have been cited or posted on this thread.  Do something a bit more surgical.

    --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)

Viewing 14 posts - 1 through 13 (of 13 total)

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