Restore database to a certain point of time.

I will make it simpler to look...

I have DB1 - as backup for day 1
LOg1 as backup of logs

T1 T2 T3 T4 T5 ...some transaction on day 2

Now i backup again
DB2
Log2

I want to restore the database till the point of transaction T3 say. I
know the time or i assume a certain time.
Is this possible ....i tried several options but hand in between for
some reason or the other. How can i achieve my solution. Is there some
extra parameter i will require or what...i am wondering now that it is
not at all possible. Please help.
RVG
If possible guys can you please mail me the sloution on
raj_chins@.rediffmail.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Rajesh Garg <raj_chins@.rediffmail.com> wrote in message news:<3f4c2fb2$0$62083$75868355@.news.frii.net>...
> I will make it simpler to look...
> I have DB1 - as backup for day 1
> LOg1 as backup of logs
> T1 T2 T3 T4 T5 ...some transaction on day 2
> Now i backup again
> DB2
> Log2
> I want to restore the database till the point of transaction T3 say. I
> know the time or i assume a certain time.
> Is this possible ....i tried several options but hand in between for
> some reason or the other. How can i achieve my solution. Is there some
> extra parameter i will require or what...i am wondering now that it is
> not at all possible. Please help.
> RVG
> If possible guys can you please mail me the sloution on
> raj_chins@.rediffmail.com
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Your description is a little confusing for two reasons - first, you
don't say what time each event is happening at, and second, it's not
clear if you are backing up your log regularly or only once each day.
My guess from your description is that you are doing this:

Day 1

22h00 - BACKUP DATABASE...
22h30 - BACKUP LOG...

Day 2

09h00 - COMMIT txn1
10h00 - COMMIT txn2
11h00 - COMMIT txn3
12h00 - COMMIT txn4

22h00 - BACKUP DATABASE...
22h30 - BACKUP LOG...

Let's say it's now 23h30 on day 2, and you want to recover your
database to 11h00 on day 2. If my assumptions are correct, then you
cannot recover to a point in time because you have no transaction log
backups during Day 2. To recover to a point in time, you first recover
a full backup, then recover your log backups, one after another. But
in your scenario you have no series of transaction backups. At the end
of Day 2 you can recover to Day 1 22h00 (full backup), Day 1 22h30
(full backup + log backup), Day 2 22h00 (full backup), or Day 2 22h30
(full backup + log backup).

To recover to a point in time, you need at least one transaction log
backup after 11h00:

Day 2

09h00 - COMMIT txn1
10h00 - COMMIT txn2
11h00 - COMMIT txn3
12h00 - COMMIT txn4

13h00 - BACKUP LOG...

22h00 - BACKUP DATABASE...
22h30 - BACKUP LOG...

Now you can recover to 11h00: restore day 1 22h00 full + day 1 22h30
log + day 2 13h00 log, recovering only transactions before 11h00.

I may have misunderstood what your situation is - if so, then please
post a clear description of exactly what you are doing (in a similar
way to my example above). In the meantime, I suggest you review the
Books Online backup/restore section, in particular "Using Recovery
Models" and "Backup and Restore Operations".

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:60cd0137.0308270354.789e8248@.posting.google.c om...
> Your description is a little confusing for two reasons - first, you
> don't say what time each event is happening at, and second, it's not
> clear if you are backing up your log regularly or only once each day.
> My guess from your description is that you are doing this:
> Day 1
> 22h00 - BACKUP DATABASE...
> 22h30 - BACKUP LOG...
> Day 2
> 09h00 - COMMIT txn1
> 10h00 - COMMIT txn2
> 11h00 - COMMIT txn3
> 12h00 - COMMIT txn4
> 22h00 - BACKUP DATABASE...
> 22h30 - BACKUP LOG...
> Let's say it's now 23h30 on day 2, and you want to recover your
> database to 11h00 on day 2. If my assumptions are correct, then you
> cannot recover to a point in time because you have no transaction log
> backups during Day 2. To recover to a point in time, you first recover
> a full backup, then recover your log backups, one after another. But
> in your scenario you have no series of transaction backups. At the end
> of Day 2 you can recover to Day 1 22h00 (full backup), Day 1 22h30
> (full backup + log backup), Day 2 22h00 (full backup), or Day 2 22h30
> (full backup + log backup).
> To recover to a point in time, you need at least one transaction log
> backup after 11h00:

This is not true (see my reply to Rajesh), a full backup does not truncate
the transaction log, so in your example it is possible to restore from the
first full backup and then use the two transaction logs, completely ignoring
the second full backup.

Ian.