Question on Backup/Restore

New to SQL Server. is it true, as it seems to be, that SQL Server
cannot do a point in time recovery to a point in time that would exceed
the time of the last transaction log backup?
That is, it cannot use the active log in conjunction with the archived
logs?
Second question, if the server fails and is rebooted, will the system
catalog tables 'remember' the times of the transaction log backups?
Thanks in advance.
Gerry
DataPro wrote:
> New to SQL Server. is it true, as it seems to be, that SQL Server
> cannot do a point in time recovery to a point in time that would exceed
> the time of the last transaction log backup?
> That is, it cannot use the active log in conjunction with the archived
> logs?
> Second question, if the server fails and is rebooted, will the system
> catalog tables 'remember' the times of the transaction log backups?
> Thanks in advance.
> Gerry
>
1. No, it cannot use the "active" log for this. You must take a final
transaction log backup, which will contain the data from the active log,
which you can then use in your point-in-time restore.
2. Yes, this information is retained between reboots.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||
> 1. No, it cannot use the "active" log for this. You must take a final
> transaction log backup, which will contain the data from the active log,
> which you can then use in your point-in-time restore.
> 2. Yes, this information is retained between reboots.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Thanks Tracy
|||Thanks Tibor;;
OK after I do the restore of a user database...even though the restore
is marked complete on the percentage bar graph the database was marked
as 'Loading'
Baffled me...thought it was because it was being rolled forward
through transaction logs.
I ended up issuing a
RESTORE DATABASE <Database name> WITH RECOVERY and that cleared it up.
But that does not leave me with a warm and fuzzy.
Is this typical behavior in SQL Server?[vbcol=seagreen]
|||Tibor Karaszi wrote:
> When you do RESTORE, you specify NORECOVERY on all restores (db, log, log etc) but the last one.
> Until you've done restore using RECOVERY, the database is in "restoring" state (reported as
> "loading" by earlier tools). If you forgot to do RECOVERY on your last backup, you can handle the
> situation in just the way you did. If you have a case where you do, for instance:
> RESTORE DATABASE ... WITH NORECOVERY
> RESTORE LOG ... WITH NORECOVERY
> RESTORE LOG ... WITH NORECOVERY
> RESTORE LOG ... WITH RECOVERY
> And the database is still in "restoring" state (inaccessible, and not only a refresh problem in EM),
> you should report this as a bug to MS.
> Technical sidenote: The only difference between NORECOVERY and RECOVERY is whether the UNDO phase is
> performed by the restore process. The prior REDO phase is always performed.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Thanks Tibor