I'm trying to create a Maintenance plan that will work well for my environment. I'm new to SQL so any help/suggestion is most welcomed.
Here is what I have:
DB1 and DB2 are business critical databases. DB2 is tied to DB1. I can restore the two databases separately. Both DB1 and DB2 are hosted on a 2 node cluster.
I need a good backup plan so that I can restore from any point in time when needed. I expect to loose at the worst-case scenario only an hours work.
My tentative backup plan for now is below so help me have a better plan.
-Differential/every lunch time
-Transaction log/hourly everyday
-cleanup/once a week
If there is a disaster and I need to restore the database, I expect to only loose less than an hours worth of data.
What will be a good restore plan to achieve this? My backup plan should enable me to do this restore accordingly.
Now I do recognize that my transaction logs will get huge. How do I clean it up. What is a good time frame to clean up transaction log, considering my requirements. Can this be incorporated in the Maintenance plan.
All in all I want to have a plan where I can only afford to lose up to an hour of transaction in the worst case scenario.
Please advice on how go about this.