Multiple mdf, empty one is missing

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

Answer 1

Might sound silly but first thing, check your recycle bin. If the secondary file was small/empty it may still be there.

IF it is, shut off sql server, go to recycle bin, right click the file and choose restore. Then restart sql server and see if it works.

IF you did not drop the secondary file from the filegroup then the database expects there to be more files, so the error "inaccessible files" makes sense.