Home Forums SQL Server 2017 SQL Server 2017 - Administration Backing up multiple SQL instance and databases using 3rd party software - Any recommendations RE: Backing up multiple SQL instance and databases using 3rd party software - Any recommendations

  • Steve Jones - SSC Editor - Wednesday, November 22, 2017 4:36 PM

    Jeff Moden - Wednesday, November 22, 2017 2:34 PM

    You won't find better backup and faster restore times than the native methods.  3rd party backup tools just make it all a bit easier for people that need it to be easier.

    As for a "centralized software management tool" that does this, let me ask.... If you're in a DR situation and trying to get back in business quickly and restores are the only hope left to do so, what are you going to do if your  "centralized software management tool" was a casualty of whatever went wrong?

    That's not quite true. Compressed backups and restores can be faster, depending on your CPU/IO systems.

    As for central servers, SQL Backup can be easily setup  and instances added back. Or a restore of the system. We also include utilities in extreme cases to convert our compressed backups to native formats.

    SQL Server native backups (yes... I'm using 2016 Enterprise Edition and not everyone has the Enterprise Edition) does a fine job of using compressed backups and restores even to NAS if you have a good "pipe".  It's part of how I've been able to backup 2TB of databases in a single threaded fashion in just a couple of minutes over an hour.

    I also agree that SQL Backup is a fine product and can easily be setup and instances added back.  However, as with all such centrally controlled backups, if the box that the backups are on goes down, there is a time lag between that event and the time you can get a new instance online unless you have a hot standby ready and waiting (and a lot of people don't) capable of doing an automatic flop.  During that time and depending on the system resources, the databases involved, and the type of traffic the databases are exposed to, you can have several log file explosions in the time it takes to get your centralized backups working again.  If no log files explode, you may certainly have to go back to many of the systems to resize the log files back to "normal" because rebuilding the log file during a restore, even in the presence of instant file initialization, is a very expensive part of doing any restore.

    It would be interesting to do a head-to-head race of tuned native compressed backups with RG SQL Backup for marketing purposes.  Has RedGate done such a test and published such results somewhere?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)