Below is a slight variant of some TSQL I borrowed from http://www.roelvanlisdonk.nl/?p=1703
It loops through all the databases on a server and changes the recovery model to Simple, which, if you aren’t aware, changes the amount of logging and how the logging is handled. You should make sure you aware of the impact of that change before making the adjustments.
-- This script will set recovery model to Simple on all databases use [master] go -- Declare variable for each database name declare @databaseName nvarchar(128) -- Define the cursor declare databaseCursor cursor -- Define the cursor dataset for select [name] from sys.databases -- 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') -- Get information from next row fetch next from databaseCursor into @databaseName end -- End loop and clean up close databaseCursor deallocate databaseCursor go
How to exclude the system Databases?
With regards,
O. van Nimwegen
You could manually exclude anything you wanted by adjusting the cursor query similar to:
select [name] from sys.databases where [name] not in (‘somedatabase’, ‘anotherdatabase’)