Full & Simple Recovery Model

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Full & Simple Recovery Model

11 posts, Page 1 of 2

1 2 »»

Full & Simple Recovery Model

Rate Topic

Display Mode

Topic Options

Author

Message

msocp

Posted Saturday, February 02, 2013 2:36 AM

Grasshopper

Group: General Forum Members
Last Login: Today @ 2:10 AM
Points: 20, Visits: 223

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

Post #1414925

george sibbald

Posted Saturday, February 02, 2013 2:39 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 5,269, Visits: 11,205

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.

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

Post #1414926

GilaMonster

Posted Saturday, February 02, 2013 3:33 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:17 PM
Points: 37,713, Visits: 29,968

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

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1414935

msocp

Posted Saturday, February 02, 2013 11:06 PM

Grasshopper

Group: General Forum Members
Last Login: Today @ 2:10 AM
Points: 20, Visits: 223

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

Post #1414982

GilaMonster

Posted Sunday, February 03, 2013 1:10 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:17 PM
Points: 37,713, Visits: 29,968

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 2008, MVP
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

Post #1414986

GilaMonster

Posted Sunday, February 03, 2013 1:15 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:17 PM
Points: 37,713, Visits: 29,968

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 2008, MVP
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

Post #1414988

msocp

Posted Sunday, February 03, 2013 1:48 AM

Grasshopper

Group: General Forum Members
Last Login: Today @ 2:10 AM
Points: 20, Visits: 223

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.

Post #1414995

george sibbald

Posted Sunday, February 03, 2013 3:07 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 5,269, Visits: 11,205

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.

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

Post #1414999

SQLAJ

Posted Sunday, February 03, 2013 5:12 AM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 6:36 AM
Points: 85, Visits: 356

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

Post #1415009

Jeff Moden

Posted Sunday, February 03, 2013 10:57 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906, Visits: 26,790

msocp (2/3/2013)

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1415036

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions