Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Recovery model

Author  Topic 

luka
Starting Member

8 Posts

Posted - 2015-02-18 : 02:14:48
Hello,

i do not know what recovery model should be most suitable for the following scenario. Can someone help me with this situation?
The data file and transaction log file are stored on Storage Area Network.A full backup of DB starts every Friday at 11 pm and runs over the weekend.Further backups take place every three hours during the week.To minimize the amount of data lost in the event of hardware failure which recovery model would be more applicable. (simple, full , or transaction recovery model).

Thx.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-18 : 10:00:18
There is no "transaction recovery model" Your choices are Simple, Full, or Bulk Logged.

https://msdn.microsoft.com/en-ca/library/ms189275.aspx

Which one you choose depends on how the data is used and modified.

e.g. Simple is common in DW scenarios, Full in OLTP

Basically there's not enough info to make a recommendation
Go to Top of Page

luka
Starting Member

8 Posts

Posted - 2015-02-18 : 15:03:29
Thank you.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-18 : 15:07:03
The backup schedule is not what defines which recovery you should use. The business is what defines it. How much data loss can the business afford to lose if the database needs to be restored? Often times in an OLTP environment, we are talking just a few minutes. If that's the case for your business and application, then use FULL.

When using FULL, I recommend the following backup schedule:
1. FULL backups - every day during non-peak, typically at night
2. DIFF backups - 12 hours after the full backup
3. TLOG/incremental backups - every 5 minutes, perhaps every 1 minute, though it's most common to use 15 minutes but that is pretty out-dated

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -