Post reply

copy files through network to another server

  • April 17, 2011 at 6:35 pm

    #237851

    I'm planning to upgrade sql server 2005 to 2008. For now I'm just planning on it. If we use side by side upgrade, we will have to either copy data files to another server, or copy backup files to another server.

    They are quite large size files. What are the best way to copy them? Currently, I only know manually use copy then paste to another \\serverB\$D\Data or \\serverB\$D\backup. Any other way of doing that for faster copy?

    Thanks

  • April 17, 2011 at 9:16 pm

    #1312581

    Restore from tape maybe?

    --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.

    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)

  • April 18, 2011 at 2:09 am

    #1312627

    Jeff Moden (4/17/2011)

    Restore from tape maybe?

    Of course, if they don't have the tape drive directly connected to the SQL server itself (or SAN, if they're using one) that would just come back to a network transfer under a different name... 🙂

  • April 18, 2011 at 5:11 am

    #1312676

    That, most definitely, would be true. 🙂

    --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.

    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)

  • April 18, 2011 at 7:11 am

    #1312728

    I have helped several clients with this and often the fastest mechanism is USB drive(s). If you do have multiple USB 2.0 ports on separate busses on the server you can get good very good transfer rates. Write out your backups to a number of files equal to the USB drive count. If you are at the high end I have even had companies buy cheapo direct attached storage and moved files that way. Sneakernet can be WAY faster than the existing network, and it doesn't affect existing operations either.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • April 18, 2011 at 8:08 am

    #1312814

    I had to lookup Sneakernet on Wiki , already anticipating installing something ultra fast on my network;

    That one made my day.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 18, 2011 at 10:46 am

    #1312954

    The total size files are about 100 gb.

    Can I use RoboCopy, never used it before, just heard it is faster?

    Do I need to download something in order to run the command, and what is the syntax to copy all the files from one server to another?

    Thanks

  • April 19, 2011 at 1:36 am

    #1313195

    I can't see any reason why Robocopy would be any faster than using a normal copy from a command prompt--it's not like it compresses the data it's copying or anything like that! It might be a bit faster than copying using the GUI, but even then, probably not enough of a difference to be worth worrying about.

    How big are the files we're talking here, anyway?

  • April 19, 2011 at 9:35 am

    #1313448

    The total size may be around 100gb.

  • April 19, 2011 at 10:59 am

    #1313525

    If you have limited bandwith, you could zip the backup to make it smaller.

    Keep in mind, it will take some time to zip and unzip the backup, so this only really saves you time if you have limited bandwidth.

  • April 19, 2011 at 11:02 am

    #1313528

    I think we have the regular bandwith just as other companies have.

    Not sure how long it will take to copy 100gb files.

  • April 19, 2011 at 11:19 am

    #1313537

    We're on a rather fast san here (48 1TB drives 15 K rpm.) It would take over 100 minutes to copy 100 GB.

    One way to do it is to move the full backup whenever you have time. Then take a diff backup (will be 1-5 GB at the most). Restore those 2 files. Then take a tail log backup and finish the restore with it.

    That can also give you extremely small downtime while you do the switch.

  • April 19, 2011 at 12:34 pm

    #1313582

    Thank you. That's a good idea.

    What's the difference between transaction log back and log tail backup, so far I have not used log tail backup, when should we use it?

  • April 19, 2011 at 2:14 pm

    #1313629

    The tail backup is a way to say the "final" log backup to get the latest transactions since the diff.

    As for compressing the backup, it might be worth spending the 2-8 hours it takes to do that and save some bandwith on the network. It wouldn't be an issue where I work, but it might be where you are...

    after that the diff and tlog should just fly on the network in minutes.

  • April 21, 2011 at 8:43 am

    #1314558

    Here's an option I had never heard of. I used to do this manually but this is much better :

    There's a way to both take the tail t-log and "shut down" the db at the same time so you're certain to really have everything. Tx gail.!

    http://www.sqlservercentral.com/Forums/FindPost1096908.aspx

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

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