| Author |
Topic |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2009-11-08 : 14:12:14
|
| Hi there,Taking db back up once a day is OK except you lose everything between when the back up was taken and when the db went down.What's the best way to get a continuous copy of a database and how accurate would the copy be?Cheers, XF. |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-11-08 : 15:24:11
|
| Hmm.. I'm thinking DPM! |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-11-08 : 17:05:29
|
| Regular log backups can be done as often as you wish. You can also make differential backups frequently, and unless you have a large database and limited disk space, you can make several full backups a day as well.DPM has a minimum 15 minute limit on backup frequency, and it's not a true SQL Server backup (you cannot restore to a precise point in time, only to the most recent backup window). It's not truly continuous either.I do daily full backups and log backups every 5 minutes on my main production DB (about 100 GB). Most log backups are only a few MB and only go above 100 MB after scheduled jobs that do a lot of updates. I'm thinking about going to a 15 minute window just to reduce the number of files that are generated. |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-11-08 : 18:50:51
|
| Would doing a log back up every five minutes on an ultra busy db get in the way of the latest transactions? By a 'log backup' you mean log shipping right?And is there a way to do a back up every five minutes without a scheduler? (My hosting package is on a shared server.) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-11-08 : 20:42:47
|
| Backups never get in the way of transactions. They do use extra I/O but as long as they are backed up to a different physical disk than that used by the database and log files, it won't be significant.If you're on a shared server then your host provider should be the one doing the backups, or at least provide local space for you. If they don't allow you to schedule the job yourself you can provide the details to them (backup type, frequency, etc.) You could kick off the backup commands remotely without using SQL Agent but I don't recommend it.I don't think you need to back up every five minutes unless you have an SLA that requires no more than a 5 minute gap. If your server is really that busy it should be on a dedicated box anyway.No, I don't mean log shipping, I'm only talking about backups. If you're trying to synchronize a remote source, you can use log shipping or replication for that, but that's not the same thing as making backups. |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-11-08 : 20:54:11
|
quote:
I don't think you need to back up every five minutes unless you have an SLA that requires no more than a 5 minute gap. If your server is really that busy it should be on a dedicated box anyway.
Even if a DB has a single transaction per day, surely you're more likely to recover it if the db is backed up every five minutes instead of every fifteen? What harm is there in doing a back up as frequently as possible?The hosting company does do back ups but not every day and then charges to recover it. They allow manual back ups with 'myLittleBackUp' but its totally manual. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-11-08 : 22:02:08
|
quote:
Even if a DB has a single transaction per day, surely you're more likely to recover it if the db is backed up every five minutes instead of every fifteen?
No. What gave you that idea?quote:
What harm is there in doing a back up as frequently as possible?
No harm except more backups to manage, and if you don't have transactions that frequently you don't gain anything by backing up more often.A really good piece of advice I got was to not think of it as a backup strategy, but as a restoration strategy. If you need to restore quickly you want to have as few backup files to process as possible. Taking log backups every five minutes when you only get one transaction an hour is not only wasteful but could prevent proper restoration in case a log backup gets corrupted. |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-11-08 : 22:32:56
|
quote:
No. What gave you that idea?
The DB is backed up at 5:00pm.The transaction is at 5:01pm.The DB is backed up at 5:05pm.DB crashes at 5:06pm.I use the 5:05pm back up to recover the transaction.Else...The DB is backed up at 5:00pm.The transaction is at 5:01pm.DB crashes at 5:06pm.Back up at 5:15pm never happens so transaction is lost.So where am I going wrong here? :) |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-11-12 : 20:35:25
|
| Hey robvolk, what do you think? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-11-12 : 23:23:58
|
| We backup our transaction logs every 15 minutes. We also use clustering and database mirroring. With all of that and the hardware on the backend, we achieve 99.999% availability as required by our SLAs with our customers. Backing up every 5 minutes seems excessive. The more tlog files you generate, the more that will need to be used to do a recovery. This is why a differential should be thrown in there. We perform differentials once a day, 12 hours after the full backup. That's on top of the every 15 minute tlog backups.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
|
Best way to get continuous back-up