Point of time Recovery EM

We have been testing point of time recovery using EM and found that this does not work.
We enter date and time and do net get the logs restored. Even if we use the default date it does not work. In Query Analyser we have have managed to recover to a point in time. Anybody got any idea why EM does not work.
We are using 2000 sp3What was the sequence in EM?|||We asked fro restore and had backup and three log backups detailed the point in time was on last log so highlighted all backups and logs and select point of time and chose date whcuh was current day and amended time and clicked restore sequence show ed each backuop being restored as it should be it did not work

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

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:
> 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...
> >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
> >|||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

Please help: date-only comparison

We have a select query on SQL server 2K with some date criteria like this:
transaction_date + 30 <= end_date
Because the data type of the date fields is "Datetime", the comparison gets
to time. This may cause missing records when (transaction_date + 30 =
end_date).
So I tried with Date-only as below:
convert(varchar(10), transaction_date + 30) <= convert(varchar(10),
end_date)
or
convert(varchar(10), transaction_date + 30, 120) <= convert(varchar(10),
end_date, 120)
But this does not work! Please advise on how to truncate TIME in this case.
Thanks!
JohnGet calculations out of one side.
WHERE transaction_date < (convert(smalldatetime, convert(char(8), end_date,
112)) - 29)
If you give some table structure, sample data, and desired results
(including rows that make the solution to "does not work", whatever that
means), we can give a more authoritative answer. Please read
http://www.aspfaq.com/5006 to see why it is important to include more
specific, well, specs.
"John61" <wanghaodong@.yahoo.com> wrote in message
news:uQ4dNkscGHA.4224@.TK2MSFTNGP04.phx.gbl...
> We have a select query on SQL server 2K with some date criteria like this:
> transaction_date + 30 <= end_date
> Because the data type of the date fields is "Datetime", the comparison
> gets to time. This may cause missing records when (transaction_date + 30 =
> end_date).
> So I tried with Date-only as below:
> convert(varchar(10), transaction_date + 30) <= convert(varchar(10),
> end_date)
> or
> convert(varchar(10), transaction_date + 30, 120) <= convert(varchar(10),
> end_date, 120)
> But this does not work! Please advise on how to truncate TIME in this
> case.
> Thanks!
> John
>
>|||Well I don't see a problem.
If you exactly knowthe row which is is is missing or erronously displayed,
then you can do a check on it and see why its getting displayed.
Like
select convert(varchar(10), transaction_date + 30, 120) ,convert(varchar(10)
,
end_date, 120), <other columns>
from all tables
where
conditions
--comment the following condition and check the result set. Maybe that will
give you an insight.
--and convert(varchar(10), transaction_date + 30, 120) <=
convert(varchar(10),
end_date, 120)
Hope this helps.
"John61" wrote:

> We have a select query on SQL server 2K with some date criteria like this:
> transaction_date + 30 <= end_date
> Because the data type of the date fields is "Datetime", the comparison get
s
> to time. This may cause missing records when (transaction_date + 30 =
> end_date).
> So I tried with Date-only as below:
> convert(varchar(10), transaction_date + 30) <= convert(varchar(10),
> end_date)
> or
> convert(varchar(10), transaction_date + 30, 120) <= convert(varchar(10),
> end_date, 120)
> But this does not work! Please advise on how to truncate TIME in this case
.
> Thanks!
> John
>
>