Backup policy(s) for multiple databases
-
Am looking to receive some input/suggestions on managing a backup policy for 300+ databases. They size of the databases vary from just a few MB to nearly 1 Gig. The dbs are used via web application where each application has at least two databases, with the max being around 25 databases (but there is no limit other than physical space).
I have found that the current full backup process exceeds 2 hours and the transaction log backup nears one hour.
A 30 - 60 minute loss of data is affordable.
Using 100 databases I conducted a quick experiment: I created five maintenance plans that include a transaction log backup with a 1 hour interval. Using task manager (I know that's not the best tool) I showed 100% CPU during the execution of these jobs.
Can anyone please shed some insight through experiences that will help me with creating a workable policy?
-
Are all these databases on one server?
If you perform an hourly transaction log backup, and it takes nearly an hour, maybe you should look a doing them more often, or determining why they take so long. Do you really have that much activity?
I would still use the built in maintenance plans. Setup different plans for the database backup and transaction log backup. Don't include any optimisations in the transaction log backups. Then stagger the execution times throughout the hour.
eg: backup databaseA and databaseB on the hour, backup databaseC and databaseD 15mins past the hour, etc...
This will help spread the load out a bit.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
Edited by - phillcart on 10/06/2003 5:32:25 PM
--------------------
Colt 45 - the original point and click interface
-
A 1 hour log backup is a lot. I typically like to see it under a couple mins. Are you under a time constraint for the full backup? If not, I'd just use one job/plan and let it run.
Andy
-
Thanks guys.
Yes, these are all on one server.
The activity for the server is primarily deletes and bulk inserts, though there are manual entries of data through the web app.
The full backup time is less concerning than the transaction log backup time. The web app is primarily used during 'business hours,' so late night backups can run without affecting performance or needs.
I've established four hourly trans log backups that I will monitor throughout the day, each starting at the quarter hour.
Thanks for the input.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply