Script SQL Recovery mode from FULL to Simple

I would like to change my all databases recovery mode from full to simple and set that new databases will in simple recovery model. I would like to use some script because I have very much databases. So can someone help me with this?

Thanks
thaapavuoriAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pivarCommented:
Hi,

This will create the script for you.


EXEC sp_MSForEachDB 'IF (''?'' NOT IN (''master'',''tempdb'',''model'',''msdb'')) BEGIN PRINT ''ALTER DATABASE [?] SET RECOVERY SIMPLE;''; PRINT ''GO''; END'


/peter
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ericathomeCommented:
Try the following script to generate the script to convert recovery mode to simple for your databases:

use master  
SELECT 'ALTER DATABASE [' + name + '] SET RECOVERY SIMPLE' from master..sysdatabases where name not in ('master','model','msdb','tempdb')  

I'll have to keep looking for code or setting to change the default.

Eric
0
ericathomeCommented:
The following addresses setting the default mode:

When a database is created, it has the same recovery model as the model database. To alter the default recovery model, use ALTER DATABASE to change the recovery model of the model database. You set the recovery model with the RECOVERY clause of the ALTER DATABASE statement. For more information, see ALTER DATABASE.

This was an excerpt from the following site: http://msdn.microsoft.com/en-us/library/aa173531(SQL.80).aspx 

Let me know if you need more details. Thanks, Eric
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

thaapavuoriAuthor Commented:
Thanks for both of you. I will test this and I will let you know the results :)

Timo
0
grayeCommented:
Just curious... why would you want to convert all of  your databases to the Simple Recovery model?
That's seems like quite an extreme thing to do!
0
thaapavuoriAuthor Commented:
Why extreme? Our software vendor recommend to use simple recovery mode and full recovery mode fill our hard drives...
0
grayeCommented:
Most folks who run a production database use the full recovery model.   This because it provides better protection for both "user mistake" and "failures".
Most folks are familar with failures...where a hard drive crashes and you have to restore from the last backup.  But a lot of new SQL administrator are not that familiar with recovering from a "user mistake".  For example, a user accidently deletes a table that contains useful data.  Your job is to return the database to the exact point in time before the user delete the table.  That kind of restore requires a full recovery model.
Most folks who have a database are actually concerned about the potential loss of data (particulary the loss of data between full backups).   Consider your bank... Do you want your bank account data to be covered by a system where an entire days worth of transactions could be lost if a failure or user mistake occured?
On the other hand, if you don't care about your data, then go ahead and switch to the Simple Recovery mode (or why bother putting it into a database at all?)
0
thaapavuoriAuthor Commented:
You are right. We are able to restore databases from our backups but it's always from the last night.

Our software vendor recommend to use simple recovery mode but that's true that some times might be useful to restore data from some moment which is not backed up. I understand that full recovery mode should be usefull in this case.

Im not familiar at all about this full recovery mode. I think that best practise would be to configure these transaction logs to different volume (and raid) than databases. And then I should configure my backup software (backup exec) to discuss with transaction logs and after my transaction logs have been backed up SQL could delete them. Am I right? However, this is something that I dont really know how to do it. Do you have some white paper about this.

My plan was change our rest databases to simple mode (because most of them already are) because this full recovery mode is eating very much disk space...  I was planing that one backup a day would be enough but you are actually right that always it's not enough...

We are actaully planning to implement Microsoft Data Protection Manager in use and this should offer almost online backups. I dont know this product very well yet but might be that this would need recovery mode as well...
0
grayeCommented:
Take a look at the article about log file maintenace... it might help:
http://www.emmet-gray.com/Articles/SQL_LogMaintenance.htm 
0
thaapavuoriAuthor Commented:
If I try to implement  full recovery mode should I change these log files to different volume. In my SQL server there is about 10 instances and hundreds and huhdreds of databases but they are not very big ones. All of my databases might be about 300 or 400 gb.. If I change them to a different volume how I should do ti?
0
grayeCommented:
You might not have any trouble just keeping the log files on the same volume.  It's difficult to guess where "weak link in the chain" might be.   I wouldn't immediately assume that placing the logfiles on the same volume would cause a major impact on performance.
But if there is trouble, moving logfiles to another volume at a late rime isn't difficult.   Also splitting MDF files from LDF files may cause the Volume Shadow Copy feature to fail on the database (which is bad, but not un-workable)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.