Point in Time Restore Part II

[A.M. de Jong]

In the hereunder written message I talk about point in time restore.
It is now based upon the fact that there are no hardware problems or what so
ever.
I just would like to roll back to a situation of some time (minutes, hours
or what ever) ago.

Used to the ingres database a point in time restore can take place UP to
any, any, any time since the last FULL backup. (any time up to now !!!)

I can't understand why a point in time restore can only be done based upon
transaction log backups. The current transaction log is also available in my
opinion. (Turn off the power, turn on the power and you will notice that the
automatic recovery is based upon this transaction log file; so in that case
this file is used)

That's what my question is about. Is it correct that a point in time restore
in a SQL server environment can only be done up to the last transaction log
backup.

Bye

Arno de Jong,
The Netherlands.

[Erland Sommarskog]

A.M. de Jong (ar****@wxs.nl) writes:

Used to the ingres database a point in time restore can take place UP to
any, any, any time since the last FULL backup. (any time up to now !!!)

I can't understand why a point in time restore can only be done based
upon transaction log backups. The current transaction log is also
available in my opinion. (Turn off the power, turn on the power and you
will notice that the automatic recovery is based upon this transaction
log file; so in that case this file is used)

That's what my question is about. Is it correct that a point in time
restore in a SQL server environment can only be done up to the last
transaction log backup.

Just because Ingress works in one way, there is no requirement for MS
SQL Server to work in that way too.

In SQL Server you need a transaction-log backup to do a point-in-time
restore, but since you can backup the transaction log at any point,
this is not any serious restriction.

I should add here that you must be running full or bulk_logged recovery
mode to have availability to point-in-time restore. With simple recovery
mode, this feature is not available.

In the case you want to undo a fatal SQL statement like an UPDATE without
a WHERE clause, you may be interested in exploring the third-party tool
Log Explorer from Lumigent, check out www.lumigent.com.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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