June 18, 2015 at 9:33 am
I have 14 databases that I want to do a full backup on each morning around 2 am.
I created a maintenance plan to do this. I found out it is doing each database one by one. This has caused rotten network performance the backups are taking 8 - 12 hours to finish.
Other than create 14 separate maintenance plans, is there another way?
Thanks,
Mike
June 18, 2015 at 9:41 am
Sounds like you're saying that you're backing up to a location that is not on the SQL server and is on another box on the network.
If that is the case, I'd recommend you try backing up to the same machine and then copy/move the backup to the network location. Poor performance is often the case when backing up across the network.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 18, 2015 at 9:48 am
You can create your own backup jobs. Options are to have multiple jobs with one step for each database being backed up. For example, let's say you have 10 databases and want to use two jobs:
JobA: backs up DB1, DB3, DB4, DB6, DB9
JobB: backs up DB2, DB5, DB7, DB8, DB10
I'd suggest 'load balancing' the backups by size.
Another option is to create a table with the database names in it and assign each one a number which would equate to a job. So Job1 would backup all database where the JobNumber is 1.
Yet another option is to put all the database names into a temporary table, have the jobs get a database name, delete it from the table, and do the backup. Then you don't need to load balance as the jobs would pull the next database name in order until all the backups are done. Using this method means you won't miss backing up a database if you use one of the other options and forget to update either the job or table with a new database name. With this option you can set up a query to get all the database names every time the backup jobs run.
The best part about doing your own backup jobs is that you control what is being done and how it is getting done.
-SQLBill
June 23, 2015 at 4:29 am
You can also create a maintenance plan with two subplans executing at the same time to run as a parallel task. The side effect would be that you need to split target databases into the subplans and new databases won't be automatically added to your plan.
As a complex solution you can also edit your maintenance plan with BIDS and use advanced control flow features to add some parallelism or "intelligence" on your backup tasks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply