question

Katie 1 avatar image
Katie 1 asked

Database backup files : Practices

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,
backupbackup-restore
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SirSQL avatar image
SirSQL answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

inder avatar image
inder answered
Add some sort of compression like SQL Server built in compression OR like WINZIP
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
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...)
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Given the latencies Katie mentioned I would not recommend backing up to an additional target across the WAN. This would be overly prone to network error and a failed backup.
0 Likes 0 ·
The network latencies I have to deal with are similar, but I've very rarely had any problems. And compression will help reduce the window of opportunity for those problems to occur... ...but I take your point.
0 Likes 0 ·
Blackhawk-17 avatar image
Blackhawk-17 answered
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...
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yup, done that before. Works well.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.