Regarding Backup and Restore Strategy

Question

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?

Answer 1

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.

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.

Answer 3

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).