Designing disaster recovery from scratch
Please let me know Best practices for backup/restore point in time? should i use t-sql, cmd, powershell, sql jobs? Where to store the files/retention period/frequency?
All of the answers to your questions depend on what your users/company/compliance need.
Rather than doing from scratch, you might consider using some well-proven backup/restore software, e.g. Ola Hallegren's backup scripts, Minion backup etc.
If you are in a VM environment, there may be other options as well, e.g. Veeam for VMVare.
Regardless of what technology you use, one of the most important things to do is to exercise and document your restore procedure. It would be very stressful if you are trying to learn how to restore after a disaster has happened, or even worse, if you find out then that your backups are no good - corrupted, or what have you.
I dont like to use someones code cause if i dont know how to use it i may screw up and Im also not looking for a third party software. I just want to design mine from scratch. But i need some ideas, i will like to know people who has experience designing this can chime in
Ahh "the not invented here" syndrome
Do what @JamesK says, look at Ola Hallegren's work.
Reading through the scripts and you'll get an idea of how to design yours from scratch.
You'll also then realise that you wont be able to do as good a job as the Ola scripts and have enough understanding that you (like 1000's of other people, including me) will be able to use/rely on them.
2 Likes