simple and full recovery models

Question

simple and full recovery models

nikhil.verma

SSCrazy

Points: 2603
More actions
March 16, 2009 at 2:12 am

#198017

Hello
Can anyone please tel me the exact difference in simple and full recovery models ( from the architectural point of view) . Say if i running DML operations on db , then how simple recovery model helps in saving space ( or restricts LDF growth ) . Please let me know how db behaves while it is runnig under these two model at one time.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQL_DBA_3 SSCarpal Tunnel Points: 4772 More actions · Answer 1

In full recovery model,all the DML transactions are logged and unless you have a periodic transactional backups,the committed transactions are still there in the log file.This makes it to grow until you perform a log backup.Log backup takes all the committed transactions into it.Since you have all the transactions in the log you can recover the database upto the point in case of disaster.

In simple recovery model,only the uncommitted transactions will be there in the log.The committed transactions are removed upon a checkpoint issued by sql server.So log file never grows abnormally and you cannot perfom a log backup.Point in time recovery is not possible in this case.If you have a full backup scheduled ,we can recover only upto that point.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

You may want to read this blog post by Kimberly Tripp. Her blog is one of the ones that I would recommend to anyone who works with sql server.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

maechismo_8514 SSCoach Points: 17510 More actions · Answer 3

nikhil.verma (3/16/2009)
Hello
Can anyone please tel me the exact difference in simple and full recovery models ( from the architectural point of view) . Say if i running DML operations on db , then how simple recovery model helps in saving space ( or restricts LDF growth ) . Please let me know how db behaves while it is runnig under these two model at one time.
Thanks

There are lots of differences between the two and the way they impact on a database. Database behaves primarily on the recovery model it is set to. Basic difference has already been advised. You should go through BOL and articles online.

eng_mgomaa Right there with Babe Points: 796 More actions · Answer 4

All what SQL Reddy said is right, and i think that he answered you in very good way , just i want to add very small note but very important:

Backup process hardly depend on Recovery mode for example :

1. in simple mode you can't make Transaction backup and also you can't make logshipping and Mirror DB

2. In bulk mode you can make transaction backup and log shipping but in the same time you can't make Mirroring for DB.

from this if you want to be in safe side try to use Bulk or Full recovery mode.