shriking of database

Use master in QUERY ANALYSER
declare @DBName varchar(35),
             @str varchar (255),
  @str1 varchar(1000),
    @str2 varchar(1000),
      @str3 varchar(1000),
      @defaultRecoveryModel Varchar(100)
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
SELECT @defaultRecoveryModel=Convert(Varchar(100),DATABASEPROPERTYEX (@DBName, 'Recovery'))
IF(@defaultRecoveryModel<>'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)
select @str1  = 'ALTER DATABASE' + ' ' +@DBName+ ' ' + 'SET RECOVERY ' + @defaultRecoveryModel
PRINT 'EXECUTING - ' + @str1
exec(@str1)

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

source: ftp.PTCinfo.org

Share on Google Plus

About Unknown

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.