Point in time restore - cannot change date

I am trying to roll back a SQL Server 2000 database to a previous point
in time, but I am not allowed to select the date I want in the 'point
in time' dialog - it always reverts back to the date of the transaction
log backup.
So far, I have done the following, in the following order:
1. Backed up the database.
2. Backed up the transaction log.
3. Restored the database from a previous backup.
4. Do a 'point in time restore' from the transaction log backup
completed in step 2.
At step 4, if I follow through with the restore, choosing the date and
time of the log backup as my 'point in time', I sucessfully get the
data I started with.
Can anyone tell me why I cannot specify a 'point in time' of, say, five
days ago - and, more importantly, *how* to specify another point in
time? If the transaction log has the data to restore my database to the
point at which the log backup was taken, surely it has the data to
restore to a point a few days earlier.
Thanks,
Joejoe
Do you perfom it from EM, right?
There is very good topic ( with examples) about it in the BOL
"joe" <joe.hodsdon@.gmail.com> wrote in message
news:1155098591.530691.44480@.n13g2000cwa.googlegroups.com...
>I am trying to roll back a SQL Server 2000 database to a previous point
> in time, but I am not allowed to select the date I want in the 'point
> in time' dialog - it always reverts back to the date of the transaction
> log backup.
> So far, I have done the following, in the following order:
> 1. Backed up the database.
> 2. Backed up the transaction log.
> 3. Restored the database from a previous backup.
> 4. Do a 'point in time restore' from the transaction log backup
> completed in step 2.
> At step 4, if I follow through with the restore, choosing the date and
> time of the log backup as my 'point in time', I sucessfully get the
> data I started with.
> Can anyone tell me why I cannot specify a 'point in time' of, say, five
> days ago - and, more importantly, *how* to specify another point in
> time? If the transaction log has the data to restore my database to the
> point at which the log backup was taken, surely it has the data to
> restore to a point a few days earlier.
> Thanks,
> Joe
>|||Hi Uri,
Thanks for the reply. Yes, I'm working in EM. I reviewed the Books
Online before doing anything - that's where I got much of my
instruction. According to the "How to restore a point in time" article
in BOL, I should be able to specify a date and time to which I want to
restore. But EM won't let me change the date.
Thanks again,
Joe
Uri Dimant wrote:[vbcol=seagreen]
> joe
> Do you perfom it from EM, right?
> There is very good topic ( with examples) about it in the BOL
> "joe" <joe.hodsdon@.gmail.com> wrote in message
> news:1155098591.530691.44480@.n13g2000cwa.googlegroups.com...|||Joe,
I remember an issue in SQL2000 with point in time restore from EM.
Actually the very first time you try to restore a database from EM
point in time restore doesn't work. You have to do it from QA using the
SQL statements. I thought it has been fixed in one of the service packs
but don't remember which one.
Markus|||Thank you, I'll give that a try.
Joe
MarkusB wrote:
> Joe,
> I remember an issue in SQL2000 with point in time restore from EM.
> Actually the very first time you try to restore a database from EM
> point in time restore doesn't work. You have to do it from QA using the
> SQL statements. I thought it has been fixed in one of the service packs
> but don't remember which one.
> Markus