?
Solved

Script SQL Recovery mode from FULL to Simple

Posted on 2010-03-28
11
Medium Priority
?
746 Views
Last Modified: 2012-05-09
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
0
Comment
Question by:thaapavuori
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 22

Accepted Solution

by:
pivar earned 500 total points
ID: 28904574
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
 
LVL 5

Assisted Solution

by:ericathome
ericathome earned 1000 total points
ID: 28904877
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
 
LVL 5

Assisted Solution

by:ericathome
ericathome earned 1000 total points
ID: 28906311
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:thaapavuori
ID: 28908664
Thanks for both of you. I will test this and I will let you know the results :)

Timo
0
 
LVL 41

Expert Comment

by:graye
ID: 28979429
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
 

Author Comment

by:thaapavuori
ID: 28979757
Why extreme? Our software vendor recommend to use simple recovery mode and full recovery mode fill our hard drives...
0
 
LVL 41

Expert Comment

by:graye
ID: 28985442
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
 

Author Comment

by:thaapavuori
ID: 28987676
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
 
LVL 41

Assisted Solution

by:graye
graye earned 500 total points
ID: 28988961
Take a look at the article about log file maintenace... it might help:
http://www.emmet-gray.com/Articles/SQL_LogMaintenance.htm 
0
 

Author Comment

by:thaapavuori
ID: 28992735
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
 
LVL 41

Expert Comment

by:graye
ID: 29000169
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question