Coding Article #: 1086 - Published On: February 13, 2021 @ 03:43:49 AM - Last Updated on: January 01, 1900

Supported Files
No Files for this Article.

In this lesson, we are going to change the Recovery model from FULL to simple.
This helps if you need to change the model but cannot change it in the database properties | Options tab

Right-click on the database and choose [New Query]
Paste the following code and then click [Execute]

USE MASTER
declare
	@isql varchar(2000),
	@dbname varchar(64)
	declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')
	open c1
	fetch next from c1 into @dbname
	While fetch_status <> -1
		begin
		select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'
		select @isql = replace(@isql,'@dbname',@dbname)
		print @isql
		exec(@isql)
		select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'
		select @isql = replace(@isql,'@dbname',@dbname)
		print @isql
		exec(@isql)
		select @isql='USE @dbname checkpoint'
		select @isql = replace(@isql,'@dbname',@dbname)
		print @isql
		exec(@isql)
		fetch next from c1 into @dbname
		end
	close c1
	deallocate c1