Someone Deleted a Table

  • tfifield

    SSCrazy Eights

    Points: 9655

    Oooops! A friend of mine deleted a table used to import order data from hand held devices into the main Point Of Sale system. The database is 2005 SP3. The deleted table doesn't affect regular order processing - only the import from the hand helds. Right now the site is up and running on normal order entry so I can't take them off line to restore the last backup and transaction logs up to the drop table.

    The database is in full recovery model and there are transaction log backups done every 15 min.

    Does anyone know of a 3rd party tool that can apply the transaction log backups to a single table?

    My plan was to restore that last full backup to a different database, copy the missing table over and then somehow get the transactions applied to the table up to the point just before the table was dropped.

    Todd Fifield

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Restore to a new DB, restore logs, copy data over.

    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
  • tfifield

    SSCrazy Eights

    Points: 9655

    Gail,

    Thanks. I've actually never restored logs to a different database than the original. Is there a write up of some kind on this?

    Todd Fifield

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Nothing different.

    RESTORE DATABASE <new name> FROM DISK = <backup location>

    WITH MOVE <files to a new place>, NORECOVERY

    RESTORE LOG <new name> FROM DISK = <Backup location>

    WITH NORECOVERY

    ..

    --repeat for rest of logs

    RESTORE DATABASE <new name> WITH RECOVERY

    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
  • tfifield

    SSCrazy Eights

    Points: 9655

    Gail,

    Thanks a lot. I'm now going to try to bail my friend out of trouble.

    Todd

  • tfifield

    SSCrazy Eights

    Points: 9655

    Well, everything worked fine thanks to Gail. However, when we were restoring the logs we stopped one short of where we really wanted to be and then did the WITH RECOVERY step. I've checked BOL and I can't seem to find out how to put the database back into a recovering state so the last log can be restored. I've actually never had to do this before.

    Anyone got any ideas?

    Todd Fifield

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    tfifield (9/20/2011)


    Well, everything worked fine thanks to Gail. However, when we were restoring the logs we stopped one short of where we really wanted to be and then did the WITH RECOVERY step. I've checked BOL and I can't seem to find out how to put the database back into a recovering state so the last log can be restored. I've actually never had to do this before.

    Anyone got any ideas?

    Todd Fifield

    You have to start over.

    This is why we use script... can reuse all the work you already did... 😉

  • tfifield

    SSCrazy Eights

    Points: 9655

    Ninja's_RGR'us (9/20/2011)


    tfifield (9/20/2011)


    Well, everything worked fine thanks to Gail. However, when we were restoring the logs we stopped one short of where we really wanted to be and then did the WITH RECOVERY step. I've checked BOL and I can't seem to find out how to put the database back into a recovering state so the last log can be restored. I've actually never had to do this before.

    Anyone got any ideas?

    Todd Fifield

    You have to start over.

    This is why we use script... can reuse all the work you already did... 😉

    Remi,

    Thanks - I was afraid of that.

    Todd Fifield

  • Gail Shaw

    SSC Guru

    Points: 1004484

    tfifield (9/20/2011)


    Anyone got any ideas?

    Todd Fifield

    Drop database, start again.

    You can put the DB back into restoring easy enough, but it still won;t allow another log restore.

    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
  • tfifield

    SSCrazy Eights

    Points: 9655

    GilaMonster (9/20/2011)


    tfifield (9/20/2011)


    Anyone got any ideas?

    Todd Fifield

    Drop database, start again.

    You can put the DB back into restoring easy enough, but it still won;t allow another log restore.

    Thanks Gail. I found it out the hard way - ouch!.

    After nearly 11 years working with SQL Server this is the first time I've ever had to do this. I don't go around deleting or truncating tables. Now I'll probably write some sort of script or procedure to get what I want out of MSDB and generate the restore statements.

    My friend now owes me tickets to a Dodgers game!

    Todd Fifield

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    tfifield (9/20/2011)


    GilaMonster (9/20/2011)


    tfifield (9/20/2011)


    Anyone got any ideas?

    Todd Fifield

    Drop database, start again.

    You can put the DB back into restoring easy enough, but it still won;t allow another log restore.

    Thanks Gail. I found it out the hard way - ouch!.

    After nearly 11 years working with SQL Server this is the first time I've ever had to do this. I don't go around deleting or truncating tables. Now I'll probably write some sort of script or procedure to get what I want out of MSDB and generate the restore statements.

    My friend now owes me tickets to a Dodgers game!

    Todd Fifield

    You can do that in the restore GUI.

    Pick your db, the PIT to which you want to restore to. Then SCRIPT & manually add the with move and new db name (ideally you'd do the restore on another server). Make sure you don't select the with replace option. That way you can't accidentally erase the prod db altogether.

  • tfifield

    SSCrazy Eights

    Points: 9655

    Ninja's_RGR'us (9/20/2011)


    tfifield (9/20/2011)


    GilaMonster (9/20/2011)


    tfifield (9/20/2011)


    Anyone got any ideas?

    Todd Fifield

    Drop database, start again.

    You can put the DB back into restoring easy enough, but it still won;t allow another log restore.

    Thanks Gail. I found it out the hard way - ouch!.

    After nearly 11 years working with SQL Server this is the first time I've ever had to do this. I don't go around deleting or truncating tables. Now I'll probably write some sort of script or procedure to get what I want out of MSDB and generate the restore statements.

    My friend now owes me tickets to a Dodgers game!

    Todd Fifield

    You can do that in the restore GUI.

    Pick your db, the PIT to which you want to restore to. Then SCRIPT & manually add the with move and new db name (ideally you'd do the restore on another server). Make sure you don't select the with replace option. That way you can't accidentally erase the prod db altogether.

    Thanks Remi. I'll try that. It was kind of a PITA to do all of the logs by hand, but we did get it done with no data loss.

    Todd

  • Gail Shaw

    SSC Guru

    Points: 1004484

    tfifield (9/20/2011)


    Ninja's_RGR'us (9/20/2011)


    You can do that in the restore GUI.

    Pick your db, the PIT to which you want to restore to. Then SCRIPT & manually add the with move and new db name (ideally you'd do the restore on another server). Make sure you don't select the with replace option. That way you can't accidentally erase the prod db altogether.

    Thanks Remi. I'll try that. It was kind of a PITA to do all of the logs by hand, but we did get it done with no data loss.

    Todd

    However restoring by hand is good practice for cases where you don't have the MSDB backup history around. Use the tools, but be sure you can do it without them too.

    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
  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    GilaMonster (9/21/2011)


    tfifield (9/20/2011)


    Ninja's_RGR'us (9/20/2011)


    You can do that in the restore GUI.

    Pick your db, the PIT to which you want to restore to. Then SCRIPT & manually add the with move and new db name (ideally you'd do the restore on another server). Make sure you don't select the with replace option. That way you can't accidentally erase the prod db altogether.

    Thanks Remi. I'll try that. It was kind of a PITA to do all of the logs by hand, but we did get it done with no data loss.

    Todd

    However restoring by hand is good practice for cases where you don't have the MSDB backup history around. Use the tools, but be sure you can do it without them too.

    +100 on this one.

    I have designed my whole DR strategy based on the fact that msdb will also be whacked. I rebuild the script solely based on the backup file names.

    It's actually just as quick as the GUI, but with the certainty that it can't fail (because I've tested the script and I won't change it without fully retesting). With the GUI you can always forget or add an option by accident that makes the whole thing fail.

    I had a real life test run with it 2 weeks ago.

    I was out of office, I had never shown how to use the script and I was able to restore the prod DB over the phone on the first try & with minimal downtime without seeing the screen.

    I call that a successful 'test' run ;-).

    By now I know a monkey with a little training can start the restore of the prod DB in 2 minutes flat (no offense to the IT guys here :hehe:).

  • tfifield

    SSCrazy Eights

    Points: 9655

    Remi,

    I can think of a couple of ways to do this using file names or dates. I'm curious as to how you do it?

    Todd Fifield

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

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