Post reply

Recover data after drop table

  • July 4, 2019 at 2:38 pm

    #3660031

    I have a database which is partitioned.  Part of maintenance for a particular table is to switch a partition to another table in order to remove the unwanted oldest month. I am testing a scenario in which the script would be run twice accidentally, and checking if data could be recovered without doing a full restore. The file itself wasn't deleted yet.

    I would need to find a way to switch the partition again to the former table. Is that possible when the switchout table no longer exists ?

     

  • July 5, 2019 at 3:10 pm

    #3660222

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • July 5, 2019 at 3:28 pm

    #3660229

    Which "file"? The database file?

     

    Once the switch and delete is logged, AFAIK, you cannot recover this data from the partitioned table without a full restore. What I might do instead in the script is specify which date/partition you are trying to delete and only delete the matching one. More coding, but if it runs twice accidentally, this shouldn't cause an issue.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • July 5, 2019 at 9:57 pm

    #3660319

    oskargunn wrote:

    I have a database which is partitioned.  Part of maintenance for a particular table is to switch a partition to another table in order to remove the unwanted oldest month. I am testing a scenario in which the script would be run twice accidentally, and checking if data could be recovered without doing a full restore. The file itself wasn't deleted yet. I would need to find a way to switch the partition again to the former table. Is that possible when the switchout table no longer exists ?  

    You should never write code that simply drops the oldest partition.  You should explicitly calculate which partition it is that you want to drop so that accidental reruns don't kill you by mistake.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 12, 2019 at 11:21 am

    #3662348

    At the moment I only remove oldest partition manually, so I know which partition to delete. As test case I want to have scripts ready if there comes a problem like this 🙂

    Normally the script creates a backup of the data I want to remove as a separate database (create database, copy data). It's done like that to have a complete month as a database which can be restored at a later date if needed. Last step is to switch last partition (the data that was copied into new database) into a new table in source db, then drop that table and remove the partition from original table.

    I intentionally ran the switch-out twice, so the partition was switched to another table, that table was deleted, but original partition is still visible in the original table, but of course there is no data since it was switched.   The physical file itself which contains/contained the data for that particular month was not deleted. What I would like to know is if that data can somehow be switched back to the original partition without doing a full restore. It's just good to know if it is not possible 🙂

     

     

Viewing 5 posts - 1 through 4 (of 4 total)

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