sp_msforeachdb: Changing recovery model for all databases

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/

Advertisements

3 thoughts on “sp_msforeachdb: Changing recovery model for all databases

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s