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

Point in time restore

Is it possible?

I have no Backups at all.
And I dropped a important table completely.
Is there anyway to roll back this command.

I am guessing that the transaction log keeps this inforation and its still there. I think with other 3rd party tools you can recover to a point in time without backups so there must be a way we can do it without 3rd party tools , cant it?

Your 'best' option at this point is to immediately download an eval copy of one of the Third party log tools. Many are fully functioning for 14-30 days.

But since you indicated you have no backups, if by some bad karma your database is in 'Simple' mode and you may be SOL.

|||Is there anyother way without 3rd party tools.
its a Full Recovery Model.
what is meant by > you may be SOL. <
|||

Download Lumnigents' Log Exlorer, or ApexSQL's SQLLog -or one of the several other good log tools.

It is just not worth any time and effort to try this without their help.

*SOL = "Sorry, Out of Luck"

|||

what is u r backup policy ... if u have full backup and subsequent transaction log backup it is possible to restore to the time just before u deleted the table... Read about STOP AT option in Restore Database...

suppose

(a) 06 Mar 07 you deleted the table at 1000 AM...

(b) U have full backup of 05 Mar 07 20:00 hrs (8 PM)

(c) your database is in Full recovery model

(d) you have no differential backup

(e) you have no TL backup

06 Mar 07 15:00 hrs you found that the data has been delete by mistake and u need to get the databack

(a) first and foremost thing is to take the TL backup

(b) restore the full backup of 05 Mar 07 20:00 hrs as a new database with No REcovery option

(c) Restore the TL with STOPAT 09:55 hrs with Recovery option

this is just a scenario which u can relate and try to get the data back

Madhu

|||I am aware of the stop at option and the scenario you mentioned.
But i am thinking of the worst-worst case scenario. What if you have no backups at all.
you deleted a table.
now u need to recover that table. How do you go about doing that without any backups.

If the history is kept in the transactional logs then it seems correct to assume that you can recover a table dropped 5 minutes before from the transactional logs.

Can we achive such a thing without using third party tools. worst case scenario.

Thanks everyone..|||

In simple terms, as I've tried to communicate to you a couple of times -NO!

If you need to do this, get a third party tool.

|||

Just out of interest, I've noticed that SQL 2005's RESTORE DATABASE command now has a STOPAT option, however BOL doesn't give much information away as to how/under what circumstances this option can be used.

I gave it quick a test earlier on today but couldn't get it to work. Does anyone have any experience of using it? I was wondering if it could possibly be of use to the original poster of this thread (i.e. by taking a full backup now, and if the log hasn't been backed-up, then is it possible to restore to a point in time earlier than the full backup? - my guess is 'probably not', although in theory surely this should be possible by examining the log and rolling back / undoing all uncommitted and comitted transactions to the point in time specified?).

Thanks
Chris

|||

It seems to me that if you make a 'Full Backup' at this juncture, ALL committed activities would be assumed to be in the database, and the transaction log would be truncated. Then not only would the table to permanently 'gone', but any record of it would no longer be in the transaction log.

The missing element is the previous 'Backup' -it does not exist, therefore, the only vestiges of the missing table (if other than page logging) 'might' be in the existing transaction log. Lunmigent's Log explorer would help find out IF there is any chance of recovering the data.

|||

Just one thing, though, taking a full database backup does not truncate the transaction log - this is one of the reasons why I was wondering whether the STOPAT option in the RESTORE DATABASE command would be of use. Agreed that a log-reader tool would be a good tool to invest in / trial if not.

Chris