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

  • Quick start video: Hosting your ASP.NET Website in Microsoft AzureQuick start video: Hosting your ASP.NET Website in Microsoft Azure
  • Cloud Server FlexibilityCloud Server Flexibility
  • Orchard Memory Usage and PerformanceOrchard Memory Usage and Performance
  • Linking spam sent through shared IIS SMTP server to a userLinking spam sent through shared IIS SMTP server to a user
  • Extend Your Linux Disk SpaceExtend Your Linux Disk Space
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

.NET ASP.NET Automation blog centos cloud CMS css cytanium Development/Coding Email gmail Hosting htaccess IIS javascript Learning Linux logparser MVC MySQL Orchard OrcsWeb performance PHP PowerShell redirect RHEL security server SherWeb smtp SQL/Databases System Administration tip Troubleshooting Ubuntu virtualization Visual Studio web farm web hosting WebMatrix Windows windows server Wordpress

Categories

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