April 10, 2008 at 12:25 am
It is recommended that if we have a simple recovery model, only full backups be taken; if Full or Bulk-logged recovery model, Full/Differential/Transaction should be taken
Is it true..can anyone support this statement and describe?
April 10, 2008 at 12:43 am
BOL has good info !
search for "Choosing the Recovery Model for a Database"
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/17ca2e0a-016d-4fb8-83b7-354578d6cfb9.htm
Short:
Simple recovery model: Full and differential backups supported.
bulk-recovery and full recovery:full / diff / log backups supported.
regarding bulk recovery model, BOL states this important note:
The bulk-logged recovery model is intended strictly as an adjunct to the full recovery model. We recommend that you use it only during periods in which you are running large-scale bulk operations, and in which you do not require point-in-time recovery of the database.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 11, 2008 at 12:02 pm
Hi Ambuj Mathur
The true answer here is - it depends...
Small DB - Low Transaction Volume
If you have a 150MB Database and the system that makes you of this database is a promotions company that keeps paper contracts of their daily operations and maybe make 6 bookings a day.
They will more than likely not suffer from a loss of 1 hour or even 8 due to a server crash - as long as their database backup is not on the server from the previous night.
The DB setting can be SIMPLE and Daily Backups - if the business is aware of the potential data loss
This does allow for a more automated database management from SQL itself in terms of data storage
Large DB - High Transaction Volume
If the DB you are looking after is say 100GB then 30 minutes log backups and evening full backups are made.
If more than 30 minutes of transaction can not be lost then this is a more secure backup method.
DB Option - Full Recovery - but ensure that you create log backups of the log and evening full backups
(If the DB is very Large then Differential backups may be required during the week and full on weekends)
To answer your question - what is stated is 100% - if a DB is in SIMPLE - you can only do a full backup - no log backups can be taken.
if DB is in FULL then a more flexible strategy for backups can be implemented - taking into account the size of the database and hardware (in terms of speed and storage space)
Thanks
Kevin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply