Creating Split backups Question

  • Lee Hopkins

    Right there with Babe

    Points: 779

    When creating a split backup is it required to have each of the splits go to different path, or can i have them all go to the same location?

  • John Mitchell-245523

    SSC Guru

    Points: 148519

    You mean a striped backup?  No, they don't have to go to different paths.  Depending on your disk configuration, though, your backup may run faster if they do.

    John

  • Lee Hopkins

    Right there with Babe

    Points: 779

    Thank you.

  • Jeff Moden

    SSC Guru

    Points: 995462

    Be advised that if you cannot guarantee that the individual files are guaranteed to go to separate physical spindles (if you're backing up to spinning rust), striped backups can and frequently ("usually" is what my personal observations have been) will take longer (sometimes, a LOT longer) that a single file backup.

    If you have SSDs, you don't run into that problem but there's also no real advantage there, either.  It could still take longer if your SSDs are setup as "NAS" because the "pipe" becomes the limiting factor there.

    But, as Wernher von Braun is quoted as saying, "One good test is worth a thousand expert opinions"... especially since all of this depends a whole lot on the infrastructure of the whole shootin' match.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • John Mitchell-245523

    SSC Guru

    Points: 148519

    Jeff Moden wrote:

    Be advised that if you cannot guarantee that the individual files are guaranteed to go to separate physical spindles (if you're backing up to spinning rust), striped backups can and frequently ("usually" is what my personal observations have been) will take longer (sometimes, a LOT longer) that a single file backup.

    If you have SSDs, you don't run into that problem but there's also no real advantage there, either.  It could still take longer if your SSDs are setup as "NAS" because the "pipe" becomes the limiting factor there.

    But, as Wernher von Braun is quoted as saying, "One good test is worth a thousand expert opinions"... especially since all of this depends a whole lot on the infrastructure of the whole shootin' match.

    Striped backups can go parallel, whereas single-file backups can't (as far as I know) so there may be a performance advantage there.  But like you said, Jeff, you won't know until you test.

    John

  • Lee Hopkins

    Right there with Babe

    Points: 779

    The main reason i am using the strip backup if because i am transferring the bak and the main db was 12g bak and our share point was being a pain so i striped it to 3 and had no problem.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717276

    Make sure you have all the stripes. You need them all for a restore.

  • Lee Hopkins

    Right there with Babe

    Points: 779

    yep, learned the hard way forgot #3 of 4 lol

  • frederico_fonseca

    SSChampion

    Points: 14297

    from my personal testing over the years splitting onto 4 files even if they are on the same destination disk will normally yield best results than 1 or 2 files - more than 4 depends on other factors.

    As people will ask.. following was done today in 2 of my servers db files on one drive, backup files on a different drive.

    3 different databases (diff sizes) 5 backups done - 1 , 2, 4, 6 and 8 files respectively, 2 runs each

    server with 8 cores - 300 GB ram, xTremeIO V2 storage
    DB 1
    backup database size = 72.30 GB, test = case 1, run = 1, bak files = 1 processed 3097857 pages in 97.884 seconds (247.251 MB/sec).
    backup database size = 72.30 GB, test = case 1, run = 2, bak files = 1 processed 3097857 pages in 97.876 seconds (247.272 MB/sec).

    backup database size = 72.30 GB, test = case 1, run = 1, bak files = 2 processed 3097857 pages in 44.391 seconds (545.200 MB/sec).
    backup database size = 72.30 GB, test = case 1, run = 2, bak files = 2 processed 3097857 pages in 48.430 seconds (499.731 MB/sec).

    backup database size = 72.30 GB, test = case 1, run = 1, bak files = 4 processed 3097857 pages in 31.764 seconds (761.931 MB/sec).
    backup database size = 72.30 GB, test = case 1, run = 2, bak files = 4 processed 3097857 pages in 29.518 seconds (819.906 MB/sec).

    backup database size = 72.30 GB, test = case 1, run = 1, bak files = 6 processed 3097857 pages in 22.236 seconds (1088.415 MB/sec).
    backup database size = 72.30 GB, test = case 1, run = 2, bak files = 6 processed 3097857 pages in 22.443 seconds (1078.376 MB/sec).

    backup database size = 72.30 GB, test = case 1, run = 1, bak files = 8 processed 3097857 pages in 33.795 seconds (716.141 MB/sec).
    backup database size = 72.30 GB, test = case 1, run = 2, bak files = 8 processed 3097857 pages in 31.654 seconds (764.579 MB/sec).

    DB 2
    backup database size = 66.55 GB, test = case 2, run = 1, bak files = 1 processed 4247609 pages in 114.931 seconds (288.733 MB/sec).
    backup database size = 66.55 GB, test = case 2, run = 2, bak files = 1 processed 4247609 pages in 95.704 seconds (346.740 MB/sec).

    backup database size = 66.55 GB, test = case 2, run = 1, bak files = 2 processed 4247609 pages in 39.617 seconds (837.631 MB/sec).
    backup database size = 66.55 GB, test = case 2, run = 2, bak files = 2 processed 4247609 pages in 60.939 seconds (544.551 MB/sec).

    backup database size = 66.55 GB, test = case 2, run = 1, bak files = 4 processed 4247609 pages in 29.842 seconds (1112.004 MB/sec).
    backup database size = 66.55 GB, test = case 2, run = 2, bak files = 4 processed 4247609 pages in 28.237 seconds (1175.211 MB/sec).

    backup database size = 66.55 GB, test = case 2, run = 1, bak files = 6 processed 4247609 pages in 25.671 seconds (1292.681 MB/sec).
    backup database size = 66.55 GB, test = case 2, run = 2, bak files = 6 processed 4247609 pages in 23.857 seconds (1390.972 MB/sec).

    backup database size = 66.55 GB, test = case 2, run = 1, bak files = 8 processed 4247609 pages in 23.871 seconds (1390.157 MB/sec).
    backup database size = 66.55 GB, test = case 2, run = 2, bak files = 8 processed 4247609 pages in 22.110 seconds (1500.879 MB/sec).

    DB 3
    backup database size = 43.23 GB, test = case 3, run = 1, bak files = 1 processed 5189745 pages in 105.750 seconds (383.403 MB/sec).
    backup database size = 43.23 GB, test = case 3, run = 2, bak files = 1 processed 5189745 pages in 113.228 seconds (358.081 MB/sec).

    backup database size = 43.23 GB, test = case 3, run = 1, bak files = 2 processed 5189745 pages in 73.210 seconds (553.816 MB/sec).
    backup database size = 43.23 GB, test = case 3, run = 2, bak files = 2 processed 5189745 pages in 57.910 seconds (700.136 MB/sec).

    backup database size = 43.23 GB, test = case 3, run = 1, bak files = 4 processed 5189745 pages in 32.390 seconds (1251.771 MB/sec).
    backup database size = 43.23 GB, test = case 3, run = 2, bak files = 4 processed 5189745 pages in 40.996 seconds (988.995 MB/sec).

    backup database size = 43.23 GB, test = case 3, run = 1, bak files = 6 processed 5189745 pages in 31.525 seconds (1286.118 MB/sec).
    backup database size = 43.23 GB, test = case 3, run = 2, bak files = 6 processed 5189745 pages in 27.271 seconds (1486.739 MB/sec).

    backup database size = 43.23 GB, test = case 3, run = 1, bak files = 8 processed 5189745 pages in 22.595 seconds (1794.418 MB/sec).
    backup database size = 43.23 GB, test = case 3, run = 2, bak files = 8 processed 5189745 pages in 27.328 seconds (1483.638 MB/sec).

    Server with 12 cores - 64GB ram - EMC Vnx storage
    DB 1
    backup database size = 84.65 GB, test = case 1, run = 1, bak files = 1 processed 10918553 pages in 492.992 seconds (173.027 MB/sec).
    backup database size = 84.65 GB, test = case 1, run = 2, bak files = 1 processed 10918553 pages in 536.338 seconds (159.043 MB/sec).

    backup database size = 84.65 GB, test = case 1, run = 1, bak files = 2 processed 10918553 pages in 401.909 seconds (212.240 MB/sec).
    backup database size = 84.65 GB, test = case 1, run = 2, bak files = 2 processed 10918553 pages in 377.671 seconds (225.861 MB/sec).

    backup database size = 84.65 GB, test = case 1, run = 1, bak files = 4 processed 10918553 pages in 307.046 seconds (277.812 MB/sec).
    backup database size = 84.65 GB, test = case 1, run = 2, bak files = 4 processed 10918553 pages in 311.036 seconds (274.248 MB/sec).

    backup database size = 84.65 GB, test = case 1, run = 1, bak files = 6 processed 10918553 pages in 299.390 seconds (284.916 MB/sec).
    backup database size = 84.65 GB, test = case 1, run = 2, bak files = 6 processed 10918553 pages in 351.135 seconds (242.929 MB/sec).

    backup database size = 84.65 GB, test = case 1, run = 1, bak files = 8 processed 10918553 pages in 295.205 seconds (288.955 MB/sec).
    backup database size = 84.65 GB, test = case 1, run = 2, bak files = 8 processed 10918553 pages in 382.069 seconds (223.261 MB/sec).

    DB 2
    backup database size = 78.55 GB, test = case 2, run = 1, bak files = 1 processed 5914538 pages in 461.714 seconds (100.077 MB/sec).
    backup database size = 78.55 GB, test = case 2, run = 2, bak files = 1 processed 5914538 pages in 462.626 seconds (99.880 MB/sec).

    backup database size = 78.55 GB, test = case 2, run = 1, bak files = 2 processed 5914538 pages in 381.355 seconds (121.166 MB/sec).
    backup database size = 78.55 GB, test = case 2, run = 2, bak files = 2 processed 5914538 pages in 313.254 seconds (147.507 MB/sec).

    backup database size = 78.55 GB, test = case 2, run = 1, bak files = 4 processed 5914538 pages in 313.289 seconds (147.491 MB/sec).
    backup database size = 78.55 GB, test = case 2, run = 2, bak files = 4 processed 5914538 pages in 360.539 seconds (128.161 MB/sec).

    backup database size = 78.55 GB, test = case 2, run = 1, bak files = 6 processed 5914538 pages in 278.769 seconds (165.754 MB/sec).
    backup database size = 78.55 GB, test = case 2, run = 2, bak files = 6 processed 5914538 pages in 259.328 seconds (178.180 MB/sec).

    backup database size = 78.55 GB, test = case 2, run = 1, bak files = 8 processed 5914538 pages in 259.239 seconds (178.242 MB/sec).
    backup database size = 78.55 GB, test = case 2, run = 2, bak files = 8 processed 5914538 pages in 264.809 seconds (174.493 MB/sec).

    DB 3
    backup database size = 38.33 GB, test = case 3, run = 1, bak files = 1 processed 1596394 pages in 107.363 seconds (116.164 MB/sec).
    backup database size = 38.33 GB, test = case 3, run = 2, bak files = 1 processed 1596394 pages in 105.127 seconds (118.635 MB/sec).

    backup database size = 38.33 GB, test = case 3, run = 1, bak files = 2 processed 1596394 pages in 103.137 seconds (120.924 MB/sec).
    backup database size = 38.33 GB, test = case 3, run = 2, bak files = 2 processed 1596394 pages in 81.863 seconds (152.349 MB/sec).

    backup database size = 38.33 GB, test = case 3, run = 1, bak files = 4 processed 1596394 pages in 80.178 seconds (155.551 MB/sec).
    backup database size = 38.33 GB, test = case 3, run = 2, bak files = 4 processed 1596394 pages in 82.614 seconds (150.964 MB/sec).

    backup database size = 38.33 GB, test = case 3, run = 1, bak files = 6 processed 1596394 pages in 81.441 seconds (153.139 MB/sec).
    backup database size = 38.33 GB, test = case 3, run = 2, bak files = 6 processed 1596394 pages in 74.138 seconds (168.224 MB/sec).

    backup database size = 38.33 GB, test = case 3, run = 1, bak files = 8 processed 1596394 pages in 90.765 seconds (137.407 MB/sec).
    backup database size = 38.33 GB, test = case 3, run = 2, bak files = 8 processed 1596394 pages in 77.376 seconds (161.184 MB/sec).

  • Jeff Moden

    SSC Guru

    Points: 995462

    John Mitchell-245523 wrote:

    Jeff Moden wrote:

    Be advised that if you cannot guarantee that the individual files are guaranteed to go to separate physical spindles (if you're backing up to spinning rust), striped backups can and frequently ("usually" is what my personal observations have been) will take longer (sometimes, a LOT longer) that a single file backup.

    If you have SSDs, you don't run into that problem but there's also no real advantage there, either.  It could still take longer if your SSDs are setup as "NAS" because the "pipe" becomes the limiting factor there.

    But, as Wernher von Braun is quoted as saying, "One good test is worth a thousand expert opinions"... especially since all of this depends a whole lot on the infrastructure of the whole shootin' match.

    Striped backups can go parallel, whereas single-file backups can't (as far as I know) so there may be a performance advantage there.  But like you said, Jeff, you won't know until you test.

    John

    Just as with queries, there is sometimes a hidden cost in going parallel and it does "Depend" on a whole lot of factors.  I worked with one company about 2 years ago where they were using 4 striped files for their backups.  It was a "long haul" from an offsite 3rd party provider of equipment (this wasn't a "Cloud") to on premise (and I'm not including any restore time here) and the amount of time the backup was taking was horrible.  Reverting to single file cut the backup time to about 1/8th the time.

    As we've just seen from frederico_fonseca, he got much different results.

    So, yeah... definitely have to test.  There is no panacea here.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • Jeff Moden

    SSC Guru

    Points: 995462

    frederico_fonseca wrote:

    from my personal testing over the years splitting onto 4 files even if they are on the same destination disk will normally yield best results than 1 or 2 files - more than 4 depends on other factors.

    Totally awesome post!  Thank you very much for taking the time to gather such information and post it!

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • ScottPletcher

    SSC Guru

    Points: 98285

    If you're also looking into overall performance, don't neglect to specify both BUFFERCOUNT and MAXTRANSFERSIZE.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • frederico_fonseca

    SSChampion

    Points: 14297

    ScottPletcher wrote:

    If you're also looking into overall performance, don't neglect to specify both BUFFERCOUNT and MAXTRANSFERSIZE.

    Yup - I did that in a few cases where squeezing any amount of time was paramount - for most cases the effort to try and get the right combination is just not worth the hassle.

  • ScottPletcher

    SSC Guru

    Points: 98285

    frederico_fonseca wrote:

    ScottPletcher wrote:

    If you're also looking into overall performance, don't neglect to specify both BUFFERCOUNT and MAXTRANSFERSIZE.

    Yup - I did that in a few cases where squeezing any amount of time was paramount - for most cases the effort to try and get the right combination is just not worth the hassle.

    I've seen big improvements, up to a point.  Beyond 20 (or 30?  been awhile, can't remember for sure) buffers didn't seem to gain anything worthwhile, no matter how large the db.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Jeff Moden

    SSC Guru

    Points: 995462

    ScottPletcher wrote:

    If you're also looking into overall performance, don't neglect to specify both BUFFERCOUNT and MAXTRANSFERSIZE.

    Totally agreed on that.  I use both.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

Viewing 15 posts - 1 through 15 (of 24 total)

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