|
|
Posted by Erland Sommarskog on 08/17/06 21:59
TG (spam@spam.com) writes:
> I had assumed that they work like Oracles archive logs, in that once
> they are full they are archived to disk in case they are needed, to
> duplex destinations if need be. Does SS do this, or do I need to be
> backing up transaction logs on an hourly (or shorter) basis? In looking
> at enterprise manager I see that it does not look like the transaction
> logs are archived anywhere, but overwritten once filled up, similar to
> Oracle noarchivelog mode, in that if you lose your online redo logs or
> anyone of them has recycled since your last backup, then you can only
> recover to the last good backup.
>
> Is this a correct assumption? Should I be backing up transaction logs
> hourly to make sure I can have PIT or POF recovery?
If you don't backup your transaction logs, and you are in full recovery,
the logs will grow and grow, until you run out of disk space. Nothing
will be overwritten.
So, yes, you need to backup your transaction logs with some frequency.
Note that you can backup the last part of the transaction log, even if
the data file is inaccessible, so that you can restore to a point in
time just before a failure.
There are some articles on SQL Server MVP Tibor Karaszi's web site that
covers the last part, see http://www.karaszi.com/SQLServer/articles.asp.
--
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
Navigation:
[Reply to this message]
|