How to Recover Database without Backup

  • praveen_manyam (7/21/2009)


    Well you got the link,,, i did'nt, i got tht from my inbox from one of my friends

    You should still acknowledge that what you're posting is not your own work. Even if it's just "A friend sent me this, don't know where it came from, think it may help" Without that it will be assumed that the script is your original work.

    btw, it took me maybe 30 sec to find that link with google.

    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
  • We have a 3rd party product called Apex SQL Recover. You can recover dropped tables w/o any backups. However, (As Jeff Modem stated) it reads the disks and the longer you go, the possibility exists that some data got overlaid.

    Tim White

  • This was removed by the editor as SPAM

  • crickwilli (8/20/2015)


    Get more effective safe and secure software, you can download third-party software, Kernel for SQL Database Recovery Tool. This software easy to recover complete database objects available in .MDF files such as tables, views, stored procedures, triggers, indexes, primary keys, unique keys, foreign keys and etc. That software supported all update version MS SQL Server 2014/2012/2008 R2/2008/2005.

    The problem is that the OP dropped the database which deletes the .MDF file, so there's no .MDF file to do this with. The .MDF file would first have to be undeleted at the OS level and the chances of that happening go to near zero very quickly on an active system because the disk space where the deleted file lives can be quickly overwritten.

    I would also imagine that the same holds true for individual objects that have been dropped. The parts of the MDF file that contain dropped objects and will be overwritten, sometimes in an instant.

    My point is that if you don't have backups, don't plan on being saved by such tools.

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

  • I guess the real questions at this point is not "how to I recover a dropped database?" but rather "how do I recover a deleted file?".

    How Volume Shadow Copy Service Works

    https://technet.microsoft.com/en-us/library/cc785914(v=ws.10).aspx

    ShadowExplorer

    http://www.shadowexplorer.com/

    19 Free Data Recovery Software Tools

    http://pcsupport.about.com/od/filerecovery/tp/free-file-recovery-programs.htm

    Hopefully there hasn't been much action on the server, since the database has been offline. However, the more time that passes, the less chance you have for recovery of the .mdf file. Each write to disk can potentially overwrite portions of your database files. So, install and run the file restore software from an external drive, and restore the files to an external drive.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • I guess the real questions at this point is not "how to I recover a dropped database?" but rather "how do I recover a deleted file?".

    How Volume Shadow Copy Service Works

    https://technet.microsoft.com/en-us/library/cc785914(v=ws.10).aspx

    ShadowExplorer

    http://www.shadowexplorer.com/

    19 Free Data Recovery Software Tools

    http://pcsupport.about.com/od/filerecovery/tp/free-file-recovery-programs.htm

    Hopefully there hasn't been much action on the server, since the database has been offline. However, the more time that passes, the less chance you have for recovery of the .mdf file. Each write to disk can potentially overwrite portions of your database files. So, install and run the file restore software from an external drive, and restore the files to an external drive.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • This will not help the OP but he may want to consider it in future.

    I have a non-production server where the users have a habit of dropping DBs!

    I have saved mayself a lot of time by having hard links to the database files in a hidden directory.

    eg

    C:\>MkLink /h C:\Hidden\MyDB.mdf C:\SQLData\MyDB.mdf

    C:\>MkLink /h C:\Hidden\MyDB_log.ldf C:\SQLData\MyDB_log.ldf

    If MyDB gets dropped, then all I need to do is:

    C:\>MkLink /h C:\SQLData\MyDB.mdf C:\Hidden\MyDB.mdf

    C:\>MkLink /h C:\SQLData\MyDB_log.ldf C:\Hidden\MyDB_log.ldf

    CREATE DATABASE MyDB

    ON (FILENAME = 'C:\SQLData\MyDB.mdf')

    ,(FILENAME = 'C:\SQLData\MyDB_Log.ldf')

    FOR ATTACH;

  • For users who have a habbit of dropping databases, I've found the following works too.

    ALTER SERVER ROLE [sysadmin] DROP MEMBER [username];

    And, if they know the password for 'SA' account, then I'll do this:

    use MASTER

    go

    alter login [sa] DISABLE;

    go

    alter login [sa] with name = [sa_bak];

    go

    create login [sa] with PASSWORD = '...', DEFAULT_DATABASE = master;

    go

    use Accounting;

    go

    drop user [sa];

    go

    create USER [sa] for login [sa];

    go

    exec sp_addrolemember 'db_datareader', 'sa';

    exec sp_addrolemember 'db_datawriter', 'sa';

    go

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Eric M Russell (8/20/2015)


    Hopefully there hasn't been much action on the server, since the database has been offline.

    Well, considering that it has been 6 years now since the "accidental drop", I guess there has been a lot of action on the server. 😀 :hehe:

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (8/20/2015)


    Eric M Russell (8/20/2015)


    Hopefully there hasn't been much action on the server, since the database has been offline.

    Well, considering that it has been 6 years now since the "accidental drop", I guess there has been a lot of action on the server. 😀 :hehe:

    Hey, we're just practising Case Law here. 🙂

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Can anyone restore a WP DB to a point before it was overwritten?

  • You'll get a lot better response posting that question to the main forums. The only people who will see are those who responded to this six year old thread.

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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 12 posts - 16 through 27 (of 27 total)

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