[HELP] I did a DROP and CREATE blank records on a production database 

  • Hi there,

    My name is Kay and I work as a data analyst in Indonesia. I made a terrible mistake when I (intent to) copy the query from an old database to the live production database. Instead of copying the query, I was unaware that I did not remove the check-mark in  the checkbox on the tables icon in the Navicat software.(at that time I opted for not inserting any records)

    Alas, the whole table in the production database was dropped. Already notified my manager, and we only managed to restore the data to the 7th May 2019 (when the latest backup was created).

    I wonder if there is a way, a software, or a paid services to help me undo my wrong... (in this case, the database has already been restored to an earlier restore point, 3 weeks before the calamity happened, there is no dbase log, and the daily automatic update failed because of Harddisk capacity issue ).

    Best,

    Kay

  • Do you take server backups as well as database backups? I think that will be the only way you'll be able to recover the deleted data.

    It's a lesson for you to sort out your backup strategy. To only take backups every 3+ weeks is asking for trouble. You should be taking full backups every night and log backups hourly (depending on your RPO) and also be testing those backups to make sure you can recover in the event of failure. You also need to add more disk space too.

    Take a look at the posts from Paul Randal: https://www.sqlskills.com/blogs/paul/the-accidental-dba-day-6-of-30-backups-understanding-rto-and-rpo/

    Thanks

  • Unfortunately, we were running out of hard disk space, so that the automatic update that supposed to be performed every midnight did not perform (and we did not know about it)....

    Thanks for the response,

    K

  • There isn't much of anything you can do. You don't have any backups. You desperately need to not get just a good backup solution figured out including things like notifications when disk space is low. But you also need to have a restore strategy in place. What does that mean? Most people take regular backups but when something nasty happens they don't know what to do with those backups because they have never tested restoring data to see how the recovery portion of the process actually works. Good luck on this one. Sounds like you have a long haul in front of you.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • There are third parties that have software to recover dropped tables.

    ApexSQL https://solutioncenter.apexsql.com/4-techniques-for-recovering-lost-tables-due-to-drop-table-operation/

    I've not tried it so I can't really comment any further.

     

  • The only one of those that offers the slimmest of hopes is the log explorer. However, you better not have the log on simple recovery or that's a lost hope as well.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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