Full & Simple Recovery Model

Question

Full & Simple Recovery Model

msocp

Say Hey Kid

Points: 682
More actions
February 2, 2013 at 2:36 am

#273758

what is the standard way to put the database Full recovery model OR Simple recovery model?

Viewing 11 posts - 1 through 10 (of 10 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

it really depends on how frequently you want\can take full backups and what your recovery strategy is, i.e. how much data can you afford to lose.

Generally production databases are in full recovery mode and test databases in simple recovery mode, but that is not a hard and fast rule.

---------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Take a look at the differences between the recovery models and decide based on business requirements: http://www.sqlservercentral.com/articles/Administration/75461/

Also maybe Managing Transaction Logs[/url]

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

msocp Say Hey Kid Points: 682 More actions · Answer 3

thanks,

i just want to know must be production DB in Full Recovery. am right

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

msocp (2/2/2013)
thanks,
i just want to know must be production DB in Full Recovery. am right

Usually, but there are exceptions. Read up on what the differences are between the recovery models and decide based on your business requirements.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

msocp (2/2/2013)
thanks,
i just want to know must be production DB in Full Recovery. am right

Usually, but there are exceptions. Read up on what the differences are between the recovery models and decide based on your business requirements.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

msocp Say Hey Kid Points: 682 More actions · Answer 6

I know about Full Recovery Model & Simple Recovery model.

there are Five production DB Server. two 24/7 live production server contain one database each DB contain 35 tables. three DB Server contain 40, 30, 10 databases.

just i want your suggestion.

thanks for participation.

george sibbald SSC Guru Points: 104210 More actions · Answer 7

if you are not sure then play safe and put the databases in full recovery mode and set up log backups.

Certainly do that for the two 24/7 databases, the other 3 servers we don't have enough info on what the databases are for and how they are used, but better too much recovery than not enough.

---------------------------------------------------------------------

SQLAJ Ten Centuries Points: 1359 More actions · Answer 8

As Gail mentioned, ask the Business Side to determine what an acceptable level of data loss is to them and the business over all.

Use that to create your disaster recovery plan and set your backup schedule accordingly.

______________________________
AJ Mendo | @SQLAJ

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

msocp (2/3/2013)
I know about Full Recovery Model & Simple Recovery model.
there are Five production DB Server. two 24/7 live production server contain one database each DB contain 35 tables. three DB Server contain 40, 30, 10 databases.
just i want your suggestion.
thanks for participation.

This is one of those questions where I have to say that if you don't know the answer to it, then you need to immediately get some serious training or hire someone that does know the answers. The data for production servers is the lifeblood of a company and not knowing the answers to such fundamental questions puts the company at risk.

Please... for the sake of your company, get some training or hire someone that knows these things. There is no time for an "accidental" DBA here (that's not a slam... it's an observation).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

msocp Say Hey Kid Points: 682 More actions · Answer 10

Thanks for participation about Full & simple recovery mode.

regards.

shafiq