SQL server 2008 Maintenance plan.
Hi EE,
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.
-backup/frequency
-Full/nightly
-Differential/every lunch time
-Transaction log/hourly everyday
-cleanup/once a week
-?
-?
Restore plan.
-----------------
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.
Thanks.
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.
-backup/frequency
-Full/nightly
-Differential/every lunch time
-Transaction log/hourly everyday
-cleanup/once a week
-?
-?
Restore plan.
-----------------
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.
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Above issue sorted by doing a full-backup and straight after running the transaction-logs maintenance plan.
Yes, log backups needs an already existing full backup.
ASKER
Thanks.
ASKER
BAC..." failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I'm getting a bit confused with the error message as I did backup the database in a separate plan and it is current. Any help on this?