all, i have a usual requirement of refreshing the databases in house,with the production db. The problem i am facing is with moving around the files. Database is hosted off site and also backed up to the local server there. pulling up the backup files over the netwrok is a pain, like 1 gb db take upto 50mins. in this kind of scenario what is the best approach to take? thanks,
I'd probably adjust your backups to do a full once a week and differential the rest of the days. Schedule a robocopy process to get your full backup to a server at your location, that way you always have one on hand. You could do the same with differentials, which would be a lot smaller and easier to work with.
I have similar, except the backup sizes are rather larger... I use Quest Software's Litespeed for SQL Server
(other backup systems are available), which compresses backups for any version of SQL Server, not just SQL 2008 and above. One of the features is that you can tell it about multiple backup targets - eg a drive on your local server, and a share on another server - and those are created at the same time. I do weekly full backups, daily differentials and (depending on server) hourly or quarter hourly transaction log dumps - and they all go to at least two locations (some go to four locations, but that's a topic for another time...)
One option we have had to use is to copy the files to a portable USB drive and courier it. Added expense, security concerns... but if you need multi-100 GB databases transferred and the pipe just won't cut it...