Post reply

How to Recover Database without Backup

  • July 17, 2009 at 8:49 am

    #208208

    Hi All,

    I have accidently droped a Database and dont have a backup. I know there is nothing I can do expect get a backup from the development server. Still would like to know if there is any way to recover the droped database.

    I dont know .. but some were I had read an article it say it is possible if you try to recover the DB files from OS level.

    Any pointers ..

    Cheers,

    Got an idea..share it !!

    DBA_Vishal

  • July 17, 2009 at 8:58 am

    #1026232

    Do u have the link to that article? It will be interesting to know how to recover database without any backups.

  • July 17, 2009 at 9:04 am

    #1026237

    Short of a backup file, I know of no way to recover a database that has been dropped.

    Why don't you have a backup of a production database? Setting up scheduled backups should be the first thing done when a database is moved to production.

  • July 17, 2009 at 10:45 am

    #1026319

    I've never heard of anyone recovering a dropped database.

    You usually can't recover from the OS because SQL Server database files are locked while SQL Server is running (and in most places, this means all the time the machine is online). With the files locked, they can't be backed up. Now, if you were taking your SQL instance offline once a night and copying the files... You could do an attach to get the database back online.

    "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

  • July 17, 2009 at 11:04 am

    #1026335

    It used to be that when you deleted a database file, the physical files were not removed. so you could attach them again. That doesn't happen any more.

  • July 17, 2009 at 11:47 am

    #1026358

    vkundar (7/17/2009)

    I have accidently droped a Database and dont have a backup.

    Boomer. Hopefully you have learned a lesson as a result of such an unfortunate chain of events.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • July 17, 2009 at 2:17 pm

    #1026438

    You may be able to use a disk utility to try to "undelete" the file, but chances of that happening on an active system decrease dramatically with every passing second as the space occupied by the deleted file can be qucikly reused by the operating system for new files and temporary use.

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

  • July 18, 2009 at 12:01 am

    #1026508

    Jeff Moden (7/17/2009)

    You may be able to use a disk utility to try to "undelete" the file, but chances of that happening on an active system decrease dramatically with every passing second as the space occupied by the deleted file can be qucikly reused by the operating system for new files and temporary use.

    thats a catch :-). I need to run disk utility to recover the physical file and then attach it ..right !

    let me try that but not so good at sys admin, any one would like to help me on this ?

    Cheers,

    Got an idea..share it !!

    DBA_Vishal

  • July 18, 2009 at 12:13 am

    #1026509

    There are some experts who do data recovery from crashed hard disks or normal hard disks for recovering data files but as Jeff said, if you'd have shut down your system, chances are those sectors would not have been overwritten by some other files. In most of the cases, the hard disk is rendered useless after this operation. Check out in your local market for data recovery experts and see if you're luckier. they open the disks and try to read data from each platter. no software tools to be used by 'anyone' for that!!


    Pradeep Singh

  • July 18, 2009 at 5:24 am

    #1026531

    Just for fun, I wanted to try this out; I had "Recover4all Professional 2.25", some old undelete program I used to recover some files a while back.

    I created a database "Whatever" and added some tables, procs and other objects .

    never backed it up, simply went into the SSMS GUI and deleted the database. I checked before hand, so I know the MDF and LDF were in the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA

    ran the program above, which took a while to scan the whole C: drive. It did not find any deleted files in the folder. none. and the steps i did were back-to-back operations.

    maybe a different program might work better, but as i understand the delete process, the pointer record for a file in the file system gets changed to a question mark, so WHATEVER.MDF becomes ?HATEVER.MDF,

    and that's how file recovery programs work...they find that pointer, and all the pointers to the clusters that used to point to in order to try and recover a file.

    I'm thinking a different delete method may be used by MSSQL?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 19, 2009 at 9:56 pm

    #1026714

    I think when you drop a database all the physical files associated with that database get automatically deleted ( mdf & ldf). There might not be in pointers in the file system to recover this files.

    "More Green More Oxygen !! Plant a tree today"

  • July 19, 2009 at 9:58 pm

    #1026717

    I think when you drop a database all the physical files associated with that database get automatically deleted ( mdf & ldf). There might not be in pointers in the file system to recover these files.

    "More Green More Oxygen !! Plant a tree today"

  • July 21, 2009 at 6:05 am

    #1027545

    Hi,

    It would be possible for you to recover the database if you have any previous backups, You can do the follwoing

    Steps to recover your tables in the database, (Hope

    you are using FULL Recovery Model)

    Step 1 : Perform a transaction log backup in your current database

    Backup log dbname to disk='drivename\txlog_final.bak'

    Step 2 : Create a New Database

    Step 3 : Restore the FULL database backup into a new database

    (Query)

    Restore database Newdbname from disk='physicaldrive\filename.bak' with

    move 'logical_data_filename' to 'new_data_physicalfile.mdf',

    move 'logical_log_filename' to 'new_log_physicalfile.ldf',

    with NORECOVERY

    Step 4 : Restore the subsequent TX log files in sequential order till the last

    transaction log file in which log backup is taken

    (Query)

    Restore log newdbname from disk='physicaldrive\txlogfile1.bak' with

    norecovery

    Continue the step 4 for all transaction log files , but do not restore the last

    transaction log file.

    Step 5 : For the Last Transaction log file , use RECOVERY option

    (Query)

    Restore log newdbname from disk='physicaldrive\txlog_final.bak' with

    RECOVERY,STOPAT = 'Aug 12, 2008 07:30 PM'

    This will revover the new database till 'Aug 12, 2008 07:30 PM'

    Step 6 : Take the full backup of new db and restore into Current DB

  • July 21, 2009 at 6:10 am

    #1027550

    Steps 2 and 6 are not required as, once you've backed up the tail of the log, you can restore over the existing database.

    Problem is that the original poster said he didn't have a backup.

    Additionally, if you are going to copy-paste someone else's work, please at the very least attribute it.

    http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/12052/Help-I-broke-an-Stored-Procedure

    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

  • July 21, 2009 at 6:49 am

    #1027569

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

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

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