Month: July 2011

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)

My all-time favorite script is this cross-tab script showing the latest backups of all databases on the current box …

--lastbackups.sql
select sdb.name,
max(case bst.[type] when 'D' then convert(varchar(16), bst.backup_finish_date, 120) end) last_full_backup,
max(case bst.[type] when 'I' then convert(varchar(16), bst.backup_finish_date, 120) end) last_diff_backup,
max(case bst.[type] when 'L' then convert(varchar(16), bst.backup_finish_date, 120) end) last_log_backup,
case sdb.[status]
when '0' then 'Full'
when '4' then 'Bulk-logged'
when '8' then 'Simple'
when '16' then 'Full'
when '24' then 'Simple'
when '32' then 'Restoring'
when '528' then 'Off_line'
when '536' then 'Off_line'
when '1048' then 'Read_only'
when '2072' then 'Restricted User'
when '65536' then 'Full'
when '65544' then 'Simple'
when '2098176' then 'Standby / Read-Only'
when '4194328' then 'Simple'
when '4259848' then 'Simple'
when '1073741840' then 'Full'
when '1073807360' then 'ShutDown'
else '' end db_status, sdb.[status]
from master..sysdatabases sdb
left join msdb..backupset bst on bst.database_name = sdb.name
where sdb.name != 'tempdb'
group by sdb.name, sdb.[status]
--order by sdb.name
order by max(bst.backup_finish_date) -- nulls at top
--order by sdb.[status]