Posted by Erland Sommarskog on 02/17/06 00:47

Matik (marzec@sauron.xo.pl) writes:
> 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).

Then you have something investigate. The footprint of a backup should
be light. Database should certainly be available during this time.

> 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?)
> Strategy will be (I hope ok) to back up during the week only
> transaction log (incremental), and once at the weekend, full database
> backup.

Daily differential backups would be a better choice in that case.

Most of our customers run a daily full backup, and then translog backups
at least once an hour, some every ten or fifteen minutes.

Depending on your activity in the database, the log can grow big it
you back it up once a day. If your run a maintenance job, it could
grow bigger than the data file itself.

> 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.

The backup strategy should be determined of your requirements for
disaster recovery. If you are content with restoring a backup in
case of failure, continue with simple recovery. Switch to full if
you want up-to-the-point recovery.

A tip is that if you go for full recovery, is that you should allocate
a couple of GB for the log file on the spot, to prevent autogrow
growing the file piece by piece, which could lead to fragmentation
on file-syste level.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]