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
sql server management
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.
Damn styles !
Script Updated, thanks !