Database Restore-Pls Help me

• Thayalan

  SSC Enthusiast

  Points: 194

  More actions

  July 23, 2009 at 6:15 am

  #208437

  Hi,

  I have few doubts in SQLServer Restore process. I have a SQLSERVER 2005 enterprise edition database named “Test” in full recovery mode and it has “Emp” table.

  NameAgeSex

  Xxxxxxx29male

  Yyyyyyy30female

  Zzzzzzzz45male

  The scenario is as follow as

  10:00 AM – Full Backup

  10:30 AM-user1 updates emp table(by mistake), now the table looks like

  NameAgeSex

  Xxxxxxx29male

  Yyyyyyy30female

  Zzzzzzzz45female

  11:00 AM- Transactional Backup

  11:30 user2 updates emp table, table looks like

  NameAgeSex

  Xxxxxxx30male

  Yyyyyyy30female

  Zzzzzzzz45female

  Now, I am informed that the user1 had updated the emp table by mistake and he wants to rollback. I checked with user2 who updated the emp table by 11:30,

  He says he cannot enter the values into emp table again. So I prepared a restore plan as:

  Step 1: Take a tail log backup

  Backup log Test to Disk=’D:\Testtaillogbackup.trn’ with no_truncate

  Step 2: Restore from full backup

  Restore backup test from disk=’D:\TestFulllbackup.bak’ with norecovery

  Step 3:Pointintime Restore from T-Log backup

  Restore log test from disk=’D:\Testlogbackup.trn’ with norecovery, STOPAT= ‘Jul 23, 2009 10:20 AM'

  Step 4: Restore from the tail log backup

  Restore log test from disk=’D:\ D:\Testtaillogbackup.trn’ with recovery.

  Here the restore fails due to some LSN mismatch.

  My Questions are.

  What I am doing wrong

  Is it possible to fulfill my requirement.

  Kindly help me to solve the issue.

  Thanks,

  Sathyalan

• allen davidson

  SSCertifiable

  Points: 7042

  More actions

  July 23, 2009 at 6:22 am

  #1028887

  I don't think you can do that because you would have a gap in the LSN sequence (the Log chain).

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 23, 2009 at 6:42 am

  #1028896

  By using STOP AT, you're saying that you want to stop the restore at a particular point in time. No transactions after that point can be restored. You cannot then go and restore another log backup.

  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

• homebrew01

  SSC Guru

  Points: 55548

  More actions

  July 23, 2009 at 9:55 am

  #1029096

  1) Restore with STOPAT = 10:29, then manually re-enter the correct transactions after that point

  or

  2) Restore database to a new name TEST_1029 at 10:29. Then restore the full database again to TEST_1130. Then extract the correct records from TEST_1029 ..EMP and TEST_1130 ..EMP into EMP_FIXED and push that data back to TEST..EMP. (Just a rough example)

  There may be other options based on the real situation and the real data and how much "bad" data you have, timing, current & new data ......