Bulk change recovery model on all databases

I don’t know where I found this script, but I’m using it every day (well, weeks may be more accurate) on Test environments to change all the Database to simple recovery model and save lot of disk space.

use [master]
go
-- Declare container variabels for each column we select in the cursor
declare @databaseName nvarchar(128)
 -- Define the cursor name
declare databaseCursor cursor
 -- Define the dataset to loop
for 
select [name] from sys.databases where name not in ('Master','tempdb','model','msdb')
 -- Start loop
open databaseCursor
 -- Get information from the first row
fetch next from databaseCursor into @databaseName
--  Loop until there are no more rows
while @@fetch_status = 0
begin
  print 'Setting recovery model to Simple for database [' + @databaseName + ']'
  exec('alter database [' + @databaseName + '] set recovery Simple')
  print 'Shrinking logfile for database [' + @databaseName + ']'
  exec(' use [' + @databaseName + '];' +'  
     declare @logfileName nvarchar(128);
    set @logfileName = (
        select top 1 [name] from sys.database_files where [type] = 1
    );
    dbcc shrinkfile(@logfileName,1);
    ')  
    -- Get information from next row
    fetch next from databaseCursor into @databaseName 
end
 --  End loop and clean up
close databaseCursor
deallocate databaseCursor 
go

All you have to do, is to copy/paste this script in a SQL Server Management Studio SQL Query window. It works on numerous SQL version (I used it on SQL 2005 ->2012) no matter the system language…

Advertisements