Posts Tagged ‘sql server management’

Bulk change recovery model on all databases

August 12, 2013 2 comments

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]
-- 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
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
  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 loop and clean up
close databaseCursor
deallocate databaseCursor 

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…

Categories: SQL Server Tags: