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:
- How long will you take to get it back up and running (Recovery Time Objective), and
- 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?
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
- 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!),
- You can measure just how long it will take to carry out a Recovery (which can, of course, change over time), and
- You ensure that your backup files are actually usable (before it becomes job-threatening to find out that they're not).