ASPNetFAQ.com: What is ASP.NET?

Technology posts on ASP.NET, IIS, Windows (+ a little Linux), Cloud Servers, Hosting, and more!
  • Blog Home
Search the site...

How to Set All Databases to Simple Recover Model (Logging)

Tweet
Share2
2 Shares

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

More from my site

  • Build an ASP.NET Core app on a Mac Using Visual Studio CodeBuild an ASP.NET Core app on a Mac Using Visual Studio Code
  • Orchard Memory Usage and PerformanceOrchard Memory Usage and Performance
  • WordPress IIS and PNG Not Showing Up HeadachesWordPress IIS and PNG Not Showing Up Headaches
  • Video: Web Development ASP.NET C# – MasterPageVideo: Web Development ASP.NET C# – MasterPage
  • What makes good web hosting?What makes good web hosting?
Tweet
Share2
2 Shares

2 comments on “How to Set All Databases to Simple Recover Model (Logging)”

  1. Olaf van Nimwegen says:
    May 21, 2012 at 2:50 am

    How to exclude the system Databases?

    With regards,

    O. van Nimwegen

  2. Brad Kingsley says:
    May 21, 2012 at 5:51 am

    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’)

Proverbs 19:20

"Get all the advice and instruction you can, so you will be wise the rest of your life."

A Note On WordPress Hosting

Our main focus is of course .NET, but with a mix of Linux, virtualization, and other technologies. But if you're really looking for the best WordPress hosting specifically, read my WordPress host review to save yourself hassle AND money!




Recent Posts

  • What makes good web hosting?
  • jQuery Mobile C# ASP.NET and N5 Networks Software Repository
  • Open Source Bug Tracking Software and the Orchard Project
  • ASP.NET Development with Dreamweaver MX: Visual QuickPro Guide
  • Kendo UI Sample, ASP.NET Ajax Tutorial & More

Tags

ASP.NET Automation centos CMS css cytanium Development/Coding Email gmail Hosting htaccess http https IIS javascript Learning Linux logparser MySQL nginx openssl OrcsWeb performance PowerShell redirect RHEL security server SherWeb smtp SQL/Databases ssl System Administration telnet terminal tip Troubleshooting Ubuntu virtualization Visual Studio web farm web hosting Windows windows server Wordpress

Categories

  • ASP.net development
  • Development/Coding
  • Hosting
  • IIS (Internet Information Services)
  • SQL/Databases
  • System Administration
  • Virtualization
(c) ASPNETFAQ.com