Geeks With Blogs
Ilya Verbitskiy Thoughts and links about computer programming

Changing recovery mode from FULL to SIMPLE for a SQL Server database is not a big deal. You can do it with one simple command.

   1:  USE master
   2:  ALTER DATABASE YourDatabase SET RECOVERY SIMPLE

 

But it might be tricky task to do for all databases on your server. Of course, you can to generate a script using using any programming language. I usually use F# for such “quick and dirty” solutions.  This time I solved the problem with T-SQL. I had to change recovery mode for more then 200 databases.

   1:  declare @name sysname,
   2:          @recovery_model_desc nvarchar(120),
   3:          @script nvarchar(500),
   4:          @full_backup_count int
   5:   
   6:  select @full_backup_count = count(*) from sys.databases where recovery_model_desc = 'FULL'
   7:  print 'There are ' + cast(@full_backup_count as varchar) + ' with FULL backup set'
   8:   
   9:  declare db cursor 
  10:      for select name, recovery_model_desc from sys.databases where recovery_model_desc = 'FULL'
  11:      for read only
  12:   
  13:  open db
  14:  fetch next from db into @name, @recovery_model_desc
  15:  while @@fetch_status = 0
  16:  begin
  17:      set @script = 'alter database [' + @name + '] set recovery simple'
  18:      exec sp_executesql @script
  19:      print 'Done with ' + @name
  20:      fetch next from db into @name, @recovery_model_desc
  21:  end
  22:   
  23:  close db
  24:  deallocate db
  25:   
  26:  select @full_backup_count = count(*) from sys.databases where recovery_model_desc = 'FULL'
  27:  print 'There are ' + cast(@full_backup_count as varchar) + ' with FULL backup set'

 

The script worked for me. Please make sure that you are using the script on your own risk, and I am not responsible for potential data damage.

You may find additional information about changing database recovery mode in View or Change the Recovery Model of a Database (SQL Server) article.

Posted on Thursday, October 10, 2013 6:30 AM SQL , SQL Server | Back to top


Comments on this post: How to change recovery mode for all databases

# re: How to change recovery mode for all databases
Requesting Gravatar...
It works for me too on MSSQL server 2008.

THX
Left by seb on Jul 01, 2014 10:41 AM

Your comment:
 (will show your gravatar)


Copyright © Ilya Verbitskiy | Powered by: GeeksWithBlogs.net