How to revert recovered database to previous in recovery mode?

Question

How to revert recovered database to previous in recovery mode?

sporoy

Hall of Fame

Points: 3114
More actions
July 23, 2010 at 5:23 am

#221636

Hi,
In SQL Server 2008 is there a way to revert a db to previous in recovery state after it has recovered?
(Is it possible to undo a log file?)
In particular we want to recover a 1,5 TB log shipping secondary database, after the recovery, it must go on as log shipping secondary (primary will be operational all the time), any ideas cheaper than full backup restore?
Thanks,

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

Once the database has been recovered (a restore was run WITH RECOVERY), you'll need to start again from the full backup to be able to restore further log backup.

Why do you want to recover the secondary?

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

sporoy Hall of Fame Points: 3114 More actions · Answer 2

We are planning a disaster test, but without switching the databases for the first test, only test users will be directed to test environment, is this a good approach? So we'll recover databases..

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

If you need to have users using the secondary then you'll need to recover it.

Two points:

1) Once you've finished the disaster recovery, you'll need to recreate the secondary. There's no way to put it back into a state where the logs can continue to be applied

2) You'll need some way of moving any changes that the users made back to the primary database (unless any work they do during the test can be discarded)

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

sporoy Hall of Fame Points: 3114 More actions · Answer 4

so I see no other way than to a new full backup restore (it will come a long way in a disk after being encrypted) , yes the test data will be discarding.. Thanks for the replies Gail,

have a nice day

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 5

We had a similar problem with DR testing and log shipping. One thing you can do is to use WITH STANDBY on the log shipping instead of NORECOVERY if your app can do anything useful in read-only mode. Assuming that you copy over the logins to match SIDs you can get full SELECT access and do at least a limited test. If UPDATE or INSERT or DELETE is required, then yeah, you are out of luck.

sporoy Hall of Fame Points: 3114 More actions · Answer 6

yes update, insert will be needed.. we'll recover them fully..