500 SQL instance best practice to improve backup time

[justjuls]

Hello we have 500 SQL instances that we are running more than 20 hours.

We want to shorten the backup time for this SQL backup but not sure how we can improve it. I saw some technote that we will increase the BATCHSIZE however is there any bad effect on client server side if we increase it?

[sanket_pathak1]

@justjuls I am sure all 500 do not take 20 hours.

you can start identifying and tuning only instances that really run longer. You may want to validate what network speed/CPU/memory/disk  they are running with. Its also possible some of them route via a firewall. Try implementing Weekly Full / Daily Incr/Diff + log backups policy instead of Daily FULL + log backups.

There could be lot of other reason why they run longer.

I know its a time consuming process but worth doing it for the sake of reducing backup time.

Hope this helps.

[StefanosM]

500 SQL instances in one system or 500 SQL databases in one instance?

[justjuls]

500 SQL DB instances in one server

[admin_bkp007]

We have a server with 330 databases.
So the best solution our friend told above try config daily diff + weekly full.

[StefanosM]

I do not know how your backup is configured, but I think that there are two solutions to your problem.

• You can split your instances into many policies and run them in parallel, which will lower the backup time significantly. Try the data on the policies to be even. Not the number of databases, but the size of the databases.
  Unfortunately, finding the correct number of policies is a trial-and-error procedure, as parallel backups will put pressure on the SQL server.
• You can use incremental, as sanket_pathak1 and admin_bkp007 mention.

And of course, you can combine the two solutions in one. Configure seven policies with full and incremental backups. For the full backup, choose a different day of the week for every policy and run the incremental on every other day.