4

I have a problem with my backup strategy.

Currently I'm doing a Full Backup every night and transaction log backup every 2 hours to TAPE.

This used to work without problems, but the size of the DB is almost 2TB and the Full Backup takes 8 to 12 hours to complete overnight.

During the night I have several jobs running that are affected by the backup.

I was thinking of a different strategy using DB replication that would let me keep 2 databases synced and then only backup the "replicated" database, leaving the production database without an online backup.

Could this be a problem? Do I have any risk of losing information? What do I do in this case if I need to restore a backup?

Improve this question
1
  • Thank you for your answers. I've tried the compression and the times of backup were significantly reduced. It's a great improvement. I've been reading about the Database compression and I didn't find any disavantadges (beside high CPU usage). Do you know any disavantadge in using this option? Thank you one more time.
    – user5858
    Commented Jan 17, 2012 at 9:35

2 Answers 2

Reset to default
2

I don't think this is a very good idea because replication is not just as bullet proof as a sound backup strategy. A blocking situation could stop replication jobs and you could easily be out of sync for some time. Or maybe a network card will fail and replication will be dead for a period. This means you won't be up to date for some time, and also, you will be without a good backup of the main, updated site.

Similar technologies, that will deliver updates to a copy of the main db, are mirroring and log shipping, but they both rely on full recovery model and log backups.

I'd suggest that you'd better study compression for your backups. Starting from SQL 2008 (Enterprise) and SQL 2008 R2 (from Standard) you have the option of compressing the backups using native SQL compression. In addition you should investigate data compression (in place compression for tables and indexes data). These will give you some help by making the backups smaller.

There are also 3rd party backup tools that will be able to help with compression if you need more complex solutions. See this site for question about how to make SQL Server backups as small as possible.

Also, you might think about moving old data from big tables to a history database, and play with connection strings in your app.

You could also try to make the backups locally, in a folder on the main server, and then just copy it to the tape (maybe backup to disk + direct copy will take less than backup to tape).

Sorry for the emphasis: YOU JUST WON'T BE SAFE WITHOUT TESTED PRODUCTION BACKUPS!

Improve this answer
1

I wouldn't use this as a backup solution. Any time you add a table you'll have issues applying the new snapshot to the subscriber. You should be looking at says to decrease the backup time such as using database backup compression as that will reduce your backup times. You should also look into backing up to disk first, then backing up the backups to tape as this should greatly reduce your backup times.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.