Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transferring Backups


Transferring Backups

Author
Message
ALIF-662928
ALIF-662928
Right there with Babe
Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)

Group: General Forum Members
Points: 765 Visits: 743
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-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36107 Visits: 18742
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
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3254 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
Right there with Babe
Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)

Group: General Forum Members
Points: 765 Visits: 743
Yeah we tried to copy the files into 15 smaller files,
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3254 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
SSChasing Mays
SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)

Group: General Forum Members
Points: 632 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
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 858
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
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 858
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