recover a database that has been restored

Question

Post reply

recover a database that has been restored

prabha-956826

SSC Enthusiast

Points: 134
More actions
April 8, 2009 at 2:31 am

#199102

Hi,
How to recover the old copy of the databse that has been restored once again in SQL serevr 2005?
1) Restored a database named test from a bakup file.
2) Made some changes to the database --updated some rows.
3) Again restored the database using the same file.
I need the database on the step 2 1.e before the last restore.Is it possible.If so please help..

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

steveb.

SSC-Forever

Points: 46733

April 8, 2009 at 3:11 am

#974093

do you have a log backup between steps 2 + 3?

prabha-956826 SSC Enthusiast Points: 134 More actions · Answer 2

No I don't think so.I didn't backup anything.I just restored the database as such again.

steveb. SSC-Forever Points: 46733 More actions · Answer 3

Then unless you have documented your changes, they are lost..

prabha-956826 SSC Enthusiast Points: 134 More actions · Answer 4

Thanks for your immediate reply.I have the test_log file.Can anything be done with this?

steveb. SSC-Forever Points: 46733 More actions · Answer 5

not easily , you could try out a third party log-reader to recover the data but these can be expensive to purchase. So I guess it depends on how important the data was to the business.

prabha-956826 SSC Enthusiast Points: 134 More actions · Answer 6

Hi there,

I need to add this .When the database was restored the option selected in the GUI was "restore with recovery".I'm not sure the type of recovery is "SIMPLE"

Thanks,

Prabha

maechismo_8514 SSCoach Points: 17510 More actions · Answer 7

prabha (4/8/2009)
Hi there,
I need to add this .When the database was restored the option selected in the GUI was "restore with recovery".I'm not sure the type of recovery is "SIMPLE"
Thanks,
Prabha

Hi,

I hope this is all done in for testing purposes because in production this is not acceptable.

You should always think of backing up before changing any type of data in the database as a best practice.

Now, in your case before making the change and after making the change or before you restore and after your restored, this is where transaction log backups will help you, but since your recovery model is in Simple tlog backups is not possible.

Please give us more insight into this as what your are trying to do and also as per reading the logs I dont think thers any custom built tools unless you purchase some third party tools like Apex Log reader or look at this from Quest:

http://www.quest.com/change-director-for-sql-server/demo/chap06.htm

prabha-956826 SSC Enthusiast Points: 134 More actions · Answer 8

Hi ,

I'm new to this and sorry if i could not provide the information what you are looking for.

As I said I want to recover the database before the last restore has been done.Any way whatever it may be

maechismo_8514 SSCoach Points: 17510 More actions · Answer 9

prabha (4/8/2009)
Hi ,
As I said I want to recover the database before the last restore has been done.

Unfortunately not, unless you have made a backup after you made the changes/updates.

check out Apex SQL log you can try it, and see whether you can roll back( i don't think you can).

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

prabha (4/8/2009)
As I said I want to recover the database before the last restore has been done.Any way whatever it may be

So you're asking to recover data that has not being backed up and has since being overwritten? No way to do that. You can only restore if you have backed up, so, if you make changes you have to take a backup afterwards to be able to restore with those changes present.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

prabha-956826 SSC Enthusiast Points: 134 More actions · Answer 11

Hi Guys,

Thanks for all your reply.This is what the clear info I want to Know whether this can be done or not.So the answer is "No possibility"

Thanks once again for all you replies

Thanks,

Prabha

maechismo_8514 SSCoach Points: 17510 More actions · Answer 12

prabha (4/8/2009)
Hi Guys,
"No possibility"

It is but using a third party tool. You can read the log file and its internals using the undocumented command

Select * from::fn_dblog(NULL,NULL)

thanks,

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Krishna Potlakayala (4/8/2009)
It is but using a third party tool. You can read the log file and its internals using the undocumented command
Select * from::fn_dblog(NULL,NULL)

Only if the log is part of a database. There's no tool that can just read a .ldf file, it has to be attached to SQL as part of a DB. If the mdf has been overwritten (as a restore may do), that log file is useless.

Also, since he's in simple recovery, even a log reader may not be able to recover anything. In simple recovery, the log is truncated on checkpoint (every minute or so). Once a checkpoint has run, inactive log records are discarded and cannot be retrieved.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

prabha-956826 SSC Enthusiast Points: 134 More actions · Answer 14

Hi all,

I tried using apexsqlrecover.No use there is no option to recover a database that has been restored.