Nothing can stop me, nothing holds me back from changing recovery mode and getting development on track…

Greetings and other salutations,

Today I found out that part of the development environment was in “Full Recovery Mode”.  This means that if someone isn’t taking log backups, their databases get huge, and it also means that the backups were much bigger than they should have been.  They don’t need point in time recovery in our development environment so we decided to move them to “Simple recovery”. This could have been a big all day job if I went through the GUI, but you know me, I found a way to script it out and thought I would share it. I am showing you how to do it on one server at a time:

Connect to your development server in the master database and run this query to see how many are in “Full Recovery”:

 SELECT name, recovery_model_desc
 FROM sys.databases
 WHERE recovery_model_desc = 'FULL'

When I ran it on one of my servers, there were 24 databases that needed to be adjusted. So I built this:

 SELECT 'ALTER DATABASE [' + name + '] SET RECOVERY SIMPLE ;'
 FROM sys.databases
 WHERE recovery_model_desc = 'Full'

Then I took the results from that query and copied it into a new window and ran it and just like that, all my databases are now in “Simple Mode” in Development. I ran the first query one more time to make sure everything updated as expected.

It is a beautiful thing. I hope this helps you clean up development too!

The song from this post is from the Kongo’s Take it from Me

About andreaallred

SQL Server and helping people is my passion. If I can make someone laugh, I know I have made a difference.

2 thoughts on “Nothing can stop me, nothing holds me back from changing recovery mode and getting development on track…

  1. If you have problems with currently open connections you can also add WITH ROLLBACK IMMEDIATE to the end of the ALTER DATABASE for some settings. It will immediately close all existing connections and roll back any open transactions. Unfortunately it won’t work with RECOVERY so you would have to do something like the following (stolen w some modifications from BOL)

    USE master;
    GO
    ALTER DATABASE AdventureWorks2012
    SET SINGLE_USER
    WITH ROLLBACK IMMEDIATE;
    GO
    ALTER DATABASE AdventureWorks2012
    RECOVERY SIMPLE ;
    GO
    ALTER DATABASE AdventureWorks2012
    SET MULTI_USER;
    GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s