[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1286
  • Last Modified:

Script to change ALL databases recovery mode to Full

I am after a script to run that will change the recovery model of all databases to FULL and not just one at a time.

Can anyone please help?
0
Avatar261
Asked:
Avatar261
  • 3
  • 3
  • 2
  • +1
1 Solution
 
ralmadaCommented:
try
EXEC sp_MSForEachDB 'ALTER DATABASE [?] SET RECOVERY FULL';

Open in new window

0
 
Avatar261Author Commented:
Brilliant, just as a side thought does it matter if master, model are in FULL mode?
0
 
ralmadaCommented:
That doesn't matter to run the above script.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
anushahannaCommented:
But you won't be able to do a FULL mode on tempdb

you will get "Option 'RECOVERY' cannot be set in database 'tempdb'."
0
 
anushahannaCommented:
you can try something like

EXEC sp_MSforeachdb '
IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'')
    RETURN
ALTER DATABASE [?] SET RECOVERY FULL
'

to avoid hitting tempdb in your script, (and any other system db's you want to avoid)

I have not tested the above code, yet.
0
 
ralmadaCommented:
if you want to avoid the system dbs then do like below:

EXEC sp_MSforeachdb 'IF [?] NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
		ALTER DATABASE [?] SET RECOVERY FULL'

Open in new window

0
 
anushahannaCommented:
ralmada, you have guided me often with sp_MSforeachdb. Thanks again..

now, with a student's cap:

EXEC sp_MSforeachdb 'IF [?] NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
            ALTER DATABASE [?] SET RECOVERY FULL'

gives multiple errors..

if I change it to
EXEC sp_MSforeachdb 'IF "?" NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
            ALTER DATABASE [?] SET RECOVERY FULL'
it does not give the previous errors, but still gives the error:

Msg 5058, Level 16, State 1, Line 2
Option 'RECOVERY' cannot be set in database 'tempdb'.

that does not make sense.. even though we told to avoid tempdb, why does it try it again on tempdb?

also, I tried playing with db_name(), but no go..

muchas gracias :)
0
 
Avatar261Author Commented:
After checking a bit more after accepting the solution i to was getting the tempdb error.

After tweaking the script slightly, this works prefectly. The solution still stands as it was on the right lines.

EXEC sp_MSforeachdb 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')  
Begin  
Print ''?''
Declare @cmd varchar(255)
set @cmd = ''ALTER DATABASE [?] SET RECOVERY FULL''
exec (@cmd)
End'
0
 
sandsjhCommented:
EXEC sp_MSforeachdb 'IF [?] NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
		ALTER DATABASE [?] SET RECOVERY FULL'

Open in new window


Hi all: I used the above solution but keep getting the following:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'sandsjh1_123_db'.

Open in new window


Hoe to avoid this on 500 db's?

TIA.
Jason
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now