2

I have some questions regarding backup and restore strategies.

I want to take backup and restore of large databases approx. 60 to 70 GB and it is a critical database on SQL Server.

Backup schedule approx. I decided to:

  • Full backup every day
  • Differential backup every 2 or 3 hours
  • Transaction-log backup every 15 minutes

Is this a correct approach? If not then please suggest better option.

Which recovery model is best suited? (Simple, Full, Bulk-logged)

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

  • 5
    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
2

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 | |
2

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

It looks fine, your backups should depend on your RPO and RTO, which should be set by the business. Often when no backups are in place backups will be set up like:

  • Full backup once a week
  • Diff backup once a day
  • Log backup every 15 minutes

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

Simple should be used when you don't want to manage your transaction log + you don't need a point in time restore. (No log backups)
Full should be used when you need point in time recovery.
I've personally never used 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?

Try to find the last backup without corruption, and restore that one. It's a lot easier to prevent corruption, than to solve it.
This is why it's important to do DBCC CHECKDBs on your databases regularly.(Using Ola Hallengren scripts for example).

Personally I do a FULL backup and after that I'll do a checkdb, that way I'm sure that the backup that I have is a correct one, without any corruption.

| improve this answer | |
2

I want to take backup and restore of ... a critical database on SQL Server.

Let's start at the right end of this story ...

When your database explodes in a ball of flame:

  1. How long will you take to get it back up and running (Recovery Time Objective), and
  2. How much data is your Business prepared to lose while you go about doing so (Recovery Point Objective).

Both of these need terms to defined in collaboration between you and your Business (you cannot do this on your own) and set in stone in your [company's] Recovery Strategy.

Question: Which word is conspicuously missing from all of the above?
Answer: "backup".

Being able to recover the database is what matters.

How you go about doing so does not.

That said, it's 99.9% likely that you'll use a database-appropriate backup tool to implement your Recovery Strategy.

Is this a correct approach?

We cannot tell you.
Only your Business can do that.

In case any of the backup files ... are corrupted ...

That's why you perform regular Recovery Rehearsals so that

  1. You understand and are practised in executing the Recovery process (you don't want to be trying to work this out at 2 am with Senior Management breathing down your neck!),
  2. You can measure just how long it will take to carry out a Recovery (which can, of course, change over time), and
  3. You ensure that your backup files are actually usable (before it becomes job-threatening to find out that they're not).
| 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.