Introduction:
Here is another script that used sp_msforeachdb to change the recovery model for all the databases.
Note: There is a caveat that changing the recovery model is not recommended without thorough knowledge, but here we are only discussing the technical aspect.
Select name,recovery_model_desc From Sys.databases
--To avoid/exception on applying the change, you may add your databases in the list.
EXEC sp_msforeachdb '
IF ''?'' not in (''tempdb'',''master'',''msdb'',''model'',''Reportserver'',''ReportserverTempDB'')
begin
exec (''ALTER DATABASE [?] SET RECOVERY SIMPLE;'')
end
'
Select name,recovery_model_desc From Sys.databases
See Also:
You may be interested to know more on sp_MSforeachdb usage, please visit the below links
https://gallery.technet.microsoft.com/T-SQL-Script-Get-Table-Row-94e49d01
https://gallery.technet.microsoft.com/How-to-change-datacompressi-47dfce48
https://latheeshnk.wordpress.com/2015/05/06/identify-references-of-an-object-in-sql-server/
Can you also tell me is there anyway to shrink all log files using above method?
Thanks,
Yagnesh
LikeLike
Hello Yagnesh,
First off, Thank you for reading the post.
I find this question very interesting and please find the answer in the below post.
https://latheeshnk.wordpress.com/2015/08/20/sp_msforeachdb-shrinking-log-files-for-all-databases-in-sql-server/
LikeLike