Post reply

Can I recover Missing Database File From Backup?

  • July 1, 2016 at 7:57 am

    #314794

    So another DBA stopped the SQL Server service and deleted a .ndf file last night in the Dev environment as part of some maintenance. The wrong one was deleted and now I have a database that will not come online. I have a full back up from 6/26. Can I use that to restore that file? The database state is currently "Recovery Pending."

  • July 1, 2016 at 7:59 am

    #1888095

    first make a tail log backup

    then you can use 'restore filegroup ' starting from your latest full backup

    then restore diff backup if available

    then restore logs as needed ending with the one produced with the tail log backup

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • July 1, 2016 at 8:07 am

    #1888097

    Providing you are in full recovery model and have an unbroken chain of log backups from before the full backup until now.

    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 1, 2016 at 8:15 am

    #1888099

    The database is in Simple recovery and we keep one full backup at a time. Looks like based on the below I would be out of luck because my file group is not read only. In order to make use of this type of recovery I think I may plan on changing the recovery model to full, even in non-prod, and then take transaction log backups. Definitely learned a few things today.

    https://msdn.microsoft.com/en-us/library/aa337540.aspx

    In the meantime I did find out that the DBA signed onto the machine with their own Windows credentials to do the file deletion and the file went to their Recycle Bin. I was looking under the primary account that most of us use to connect and had not seen the file in the Recycle Bin their so I thought it was gone. The file is restored and I restarted the SQL Server service to restart the recovery process. The database is online now.

  • July 1, 2016 at 8:39 am

    #1888107

    lmarkum (7/1/2016)

    The database is in Simple recovery and we keep one full backup at a time. Looks like based on the below I would be out of luck because my file group is not read only.

    You would have been, yes.

    Glad to hear it was recoverable, usually DB files don't go to the recycle bin because they're too large.

    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 3, 2016 at 11:56 pm

    #1888246

    Thank you for the feedback.

    Glad to read to got it fixed.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • July 6, 2016 at 11:29 pm

    #1888841

    Open Microsoft SQL Server Management Studio. Right-click on the Database and select 'Restore Database'. A popup window will appear. Paste the backup file location in the option for 'Devices'. In the destination, select the database name. Click 'OK', and a new database will be created. Remove the existing database.

  • July 7, 2016 at 1:05 am

    #1888851

    SQL Programmers (7/6/2016)

    Open Microsoft SQL Server Management Studio. Right-click on the Database and select 'Restore Database'. A popup window will appear. Paste the backup file location in the option for 'Devices'. In the destination, select the database name. Click 'OK', and a new database will be created. Remove the existing database.

    The OP didn't want to restore the whole database... just one file.

    --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 7, 2016 at 5:46 am

    #1888884

    Thanks for the time to provide responses that helped me learn something about this situation. Much appreciated.

  • August 23, 2016 at 1:31 am

    This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

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