Simple Recovery \ Truncate and Shrink

,

This script sets the recovery model for all databases to simple. It also truncates the log files before shrinking the databases.

Use master
declare @DBName varchar(35),
             @str varchar (255),
		@str1 varchar(1000),
		  @str2 varchar(1000),
		    @str3 varchar(1000)
declare DBRecoveryModelGenerator_cursor cursor for
 select name from sysdatabases
where category in ('0', '1','16') 
order by name
open DBRecoveryModelGenerator_cursor
fetch next from DBRecoveryModelGenerator_cursor into @DBName while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin

IF
(SELECT DATABASEPROPERTYEX (@DBName, 'Recovery'))<>'SIMPLE'
BEGIN
select @str1 = 'ALTER DATABASE' + ' ' +@DBName+ ' ' + 'SET RECOVERY SIMPLE'
select @str2 = 'BACKUP LOG' + ' ' +@DBName+ ' ' + 'WITH NO_LOG'
select @str3 = 'DBCC SHRINKDATABASE(' + '' +@DBName+ '' + ', TRUNCATEONLY)'
PRINT 'EXECUTING - ' + @str1
exec (@str1)
PRINT 'EXECUTING - ' + @str2
exec (@str2)
PRINT 'EXECUTING - ' + @str3
exec (@str3)
END
ELSE
select @str2 = 'BACKUP LOG' + ' ' +@DBName+ ' ' + 'WITH NO_LOG'
select @str3 = 'DBCC SHRINKDATABASE(' + '' +@DBName+ '' + ', TRUNCATEONLY)'
PRINT 'EXECUTING - ' + @str2
exec (@str2)
PRINT 'EXECUTING - ' + @str3
exec (@str3)
end fetch next from DBRecoveryModelGenerator_cursor into @DBName end
close DBRecoveryModelGenerator_cursor
DEALLOCATE DBRecoveryModelGenerator_cursor
go

Rate

5 (2)

Share

Share

Rate

5 (2)