SEO

Coming from a (mostly) Oracle shop, I am unclear how SS transaction logs
work as far as up to point of failure recovery goes.

I have the few MSSQL databases I look after in full recovery mode, and I
set up maintenance plans to back these up online on a daily basis. In
the research I did it was recommended backing up the transaction logs as
well on a frequent basis to ensure recovery to POF or point in time.

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?

thanks

TG

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland Sommarskog wrote:
> 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.
>
>
Tack sa mycket..

I see - so you are saying that the transaction log will have to be
backed up in order to restore to a point in time, I was under the
impression that the transaction log was somewhat like an online redo log
, but I see that MSSQL handles this somewhat differently. I'll check out
the link you posted and do some more reading up!

TG (spam@spam.com) writes:
> I see - so you are saying that the transaction log will have to be
> backed up in order to restore to a point in time, I was under the
> impression that the transaction log was somewhat like an online redo log
> , but I see that MSSQL handles this somewhat differently. I'll check out
> the link you posted and do some more reading up!

Yes, you can never restore from the transaction log itself, you must restore
from a backup of the transaction log.

You can wait until the disaster hits to backup the log, though, but I would
not recommend that.


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx