Home > SQL Server > Bulk change recovery model on all databases

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
Categories: SQL Server Tags:
  1. July 2, 2014 at 11:15 am

    Beware of word copy/pasting…. some characters have been altered:

    “–” has been replaced by “–”
    ” ‘ ” has been replaced by ” ‘ ” or ” ’ ”

    Running the script as is, won’t work.

    • July 9, 2014 at 4:49 pm

      Damn styles !
      Script Updated, thanks !

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: