Forum Discussion

justjuls's avatar
justjuls
Level 3
2 years ago

500 SQL instance best practice to improve backup time

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's avatar
    sanket_pathak1
    Level 4
    2 years ago

    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's avatar
    StefanosM
    Level 6
    2 years ago

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

      • admin_bkp007's avatar
        admin_bkp007
        Level 5
        2 years ago
        We have a server with 330 databases.
        So the best solution our friend told above try config daily diff + weekly full.
  • StefanosM's avatar
    StefanosM
    Level 6
    2 years ago

    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.