|
|
Posted by Greg D. Moore \(Strider\) on 02/16/06 13:38
"Matik" <marzec@sauron.xo.pl> wrote in message
news:1140084783.803830.233070@g14g2000cwa.googlegroups.com...
> Hello,
>
>
> I've follow problem - thing to consider.
>
> SQLServer 200 sp3a, ms win 2003 server
> db simple recovery
>
> There is a production database, wich is around 20gb big. Db is backed
> up each day completely, but it takes up to 30 minutes.
What's the form of backup? disk, NAS, tape?
> Because there is a simple recovery model, there is no transaction log
> backup (it fails anyway), and we do not have up-to-point recovery.
>
> I'm considering to switch to full recovery model, but ....
> The problem is, I do not want to affect performance (when the backup is
> running, database is hardly avalible).
That's unusual. Generally a full-backup shouldn't hurt performance that
much.
If it really is, I'd do some more digging and try to find out why.
>
> So my question will be: does the full recovery model, will be better
> for db performance (for acces and blocking db; means, does it will take
> shorter?)
We do backups of similar size databases every 20 minutes with no noticable
impact on performance. I may eventually move to 10 minute backup windows.
Generally the backups shouldn't be blocking the db at all, the biggest cost
is generally disk I/O.
> Strategy will be (I hope ok) to back up during the week only
> transaction log (incremental), and once at the weekend, full database
> backup.
>
Only problem with this may be that in the event of a failure, say right
before you do your weekly full, you'll have a LOT of transaction logs to
restore. This will impact your time to recovery, so take that into account.
You may also want to use differential backups during the week to speed
things up in the event of having to recovery.
> Generaly, which one is better for performance?
> Which strategy will be the best, to keep performance at high level, but
> also have the possibility to restore data (in case of emergency) from
> the newest possible backup.
>
> Thanks for help
>
> Matik
>
Navigation:
[Reply to this message]
|