SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transferring Backups


Transferring Backups

Author
Message
ALIF-662928
ALIF-662928
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1015 Visits: 762
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
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62364 Visits: 19102
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5726 Visits: 11771
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
ALIF-662928
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1015 Visits: 762
Yeah we tried to copy the files into 15 smaller files,
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5726 Visits: 11771
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
naga.rohitkumar
SSC Eights!
SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)

Group: General Forum Members
Points: 852 Visits: 1378
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
Sqlsavy
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 862
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
stefan.e.schulz
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 79
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
Sqlsavy
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 862
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).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search