Simple-Mode to Full-Recovery-Mode script

Leading on from my script to list databases set to Simple recovery-mode, I found changing them to Full was not as easy as just changing the sys.databases flag from ‘3’ to ‘1’ (not allowed).

I had a requirement to change a whole server full of databases, but due to our ‘smart’ backup software (that on discovering new Full recovery-mode databases would immediately want to take full backups), had to stagger the changes to one-an-hour – so the (production) box wouldn’t slow to a crawl.

Here’s the job-step …

--capture name of one simple database
declare @name varchar(50)
select @name = [name] from sys.databases where recovery_model = 3 and [name] not in ('master', 'msdb', 'tempdb')
-- 1 = Full, 3 = Simple

--change it to full
declare @cmd varchar(200)
set @cmd = 'alter database ' + @name + ' set recovery full'
exec (@cmd)

Leave a Reply

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

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

Facebook photo

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

Connecting to %s