0

Everyone

I have some queries regarding backup and restore strategies.

I want to take Backup and Restore of Large Database approx. 60 to 70 GB and it is critical database in SQL Server.

Backup schedule approx. i decided,

  • Full Backup every day
  • Differential Backup every 2 or 3 hour
  • Transaction-log Backup every 15 minute

Is this correct approach? if not then please suggest better option?

Which Recovery model is best suits ? (Simple, Full, Bulk-logged)

In case any of backup file(full backup file, differential backup file or log backup) has corrupted then what are the options to recover database quickly with minimum data loss?

THANKS in Advance...

|improve this question
  • 3
    Backup plan should be as per RPO and RTO agreed. Why are you asking us to tell you what backup strategy you should have. Your backup plan seems OK – Shanky May 10 '16 at 9:34
  • Thanks Shanky, In case any of backup file(full backup file, differential backup file or log backup) has corrupted then what are the options to recover database quickly with minimum data loss? – Dharmesh Jayani May 10 '16 at 9:42
  • If backup is corrupt you are mostly out of luck. Make sure after backup completes you restore it on other machine and if restore is successful the backup is complete in all format. Also verify integrity of backup when you take it you should strive to make sure backup is consistent. Make sure you run checkdb as frequently as possible on databases. Read Recovering From disaster Using Backups] – Shanky May 10 '16 at 9:51
  • You won't be able to take T-Log backups if you run the database under Simple recovery model. So that's out of the possible options for you. As @Shanky alludes to, no way can anyone on here comment on whether this is the correct approach. Is it a sensible approach? Well it sure seems it. But I would suggest reviewing your data recovery SLA's internally and devising the best process from that. – Molenpad May 10 '16 at 10:41
0

Your backup plan seems to be good. Of course, everything depends on:

Recovery Point Objective (RPO) in other words – how much data can you afford to lose?

And

Recovery Time Objective (RTO) if database disaster occurs, how much time are you able to spend restoring a database to its working condition?

Note, if you will run your database under the simple recovery model you will not able to make transaction log backups.

|improve this answer

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.