question

kpsenthilkumar86 avatar image
kpsenthilkumar86 asked

Need to know appropriate Recovery Model

Dear All, We are having full backup for all our databases on daily basis. We kept our databases in Full Recovery Model. But, there is no log / differential backup and no Log shipping/Mirroring. Is "Full recovery Model" the appropriate recovery model for our setup? If the database is in "Full Recovery Model", will it be helpful at any moment when the database is corrupted/inaccessible If "Simple Recovery Model" is enough, Please let us know the reason. Thanks & Regards, K.P.Senthil Kumar
sqlserverrecovery
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
In Full Recovery you must take a log backup on a regular basis or your log file will grow and fill the drive it's on. In order to arrive at the correct recovery model, you need to talk to your business to understand the amount of data they are willing to lose. If you are in simple recovery and you only take a full backup once a day, then you can lose up to a full day's worth of data. So, you can add a differential, say every 6 hours, now, you can lose up to 6 hours of data. But, let's say the business says you need to be able to only lose 5 minutes of data. Then, you need to turn on Full Recovery and start running log backups every 5 minutes, all day long. But there's no way to determine the correct recovery model until we know what the recovery point objective (RPO) will be. This is an [introduction to backups][1] article that I've recently updated. It might help. [1]: https://www.simple-talk.com/sql/backup-and-recovery/sql-server-2014-backup-basics/
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If you are using Full recovery model and need to restore a database, you can - as you say - take a tail log backup first, then restore the full backup and all log backups taken after the full backup, including the tail log backup, and restore up to a certain point in time. When using simple recovery model, you can only restore the full backup and the changes which occured after the full backup will be lost.
1 Like 1 ·
Thank you so much Grant Fritchey. Let me speak with my manager.
0 Likes 0 ·
Dear Grant Fritchey, We need your clarification in the below scenario also. Our database is configured under "Full Recovery Model" and I am having a full backup of last night. Today, our database is corrupted. In this scenario, will the "Full recovery model" be helpful for us. (ie) can we take any tail log backup and use it with yesterday's night full backup. Thanks in advance K.P.Senthil Kumar
0 Likes 0 ·
Thanks for reply Magnus ahlkvist
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.