none
Multiple mdf, empty one is missing RRS feed

  • Question

  • Hi guys i have a big problem. I know i did such stupid and unforgivable mistake. And know please help me if you can. I wanted to make copy of database. I should make it by backup and then restore as another database but faster option was to stop mssql server service, copy files and then attach as new database. The problem is that few minutes eariel i have created new mdf file on primary filegroup in my database i wantedo to copy. That was empty mdf file, just new one. Then i stoped mssql service and deleted that new empty mdf file from hard drive manually. I dont know why, i thought that: "this was just for tests we dont need anymore". When i copied files, i started mssql service and my database is in "recovery Pending" mode and cant find that empty mdf file. Even if i create the same file from another database ofcourde mssql server knows that this is not proper file. I cant make alter table remove file beacuse of error:

    Database cannot be opened due to inaccessible files or insufficient memory or disk space

    Is there any possibilities to recover that database? I have backup but i will lost data from one day.

    Please help if you can

    Saturday, August 17, 2019 4:17 AM

All replies

  • Possibly you could create a new empty data file for some other database, and then edit that file in a hex editor to have the correct values for database, file id, LSN etc. That is, you would need quite some internals knowledge to do this. The checksum for the pages you don't touch would not match, but I think you can get around that by creating an empty database with the same set of files, and then turn off page verification of that database, detach the files, and put the files of this database in place.

    As you may realise, this could take several days to get right, so restoring the backup is likely to be the best option.

    You could try Orcas MDF to get as much as of the data from the last day out of the database. Look here: https://improve.dk/introducing-orcamdf/

    But do you only have a full backup? Are you not backing up the transaction log? With T-Log backups you can reduce the data loss considerably.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, August 18, 2019 10:10 AM
  • Hi Ukasy,

     

    I think if you don't have the habit of regular log backup, then you need to bear the risk of data loss. According to your description, this is a production database, so there is not much time to deal with this problem. Please remember to do regular log backups.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, August 19, 2019 7:41 AM