5

I have created 5 maintenance plans on my SQL server (say 50 !) which generate databse backup 5 times daily. As I read in this Q/A the full recovery is good when I need Point-in-time recovery.

My exact question: Is generating multiple daily backup with simple recovery model, similar to full recovery backup with longer period?

  • When you say daily backup is it full , differential or transaction log ? – Shanky May 23 at 7:59
  • 1
    @Shanky given he's specified it is in SIMPLE recovery, it can only be DIFF or FULL – George.Palacios May 23 at 8:01
  • 6
    What the answer in that q says: " You should use simple recovery model when you don't need point-in-time recovery of your database, and when the last full or differential backup is sufficient as a recovery point." – ypercubeᵀᴹ May 23 at 8:43
15

Setting a database to full recovery is not enough by itself to ensure you have point in time recoverability. You also need to take regular log backups. You should also be taking time regularly to test that you personally know how to execute the required commands to actually perform a restore to an arbitrary point-in-time.

In lieu of running loads of home brewed full backups, the fire-and-forget solution you want is probably to just install Ola Hallengren's backup solution. Running this script installs the required objects and deploys the required jobs to manage all of the full recovery databases on an instance. You will find these jobs 1 under SQL Server Agent > Jobs named by default DatabaseBackup - USER_DATABASES ... and similar. Add a schedule to these jobs that suits you 2 and disable other backup jobs to prevent conflicts.

You should still need to regularly take time in the normal course of business to ensure you and others on your team are able to actually restore the backups you have, regardless of how you take them.


1: assuming you're using SSMS here

2: 5 times daily if you like, but probably a bit less is fine

8

I have created 5 maintenance plans on my SQL server (say 50 !) which generate database backup 5 times daily.

Why not just create one maintenance plan and run it five times.

My exact question: Is generating multiple daily backup with simple recovery model, similar to full recovery backup with longer period?

Full backups or differential backup, no matter in what recovery model you take, are same in terms on what they backup. Now I believe you have combination of full and differential backups and my answer is it all depends on on RPO and RTO (how much data you are willing to loose in case of disaster). A DB in simple recovery model which can afford some data loss and does not needs point in time recovery would just do fine with full and differential backups.

The other important thing is you make sure you restore the backup files on other machine to see that you meet your recovery objective

  • 1
    Absolutely this. It's all about how much data you can afford to lose. If you're doing 5 backups daily, spread evenly, you could lose up to 5 hours of data. Even if you've got them all running during business hours, that still ~10/5 ~2 hours of data you could lose. If you're ok with losing 2 hours of data, then go for it. If you don't need Point-In-Time restore, then that's also fine – Zac Faragher May 24 at 0:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.