script to set all user database to simple recovery model

Question

Post reply

script to set all user database to simple recovery model

ravishankar.yedoti

Right there with Babe

Points: 738
More actions
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'
September 7, 2018 at 5:24 am

This was removed by the editor as SPAM

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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 More actions · Answer 2

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: 1004290 More actions · Answer 3

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)

george sibbald SSC Guru Points: 104210 More actions · Answer 4

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: 1004290 More actions · Answer 5

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)

george sibbald SSC Guru Points: 104210 More actions · Answer 6

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

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

marina-r SSC-Addicted Points: 472 More actions · Answer 7

marina-r

SSC-Addicted

Points: 472

March 7, 2015 at 8:15 am

#1782351

Thank you! Very useful

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 8

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)

NixIsTheNameSQLIsTheGame SSC Eights! Points: 996 More actions · Answer 9

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: 104210 More actions · Answer 10

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 More actions · Answer 11

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

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 12

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)