473,324 Members | 2,501 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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
Jul 20 '05 #1
6 4696
"Rajesh Garg" <ra*******@rediffmail.com> wrote in message
news:14**************************@posting.google.c om...
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).
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.
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.


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.
Jul 20 '05 #2
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" <no****@testbox2.co.uk> wrote in message news:<3f*********************@pubnews.gradwell.net >...
"Rajesh Garg" <ra*******@rediffmail.com> wrote in message
news:14**************************@posting.google.c om...
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).


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


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.

Jul 20 '05 #3
"Rajesh Garg" <ra*******@rediffmail.com> wrote in message
news:14*************************@posting.google.co m...
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.

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.

Jul 20 '05 #4
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" <no****@testbox2.co.uk> wrote in message news:<3f*********************@pubnews.gradwell.net >...
"Rajesh Garg" <ra*******@rediffmail.com> wrote in message
news:14*************************@posting.google.co m...
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.

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.

Jul 20 '05 #5
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

ra*******@rediffmail.com (Rajesh Garg) wrote in message news:<14**************************@posting.google. com>...
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" <no****@testbox2.co.uk> wrote in message news:<3f*********************@pubnews.gradwell.net >...
"Rajesh Garg" <ra*******@rediffmail.com> wrote in message
news:14*************************@posting.google.co m...
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.

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.

Jul 20 '05 #6
"Rajesh Garg" <ra*******@rediffmail.com> wrote in message
news:14**************************@posting.google.c om...
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.


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.
Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Gyger | last post by:
Hello, Three weeks ago, I have started to develop a binding extension for Qt and PHP 5. Now, I can display a dialog box containing some widgets like label, buttons and edit line. I have just...
8
by: Torsten Mohr | last post by:
Hi, i write an extension module in C at the moment. This module does some work on some own data types that consist of some values. The functions that can change the data are written in C. ...
5
by: Jeffry van de Vuurst | last post by:
Hi, I'm working on an xml schema and I'm running into some problems relating substitutionGroups and extensions. This xsd validates fine: There are three elements and three complex types and...
7
by: Adam | last post by:
Im trying to add an httphandler for all *.sgf file extensions. I have developed the handler, 1. installed it into the gac 2. added it to the machine.config: <httpHandlers> <add verb="*"...
4
by: pepcag | last post by:
I used http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconalteringsoapmessageusingsoapextensions.asp as a template to create a very simple web method with soap...
1
by: Brian Henry | last post by:
Just thought maybe someone here would like to know this. It's an example code I just created quickly on how to figure out the name of a type of file based on its extension (say for example .DOC)...
0
by: robert | last post by:
Hi all, I'm having a hard time resolving a namespace issue in my wsdl. Here's an element that explains my question, with the full wsdl below: <definitions name="MaragatoService"...
1
by: Petr Prikryl | last post by:
Do you think that the following could became PEP (pre PEP). Please, read it, comment it, reformulate it,... Abstract Introduction of the mechanism for language extensions via modules...
6
by: tommybiegs | last post by:
I'm having a weird problem. I can't seem to force php to load an extension using php.ini, but it loads perfectly if I use dl() at the beginning of a test script. In php.ini I've got: ...
1
Ganesh9u
by: Ganesh9u | last post by:
Hi All, import org.sf.feeling.swt.win32.extension.hook.Hook; import org.sf.feeling.swt.win32.extension.hook.data.HookData; import org.sf.feeling.swt.win32.extension.hook.data.MouseHookData; ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.