After much digging I found quite a few articles on backup plans and strategies. None gave an actual plan, but
rather talked about what questions need to be answered to construct an appropriate plan for the given
business. One major component of the plan is the schedule, which states when you backup, and what you
backup at that “when”.
Microsoft Azure has a predefined schedule, so that was probably the closes I got to a stated plan. They do full
backups every week, differentials every 12 hours, and logs every 5-10 minutes. This seems to be a fairly
comprehensive plan. They also have a Long Term Retention option where they would hold the full backups for
10 years. Retention is another component of a backup plan that some articles mentioned.
Other than Microsoft’s Azure defaults, no one gave an actual plan. After reading what makes up a plan, I
understand why; it depends on the business. The information given for SmartHomes doesn’t allow me to
answer all the questions needed to construct a comprehensive plan, but they give active hours and this helps
with the schedule. Based on the information given, I’d suggest:
A full backup can be done on Sundays at 1am.
Differential backups daily at 10pm.
Transaction log backups are also a good idea, but I’m not sure how to decide on that just yet. The 99.99%
uptime requirement points to a high availability solution, which doesn’t seem to be part of a backup strategy. To
summarize my findings, a backup strategy covers:
What tool to use – SSMS is one of the tools that can be used, but some articles suggested coding that runs
outside SSMS. Azure backups are provided as part of their database plan.
What to backup – This can be full database, changes or logs, or types of data e.g. archived data vs active.
When to back up – backups impact the system performance so off-peak hours are suggested. But this must be
balanced with what recovery requirement is needed.
Backup verification and testing – this is making sure the data is properly backed up and that it can be restored.
Location of the backup – its recommended not to use the same storage device as the database being backed
up.