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.
GerryDataPro 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|||Note, though that even though you need to do a log backup (you cannot use the active log), you still
have plenty of options. What is required is that the log file exists. I'm sure this is the same as
in other systems. If the database is inaccessible, you can still produce a log backup using the
NO_TRUNCATE option of the BACKUP LOG command. And even if you can't start the SQL Server, you can
handle this situation:
Create a database on some other SQL Server.
Stop that SQL Server.
Delete the database files.
Copy your "production" ldf file where the newly created log file used to be.
Start SQL Server.
Do a log backup using NO_TRUNCATE.
You have now produced that "last log backup".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1165419474.732740.263160@.f1g2000cwa.googlegroups.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?
> >|||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/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1165424865.858239.168580@.73g2000cwn.googlegroups.com...
> 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?
>> >
>|||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