SQL Stuff

Change Recovery Model For All Databases

Here’s some quick logic to change the recovery models of all databases (excluding system databases). This question comes up quite a bit and there are a number of ways to automate so thought I would throw it out there for anyone that might be interesting in doing it.


use master;

set nocount on

select name, recovery_model_desc from sys.databases where database_id > 4 order by name asc -- check recovery model for all databases.

declare @set_full_recovery_all_databases varchar(max)

set @set_full_recovery_all_databases = ''

select @set_full_recovery_all_databases = @set_full_recovery_all_databases +

'alter database [' + name + '] set recoverty full;' + char(10)

from sys.databases where database_id > 4 and state_desc = 'online'

exec (@set_full_recovery_all_databases) -- change all databases to full recovery.

select name, recovery_model_desc from sys.databases where database_id > 4 order by name asc -- check recovery model again to confirm.

Advertisements

Categories: SQL Stuff

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