Transferring Backups

  • ALIF-662928

    SSCertifiable

    Points: 6054

    we need to copy a backup file between two very slow network Data centers (transfer rate: 350 kbps),

    Our full bak file around 200 GB is taking around 9 days to copy to the other side. Tape transfer is also not possible between this location.

    My question is how to Recover the DB on the other side after 9 days lag,

    Database Recovery Model: Full

    Full backup size: 200GB

    Differential backup file is 15 GB every 4 hours,

    Transactional log backup for every hour is around 200MB,

    why is it so, when the full backup is 200gb and Differential backup is 15GB each,

    what is the best i can get the data to the other side? how to minimize the Diff/transaction backup size or remove them and get one or two t-log backups with min size.

    Any Suggestions will be of great help.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719720

    You can remove the diff, but that won't necessarily change the time, depending on logs. You'll have more logs to restore.

    Best way: copy the full to an external HDD and have someone take it to the other center. UPS/ FedEx, etc. Then once that's done, or in progress, take a diff and start that transferring, or log backups. You'll have to get all logs moved up to the point in time you want to recovery until.

    There's no magic way to get more data through a slow pipe, or remove data from a restore sequence.

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    If you are not using one, I suggest installing a backup compression product, like Litespeed or Redgate backup, to make the files smalller. Or as an alternative, use a compression product (zip, rar, etc.) to compress the full backup as smalll as possible.

    Also, make the full backup output multiple files, 5 or more, so that you can copy them over the network in parallel. That seems to do a better job of keeping the pipe full than copying one big file.

  • ALIF-662928

    SSCertifiable

    Points: 6054

    Yeah we tried to copy the files into 15 smaller files,

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    ALIF-662928 (9/4/2012)


    Yeah we tried to copy the files into 15 smaller files,

    Did you compress the files?

    What was the total size of the compressed backup files?

    Did you copy multiple files in parallel? How many did you copy in parallel?

    When you say 350 kbps, do you mean Kilobytes/second, not Kilobits/second?

  • naga.rohitkumar

    SSCertifiable

    Points: 5470

    1.as you mentioned 9 days lag why t is delay??

    is the rescoucre available frequently or not to monitor every day by day through which we can identify the issue

    if there is a delay of 9 days then what about the data stored at present

    2.is any users connected to the particular database ? ? or no work is performing in the database ??

    like steve said it is one option [ but it will take time and expensive, and we have to restore the backup as soon as possible most of them go for this option at last ] and another is in the case of urgent scenario

    option [1]

    1.first create a backup device and split the files as per ur backup size

    2. create a maintenance plan and add all the device backups and run the device backup maintenance plan

    3. after successful execution of backup manually copy the split backup files which u created in the source server in to the destination server in which u have to restore the database

    option [2]

    go for ftp or through unix box transfer the backup but both of them may cause break in network transfer mostly option[1] is better in my view

    Thanks
    Naga.Rohitkumar

  • SqlSavy

    SSCarpal Tunnel

    Points: 4065

    what is the tool you are using to copy the file across is it xcopy or robocopy? robocopy is faster and efficient.

  • stefan.e.schulz

    Valued Member

    Points: 66

    hello,

    there is an other tool you can use for transfering these amounts of data.

    using rsync shouzld help you to transmit your data faster tham using xcopy or robocopy.

    rsync was designet to transfer huge amounts of data via p.o.t.s.

    see rsync on wikipedia

  • SqlSavy

    SSCarpal Tunnel

    Points: 4065

    I don't have rsync installed on my servers while the Resource Kit with robocopy is often installed (or at least it's on an "approved" list of software that can be installed on a production system).

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

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