using Powershell scripts to do SQL backups

  • Siten0308

    SSCarpal Tunnel

    Points: 4334

    Hello,

    hopefully this can be a straight forward question, but at the same time, looking to get some advice and if possible experience from someone that tried this... but as the title says, My company has large databases, about 1.5 tb, and dont want to invest in software, but of course we do have good hardware, and trying to speed up the backup process.

    so, someone from the group suggested to create a powershell script that utilizes runspace, which spins up multiple threads to do the process. I am testing that as we speak, but so far doesnt seem to increase the backup performance and speed of shrinking down backup time (usually takes 10-15 hours). is Powershell/runspace good or can it actually take backups faster?

    Again, i am testing it now, and so far it seems to be stuck on 1 database at a time, though i do see multiple SPIDS on several databases, but, it seems using sp_whoisactive, i only see 1 database with percent_complete going up, again only 1 database, others are null, and are in suspended... very slow.

    I tried Ola hallengren, and that seems to increase speed, but i have to make multiple jobs and use Parallel=Y, new i guess feature from Ola Hallengren.

    So, the last question I have is, On Performance Monitor, what metrics can i use to see or should I use to measure to give me a good reading on which process works best?

    Also any other backup ideas anyone can throw out for me to try and increase backup times?

    thanks in advance 🙂

  • Phil Parkin

    SSC Guru

    Points: 244656

    Also any other backup ideas anyone can throw out for me to try and increase backup times?

    I'm sure that there are lots of ways to do that (eg, play Unreal Tournament on the server during the backup window), but I reckon you're really after a decrease here 🙂

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Siten0308

    SSCarpal Tunnel

    Points: 4334

    lol I was going to host a counter strike server on our SQL server, but I decided to wait till my movies were downloaded 😛

    but if you have any idea, if powershell is the best way to go, if and why i only see a percent for 1 database when running powershell/runspace, or anything else, it would be helpful 🙂

  • Phil Parkin

    SSC Guru

    Points: 244656

    I'll leave that one for the DBAs here. Counter Strike is a good call, that will do it!

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720432

    The best way to speed up backups is to stripe them. The SQL engine can then spew out pages to multiple locations. If you have good hardware, I'd look at that, rather than trying to somehow run multiple backups jobs at once.

    You mention 1.5TB, is that lots of dbs? Or a few?

    Also: http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/Technical%20Case%20Study-Backup%20VLDB%20Over%20Network_Final.docx

  • Siten0308

    SSCarpal Tunnel

    Points: 4334

    cool thanks Steve, Also we are on VM, so not sure if putting it on separate Drives would matter, but should i try doing it on configuring to backup separate backup files per database, or still use separate folder and files?

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720432

    Separate physical drives is mostly what you want. You want separate IO paths. Depending on your architecture, it may or may not help. The other place that diff files helps is with threading inside the OS.

    You have to test.

  • frederico_fonseca

    SSChampion

    Points: 14705

    depending on how many vcpu's you got (if only 2 vCpu use 2 files instead), but most times splitting the backup onto 4 files does help even if the files all end up on the same drive.

    just make sure it is not on the same drives where the databases themselves are.

  • TUellner

    SSCrazy

    Points: 2592

    If you want to do this in Powershell, you could use dbatools: https://docs.dbatools.io/#Backup-DbaDatabase

    But whether you use Ola's backup or Powershell, take a look at modifying the MaxTransferSize, BlockSize and BufferCount settings. I've been able to squeeze a little better performance out of backup with modifying those. Unfortunately, I don't have the links that I used as guidance when I first attempted this but I'm sure Google will come to your rescue for suggestions. You still have to play around with it to see what works best in your environment.

    Perhaps a combination of those with striping as Steve mentioned.

  • Jeffrey Williams

    SSC Guru

    Points: 88593

    I would look at other areas to improve performance - I am able to backup a 2TB+ database in less than an hour using the native backup and compressed setting.  If it is taking over 10 hours to backup your database the issue is most likely going to be the system, storage or network resources available.

    Are you backing up across the network to a UNC path by chance?  Are you backing up to the same volume (LUN) that contains the data files (in other words - are all drives just presented from the VM diskpool?).

    I know a lot of systems are setup to just use the VM storage - but for larger systems you should present individual drives to the guest instead of using that shared storage for your different needs.  For larger systems you should have at least 5 drives - one for the system databases, one for data files, one for transaction logs, one for tempdb - and a final one for backups.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply