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.
| 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.aspxWhich one you choose depends on how the data is used and modified.e.g. Simple is common in DW scenarios, Full in OLTPBasically there's not enough info to make a recommendation |
 |
|
|
luka
Starting Member
8 Posts |
Posted - 2015-02-18 : 15:03:29
|
| Thank you. |
|
|
|
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 night2. DIFF backups - 12 hours after the full backup3. TLOG/incremental backups - every 5 minutes, perhaps every 1 minute, though it's most common to use 15 minutes but that is pretty out-datedTara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
Recovery model