Posted by Erland Sommarskog on 07/08/05 11:04

pb648174 (google@webpaul.net) writes:
> Just out of curiosity, what point would there be in up to the minute
> recovery? You will still need to make a point in time backup of the mdf
> and ldf files, no? So why not just make a backup? Under what scenario
> would you use the point in time restore?

Say that you run a web shop. If you have a serious error, how much data
do you want to lose?

Say that you take full backsups every second hour. Last backup was 13:00.
At 14:53 you make intend a manual update from Query Analyzer to an order
which had some minor problem. But when you select the statement in the
query window you glitch, and the query is sent to SQL Server without the
WHERE clause. If you have simple recovery, you will have to restore that
backup from 13:00 and lose two hours of business. (And probably more,
because the customers whose orders disappeared this way may never come
back.)

With full recovery, you can in this situation take a backup of the
transaction log, and you can restore the database to the state it was
in at 14:52, and minimize your losses.

Another option for this fatal scenario is to have a log-reader tool like
Lumigent Log Explorer or LOG PI, which permits you rollback statements
selectively.

But for a scenario where the database becomes corrupt for an unknown
reason, they are less useful, and of course they can't help in case of
disk crash. In case of a disk crash you may not be able to backup the
last part of the log. However, some shops run transaction-log dumps
as often as every five minutes, or even by the minute.

And for many shops a full backup evervy second hour is not an option.
If your database is 100 GB, the backup takes time to run. And takes up
disk space.

But far from all business need all this. I recall a client whose database
proved to be corrupt. And what's worse, the most recent backup was also
corrupt. We had to go back over a week to find a good version. However,
this was not a major issue for them. They had their transaction log on
paper, so to speak, and could re-register all data that was lost.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]