Previous Article
SQL Server Management Studio - Restore database
SQL Server Management Studio - Change Recovery Model to Simple
Next Article
SQL Server Management Studio - List all tables in a database
Coding Article #: 1086 - Published On: February 13, 2021 @ 03:43:49 AM - Last Updated on: January 01, 1900
This article has been Favorited 0 times
Join today, and add this to your favorites.
Coding Source - Share on MySpace Coding Source - Share With Facebook Coding Source - Share on Twitter Coding Source - Share on Reddit Coding Source - Share on Digg It Coding Source - Share on Stumble Upon It Coding Source - Share on Delicious
Share With Friends (Updated 6-8-2010)

Supported Files
No Files for this Article.
No Screenshot Available
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
Post to Facebook about: SQL Server Management Studio - Change Recovery Model to Simple