script to set all user database to simple recovery model

  • ravishankar.yedoti

    Right there with Babe

    Points: 738

    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'

  • Lowell

    SSC Guru

    Points: 323321

    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!

  • Todd Erickson

    SSC Enthusiast

    Points: 115

    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

  • Jeff Moden

    SSC Guru

    Points: 993628

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • george sibbald

    SSC Guru

    Points: 104200

    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.

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

  • Jeff Moden

    SSC Guru

    Points: 993628

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • george sibbald

    SSC Guru

    Points: 104200

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

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

  • marina-r

    Old Hand

    Points: 332

    Thank you! Very useful

  • Jeff Moden

    SSC Guru

    Points: 993628

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • NixIsTheNameSQLIsTheGame

    SSC Eights!

    Points: 886

    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

  • george sibbald

    SSC Guru

    Points: 104200

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

    saves the 'or'

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

  • tom 2328

    Newbie

    Points: 5

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

    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

  • davidajacobus

    Grasshopper

    Points: 13

    Both options work, however, I needed to modify each as noted below


    -- ONLY GET DATABASES NOT IN SIMPLE RECOVERY ALREADY
    SELECT 'alter database ['+name+'] set recovery simple' from master.sys.databases where database_id > 4 and state_desc = 'online' AND recovery_model_desc <> 'SIMPLE'


    -- WRAP DATABASE NAME IN [ ]
    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

  • Jeff Moden

    SSC Guru

    Points: 993628

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

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

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