Join 3,552 readers in helping fund MetaFilter (Hide)


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?

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.
posted by odinsdream to Computers & Internet (2 answers total) 2 users marked this as a favorite
 
What do your rsync logs tell you? Offhand, it sounds like rsync is working at the file level rather than the block level.
posted by mkultra at 8:22 AM on August 6, 2011


this is my friend posting on my account: Hi there, I just want to give you an advice what you can do about backups. First thing you should know about is that if you are taking a backup of sql database server in Differential Back Up, you should save the file to exactly the same file which includes your full backup. Otherwise Differential Back Up takes the same size as your Full Back Up. The other information about Differential Back Up is here:


http://msdn.microsoft.com/en-us/library/ms181092.aspx

"Differential Database Backups Strategy (Simple Recovery Model)

Under the simple recovery model, work-loss exposure between backups increases over time. The following illustration shows a backup strategy that reduces work-loss exposure by using database backups supplemented by differential database backups. After the first database backup, a series of three differential backups is taken. The third differential backup is large enough that the next backup is a database backup. This database backup establishes a new differential base.


Differential Database Backups Strategy (Full Recovery Model)

With the full and bulk-logged recovery models, differential database backups minimize the time that is required to roll forward transaction log backups when restoring a database. A differential database backup restores the database to when the differential backup was completed.

The following illustration shows a backup strategy that supplements full database backups with differential database backups, and also shows a series of routine log backups. The presence of transaction log backups reduces potential work-loss exposure to the time after the most recent log backup. After the first database backup, a series of three differential backups is taken. The third differential backup is large enough that the next backup is a full database backup. This full database backup establishes a new differential base."

The other option is to take backup from remotely. You can use replication server which is included in SQL SERVER 2005 (and also in higher versions of SQL SERVER of course) too. The other option is also available in SQL Integration server which you can take backup remotely and it can also give you an option to send an information e-mail to your account.

I suggest you to use SQL Server jobs. But you should save the file into the same file. Also Replication Server and SQL Integration Server also will help you.
posted by saraindc at 9:34 AM on August 6, 2011


« Older I have been asked to cosign fo...   |  I'm in a very long-term relati... Newer »
This thread is closed to new comments.