I need to copy a 20G db backup file from one server to another nightly. They are on the same network. Would it be quicker to use the FTP transfer task in a package or the EXEC xp_cmdshell 'copy ... ' in a exec SQL task to move the file. It is important that I get a return code back to take different courses of action in the case of errors. Thanks for any help!
I'm not sure if this is a possability for you, but I have found it to be a lot quicker to backup to the remote location directly (Use a UNC)rather than backup and then copy. Copying a 20 GB file will probably take quite a while (In my experience it has anyway).
Not that is an option right now. I need to keep a backup on both servers. And the target server is not always available at the time of the backup. The local backup takses, 1/2hr, but the copy takes about 2hrs and kills the network. Thanks for the help.
Ron, FTP will probably be slower than straight copy. I'd stick with EXEC xp_cmdshell 'copy ... ' if I were you.
Instead of having your database backed up onto a single backup device, I would suggest striping backup accross several backup devices. That way, the size of each device file is much smaller, and therefore files are more manageable... You can use zip (may not work on very large files) or rar (a bit slower, but works on just about anything) to compress the files prior to copying.
copy file or use package ftp to copy backup
Posted - 12/20/2001 : 15:30:44
