October 15, 2008 at 7:28 am
Hi,
I want to create a development environment that is similar to production. The production database is on server A in location A. The development database is on server B in another state in location B. Both servers are MS Windows 2003 and the databases are SQL Server 2000.
I'm copying over the backup of the production database using ROBOCOPY. I have logged in to the development server via Remote desktop and am using ROBOCOPY from there. The database is 38G. The robocopy has completed 30% in 18 hours. At this rate, it'll take another 40 hours to finish.
Is there a better alternative to ROBOCOPY? Something that is quicker?
Thanks.
VP
October 15, 2008 at 7:32 am
Not that I know of. Did you make sure you compressed the file first?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 15, 2008 at 7:38 am
You should definitely either compress the backup file using any standard zip utility or one of the many 3rd party backup products.
In addition to this you can try backing up directly to the development server via UNC path. As long as SQL Server has acecss to the network share, backing up directly to the development server may take a little longer on backup, but will eliminate the lengthly copy step you are doing right now.
October 15, 2008 at 8:18 am
If you have issues copying, the backup won't run across the network. Any latency or delays will cause it to fail.
If it's taking that long to copy, you likely have network congestion, or it's failed and doesn't know it. A simple copy should work, why use Robocopy?
October 15, 2008 at 12:24 pm
Hi
I have used robocopy extensively and I think it is one the best ways to copy a file over the network.
Pls check you network utilization from the source to destination and your NIC card capacity.
Also use /Z option with robocopy which will enable to restart the copy of the file incase of network failure.
Regards,
Razi, M.
http://questivity.com/it-training.html
October 17, 2008 at 1:51 pm
Hi,
Why don't you try simple windows copy command using universal naming path?I tried that way for our db and it worked pretty well for me.
October 17, 2008 at 2:01 pm
Robocopy is fine and isnt' the issue.
The earlier posters that hinted about compression is more likely it.
If you're going to need to do this frequently, you might consider log shipping or db mirroring in high performance mode.
good luck
~Craig
Craig Outcalt
October 17, 2008 at 2:32 pm
At my previous client, I had to frequently copy files that were several times the size of the file you're copying. We used filezilla as that's what the ops guy knew and liked (may also have had something to do with the fact that we were mostly copying from Windows to a Unix box). The (non-compressed) files that were around 1 TB in size generally took 8-12 hours to transfer.
Definitely look at network issues. We had some that cause major issues.
October 19, 2008 at 2:23 am
using a product such as Redgate SQLBackup you can compress backups by up to 90%
download the free trial and try it out
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 19, 2008 at 2:34 am
I ran trials of all the SQL backup compression tools (Red gate, Quest and a few others). They all failed miserably.
October 19, 2008 at 5:31 am
Pam Brisjar (10/19/2008)
They all failed miserably.
quantify this some more! Failed miserably how. I use Redgate SQLBackup commercially over around 10 SQL servers backing up databases, never get a problem!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 19, 2008 at 5:52 am
I couldn't tell you which one had which error as it was about a year ago and I've since gone on to other projects. This was a 3 TB OLAP DB with little to no true "maintenance window". The desire was to be able to back up to a networked drive. All said they could do it but none succeeded.
One backup product never found / recognized the network drive. Tech support couldn't give me a solution.
One backup product failed to complete after running for 2+ days.
One backup product completed but the backup file was somehow corrupted. This happened repeatedly. Even tech support couldn't figure out what was going on as there was no real error or anything.
I had pressing issues to deal with so I just wound up coming up with my own strategy rather than wasting any more time.
To be fair, the database was (and still is as far as I know) running on seriously outdated hardware. It has issues even with its normal operations. I did a lot of patching and fixing for that client.
October 19, 2008 at 7:05 am
you are always best to backup to local disk then copy to the network, unless of course you have a guaranteed solid network connection then maybe
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 19, 2008 at 11:30 am
Even a solid network connection can get busy, and chances are it will happen when you have a backup running. It's not worth it. Get enough space for a local backup. You can always copy it to the network. Copy is much more reliable than backup across the network.
October 19, 2008 at 2:02 pm
Yes, guys, I'm well aware of this. In fact I've mentioned it myself several times on this very board. (Probably should have mentioned it myself for the benefit of more novice people.)
In that particular case, though, we didn't have a lot of options and the tools all said they could do it no issue.
Some other points:
The "network" was 100% dedicated - no other traffic other than the database backup.
There were, at the time, no other drives to use.
I wouldn't have ever even attempted it otherwise.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply