Extension- Urgent Help Required - Recovery

I have actually extended my request

I have a scenario like this.......

update esan set tes_address_city = 'TEST1' --at some time
update esan set tes_address_city = 'TEST12' --at some time
update esan set tes_address_city = 'TEST123' --at some time

backup database TESTWMS to disk = 'D:\temp\RecoveryTest\TESTWMS.db'
backup log TESTWMS to disk = 'D:\temp\RecoveryTest\TESTWMS01.log'

I take these backups at the end of day....
Now i want to recover till the second update ie 'TEST12'...
Assumptions to be made:-
I dont know the timing of the updates.
I am allowed to take back only once.
We can also assume to have a backup of the previous day also.


Is this possible and how can i do it.......
Please help me ...and urgent also.......

Thanks in advance
RVG

Extension is here
Suppose that ou database is crashing at 2.30 and i run the restore

RESTORE DATABASE TESTWMS FROM DISK='D:\temp\RecoveryTest\TESTWMS.DB'
with norecovery --Prev day log
RESTORE LOG TESTWMS FROM DISK='D:\temp\RecoveryTest\TESTWMS01.log'
with norecovery
RESTORE LOG TESTWMS FROM DISK='D:\temp\RecoveryTest\TESTWMS02.log'
with recovery, STOPAT = '2003-08-26 16:37:45.870'

i get message like this
This log file contains records logged before the designated
point-in-time. The database is being left in load state so you can
apply another log file.
RESTORE LOG successfully processed 0 pages in 0.389 seconds (0.000
MB/sec).

Now when i try to run a
Select * i get message lke

Server: Msg 927, Level 14, State 2, Line 1
Database 'TESTWMS' cannot be opened. It is in the middle of a restore.

How do i solve this problem. One way is to use a NO_TRUNCATE with a
backup in between (ie incase of database corruption).
But i dont want to use a log backup once again. I should be able to
solve it without using a log backup once again. In shot backup only
once a day. Aslo i need to refer to a database to that particular
point of time from my backup.

Thanks in advance
RVG

re: Extension- Urgent Help Required - Recovery

"Rajesh Garg" <raj_chins@rediffmail.com> wrote in message
news:14215add.0308260318.26e7c24b@posting.google.c om...[color=blue]
> I have actually extended my request
>
> I have a scenario like this.......
>
> update esan set tes_address_city = 'TEST1' --at some time
> update esan set tes_address_city = 'TEST12' --at some time
> update esan set tes_address_city = 'TEST123' --at some time
>
> backup database TESTWMS to disk = 'D:\temp\RecoveryTest\TESTWMS.db'
> backup log TESTWMS to disk = 'D:\temp\RecoveryTest\TESTWMS01.log'
>
> I take these backups at the end of day....
> Now i want to recover till the second update ie 'TEST12'...
> Assumptions to be made:-
> I dont know the timing of the updates.
> I am allowed to take back only once.
> We can also assume to have a backup of the previous day also.
>
>
> Is this possible and how can i do it.......
> Please help me ...and urgent also.......
>
> Thanks in advance
> RVG
>
> Extension is here
> Suppose that ou database is crashing at 2.30 and i run the restore
>
> RESTORE DATABASE TESTWMS FROM DISK='D:\temp\RecoveryTest\TESTWMS.DB'
> with norecovery --Prev day log
> RESTORE LOG TESTWMS FROM DISK='D:\temp\RecoveryTest\TESTWMS01.log'
> with norecovery
> RESTORE LOG TESTWMS FROM DISK='D:\temp\RecoveryTest\TESTWMS02.log'
> with recovery, STOPAT = '2003-08-26 16:37:45.870'
>
> i get message like this
> This log file contains records logged before the designated
> point-in-time. The database is being left in load state so you can
> apply another log file.
> RESTORE LOG successfully processed 0 pages in 0.389 seconds (0.000
> MB/sec).[/color]

From BOL :-

If you specify a STOPAT time that is beyond the end of the RESTORE LOG
operation, the database is left in an unrecovered state, just as if RESTORE
LOG had been run with NORECOVERY

So it looks like you haven't got the right log file with the transactions
you want.
[color=blue]
> Now when i try to run a
> Select * i get message lke
>
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'TESTWMS' cannot be opened. It is in the middle of a restore.[/color]

To make the database usable you would need to run :-

RESTORE DATABASE TESTWMS with recovery

Though it looks like you haven't got to the point you want yet.

Ian.

re: Extension- Urgent Help Required - Recovery

Hi IAN thanks for prompt help ....
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


"Ian Stocks" <nospam@testbox2.co.uk> wrote in message news:<3f4b813e$0$329$bed64819@pubnews.gradwell.net >...[color=blue]
> "Rajesh Garg" <raj_chins@rediffmail.com> wrote in message
> news:14215add.0308260318.26e7c24b@posting.google.c om...[color=green]
> > I have actually extended my request
> >
> > I have a scenario like this.......
> >
> > update esan set tes_address_city = 'TEST1' --at some time
> > update esan set tes_address_city = 'TEST12' --at some time
> > update esan set tes_address_city = 'TEST123' --at some time
> >
> > backup database TESTWMS to disk = 'D:\temp\RecoveryTest\TESTWMS.db'
> > backup log TESTWMS to disk = 'D:\temp\RecoveryTest\TESTWMS01.log'
> >
> > I take these backups at the end of day....
> > Now i want to recover till the second update ie 'TEST12'...
> > Assumptions to be made:-
> > I dont know the timing of the updates.
> > I am allowed to take back only once.
> > We can also assume to have a backup of the previous day also.
> >
> >
> > Is this possible and how can i do it.......
> > Please help me ...and urgent also.......
> >
> > Thanks in advance
> > RVG
> >
> > Extension is here
> > Suppose that ou database is crashing at 2.30 and i run the restore
> >
> > RESTORE DATABASE TESTWMS FROM DISK='D:\temp\RecoveryTest\TESTWMS.DB'
> > with norecovery --Prev day log
> > RESTORE LOG TESTWMS FROM DISK='D:\temp\RecoveryTest\TESTWMS01.log'
> > with norecovery
> > RESTORE LOG TESTWMS FROM DISK='D:\temp\RecoveryTest\TESTWMS02.log'
> > with recovery, STOPAT = '2003-08-26 16:37:45.870'
> >
> > i get message like this
> > This log file contains records logged before the designated
> > point-in-time. The database is being left in load state so you can
> > apply another log file.
> > RESTORE LOG successfully processed 0 pages in 0.389 seconds (0.000
> > MB/sec).[/color]
>
> From BOL :-
>
> If you specify a STOPAT time that is beyond the end of the RESTORE LOG
> operation, the database is left in an unrecovered state, just as if RESTORE
> LOG had been run with NORECOVERY
>
> So it looks like you haven't got the right log file with the transactions
> you want.
>[color=green]
> > Now when i try to run a
> > Select * i get message lke
> >
> > Server: Msg 927, Level 14, State 2, Line 1
> > Database 'TESTWMS' cannot be opened. It is in the middle of a restore.[/color]
>
> To make the database usable you would need to run :-
>
> RESTORE DATABASE TESTWMS with recovery
>
> Though it looks like you haven't got to the point you want yet.
>
> Ian.[/color]

re: Extension- Urgent Help Required - Recovery

"Rajesh Garg" <raj_chins@rediffmail.com> wrote in message
news:14215add.0308262006.2b60cde@posting.google.co m...[color=blue]
> Hi IAN thanks for prompt help ....
> 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.[/color]


This is definately possible, try this for yourself :-

create database restore_test

create table changes (col1 varchar(128))

backup database restore_test to disk = 'c:\restore_test_1.bck'

backup log restore_test to disk = 'c:\restore_test_t1.bck'

insert into changes (col1) values ('insert 1')
select getdate()
waitfor delay '00:00:05'
insert into changes (col1) values ('insert 2')
select getdate() -- take a note of this time
waitfor delay '00:00:05'
insert into changes (col1) values ('insert 3')
select getdate()

select * from changes

backup database restore_test to disk = 'c:\restore_test_2.bck'

backup log restore_test to disk = 'c:\restore_test_t2.bck'

-- now restore to point in time
use master
restore database restore_test from disk = 'c:\restore_test_1.bck' with
norecovery

restore log restore_test from disk = 'c:\restore_test_t1.bck' with
norecovery

restore log restore_test from disk = 'c:\restore_test_t2.bck' with stopat =
'2003-08-27 10:43:20' -- this is the time just after 'insert 2'

select * from restore_test..changes -- 'insert 3' has not been included

Ian.

re: Extension- Urgent Help Required - Recovery

Hey Ian that works and works good.
ACtually i too had come up with a solution but had some problems with
that. Just neede to discuss it over.
I just used no_truncate in my bakup logs.
The problem there was that sql was not able to free up the place used
as it does in normal case. ( i have though not tried whether it frees
the space in this case also but i assume it will). The solution u gave
to me looks better.
Thanks once again
Cheers
RVG

"Ian Stocks" <nospam@testbox2.co.uk> wrote in message news:<3f4c7f7a$0$321$bed64819@pubnews.gradwell.net >...[color=blue]
> "Rajesh Garg" <raj_chins@rediffmail.com> wrote in message
> news:14215add.0308262006.2b60cde@posting.google.co m...[color=green]
> > Hi IAN thanks for prompt help ....
> > 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.[/color]
>
>
> This is definately possible, try this for yourself :-
>
> create database restore_test
>
> create table changes (col1 varchar(128))
>
> backup database restore_test to disk = 'c:\restore_test_1.bck'
>
> backup log restore_test to disk = 'c:\restore_test_t1.bck'
>
> insert into changes (col1) values ('insert 1')
> select getdate()
> waitfor delay '00:00:05'
> insert into changes (col1) values ('insert 2')
> select getdate() -- take a note of this time
> waitfor delay '00:00:05'
> insert into changes (col1) values ('insert 3')
> select getdate()
>
> select * from changes
>
> backup database restore_test to disk = 'c:\restore_test_2.bck'
>
> backup log restore_test to disk = 'c:\restore_test_t2.bck'
>
> -- now restore to point in time
> use master
> restore database restore_test from disk = 'c:\restore_test_1.bck' with
> norecovery
>
> restore log restore_test from disk = 'c:\restore_test_t1.bck' with
> norecovery
>
> restore log restore_test from disk = 'c:\restore_test_t2.bck' with stopat =
> '2003-08-27 10:43:20' -- this is the time just after 'insert 2'
>
> select * from restore_test..changes -- 'insert 3' has not been included
>
> Ian.[/color]

re: Extension- Urgent Help Required - Recovery

Now i would lime to extend my requirement a little more. ...
I am able to restore to a certain point of time with the three
restores as explained by you. Now is there anyway where in i can do
something like

Restore DB1
Restore Log1
Restore Log2 at (say) 13.00.00.000

To restore to 13.01.01.011
i will need to
Restore DB1
Restore Log1
Restore Log2 at (say) 13.01.01.011

Is there anyway i can skip the first two restores.
It will add to look good.
Thanks for reading me...
Cheers
RVG

raj_chins@rediffmail.com (Rajesh Garg) wrote in message news:<14215add.0308270710.62d49362@posting.google. com>...[color=blue]
> Hey Ian that works and works good.
> ACtually i too had come up with a solution but had some problems with
> that. Just neede to discuss it over.
> I just used no_truncate in my bakup logs.
> The problem there was that sql was not able to free up the place used
> as it does in normal case. ( i have though not tried whether it frees
> the space in this case also but i assume it will). The solution u gave
> to me looks better.
> Thanks once again
> Cheers
> RVG
>
> "Ian Stocks" <nospam@testbox2.co.uk> wrote in message news:<3f4c7f7a$0$321$bed64819@pubnews.gradwell.net >...[color=green]
> > "Rajesh Garg" <raj_chins@rediffmail.com> wrote in message
> > news:14215add.0308262006.2b60cde@posting.google.co m...[color=darkred]
> > > Hi IAN thanks for prompt help ....
> > > 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.[/color]
> >
> >
> > This is definately possible, try this for yourself :-
> >
> > create database restore_test
> >
> > create table changes (col1 varchar(128))
> >
> > backup database restore_test to disk = 'c:\restore_test_1.bck'
> >
> > backup log restore_test to disk = 'c:\restore_test_t1.bck'
> >
> > insert into changes (col1) values ('insert 1')
> > select getdate()
> > waitfor delay '00:00:05'
> > insert into changes (col1) values ('insert 2')
> > select getdate() -- take a note of this time
> > waitfor delay '00:00:05'
> > insert into changes (col1) values ('insert 3')
> > select getdate()
> >
> > select * from changes
> >
> > backup database restore_test to disk = 'c:\restore_test_2.bck'
> >
> > backup log restore_test to disk = 'c:\restore_test_t2.bck'
> >
> > -- now restore to point in time
> > use master
> > restore database restore_test from disk = 'c:\restore_test_1.bck' with
> > norecovery
> >
> > restore log restore_test from disk = 'c:\restore_test_t1.bck' with
> > norecovery
> >
> > restore log restore_test from disk = 'c:\restore_test_t2.bck' with stopat =
> > '2003-08-27 10:43:20' -- this is the time just after 'insert 2'
> >
> > select * from restore_test..changes -- 'insert 3' has not been included
> >
> > Ian.[/color][/color]

re: Extension- Urgent Help Required - Recovery

"Rajesh Garg" <raj_chins@rediffmail.com> wrote in message
news:14215add.0308280155.4f14318d@posting.google.c om...[color=blue]
> Now i would lime to extend my requirement a little more. ...
> I am able to restore to a certain point of time with the three
> restores as explained by you. Now is there anyway where in i can do
> something like
>
> Restore DB1
> Restore Log1
> Restore Log2 at (say) 13.00.00.000
>
> To restore to 13.01.01.011
> i will need to
> Restore DB1
> Restore Log1
> Restore Log2 at (say) 13.01.01.011
>
> Is there anyway i can skip the first two restores.[/color]

I dont believe this is possble, as it is not possible to use the 'STOPAT'
clause along with the the 'STANDBY' clause. So once you have specified a
'STOPAT' time, the database is fully recovered, and you can not restore any
other transaction logs.

The only sensible way it to use something like log explorer from
www.lumigent.com, if you really dont know when a database change happened.

Ian.