Best practices for MSSQL off-site backup?
August 6, 2011 7:09 AM Subscribe
What's the smartest way to keep regular backups of Microsoft SQL Server databases stored remotely?
posted by odinsdream to computers & internet (2 answers total) 2 users marked this as a favorite
I'd like to store off-site backups of a few SQL databases with the following specifics:
* 2 week retention
* 6 hour point-in-time recovery
* Bandwidth and space efficient
I've tried to accomplish this with three SQL jobs:
* Full backup once a week
* Differential every night at midnight
* Transactional every six hours
All of this is dumped into a directory that's rsync-ed offsite.
Something about this isn't working right, though, since it frequently transfers data equivalent to a full backup, even though I'm expecting it to only transfer a 6-hour delta.
It seems like something about my process leads the rsync client to grab more files than it needs to.
What's the best way to handle this? I've reviewed two separate SQL Admin books, but they all cover local storage of backups only.