This article revolves around a trivial yet common issue. There might be a set of people for whom the current topic might appear to be insignificant. But I have been asked this question innumerable times, particularly from  people who are frequenting using forums or have blog related to storage and highly availability, which instigated me to write this article.
Here goes this frequently asked question.
Question: Is it possible to restore database if one of the files of .mdf (primary data file) or .ndf (secondary data file) is missing?
Answer: In one word the answer is NO. All the .mdf and .ndf files are mandatory to attach or restore database successfully. Even though you have all the transactions stored in .ldf you will not be able to restore the database completely.
I hope this answer removes the cloud of doubt hovering over my targeted readers.
Reference : Pinal Dave https://blog.sqlauthority.com/ )
10 Comments. Leave new
Its good to know the answer, but that would be great if you explain your readers the reason behind ‘No’.
Hi ALL,
Pinal stated in brief but he is correct. For freshers just simplify the things, Suppose if database contains 3 .mdf and 2 .ndf and 1 .ldf files. And play puzzle like this. Stop the SQL Server service and delete physically one of .mdf file. After that database comes in restoring position. Only the method you can brigs the database in active position when you add missing file. You must follow the rules of database recovery as by PINAL’s article. It require the previous or last backup which contains all .mdf, .ndf & .ldf files information/data. Also you can play more puzzles with Pinal.
Regards,
Rajiv Singh
HI Pinal ,
I have a problem with attach DB .
When I am going attach the DB , it’s failing .
The Error Message is :
Msg 1813, Level 16, State 2, Line 2
Could not open new database ‘eCompassHK_Data_1′. CREATE DATABASE is aborted.
Msg 824, Level 24, State 2, Line 2
SQL Server detected a logical consistency-based I/O error: incorrect pageid
(expected 1:6148838; actual 0:0). It occurred during a read of page (1:6148838) in database ID 8 at
offset 0x00000bba5cc000 in file ‘G:Microsoft SQL ServerMSSQL.1MSSQLDATAeCompassHK_Data.MDF’.
Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 3313, Level 21, State 2, Line 2
During redoing of a logged operation in database ‘eCompassHK_Data_1′,
an error occurred at log record ID (2857078:303:410). Typically, the specific failure is previously
logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Please Help me on this …….
Thanks
Narendra
i think it is possible
just need to write following sample statement
CREATE DATABASE MyDB2
ON PRIMARY
(FILENAME = ‘c:dataMyDB2_Prm.mdf’),
(FILENAME = ‘c:dataMyDB2.ldf’)
for attach
go
Hi Pinal.
I have a DB with an mdf, ndf and ldf file. Can I attach the DB with only the mdf and ldf file in the create for attach sql. I currently do that and it works? Is it not essential that the ndf file be part of the create-attach command?
Thanks
S. S.
quick question. I have a backup of a database, the database had 1 .mdf, 1 .ndf and 1 .ldf. When I restore I want to eliminate the .ndf and move the data into the .MDF. Is this possible.
Hi Pinal
Our fancy NAS died the other day and the secondary filegroup for one of our databases was stored on those disks.
Is it possible for a workaround, some tool we could use to restore parts of the database. We have all the importent data in primary filegroup. In the secondary we store telemetry data wich we can afford to loose.
And yet another question.
I have a DB with mdf several ndf’s and a ldf.
I did the backup as shown below.
RESTORE DATABASE [test_db]
FILEGROUP = N’PRIMARY’ TO DISK = N’N:\Location\Test_DB.BAK’ WITH NOFORMAT, INIT,
NAME = N’Test_DB’, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N’Test_DB’ and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N’Test_DB’ )
if @backupSetId is null begin raiserror(N’Verify failed. Backup information for database ”Test_DB” not found.’, 16, 1) end
RESTORE VERIFYONLY FROM DISK = N’N:\Location\Test_DB.BAK’ WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
So I only did a backup of my PRIMARY the ndf’s are additional files.
It is now possible to restore the DB using qry
RESTORE DATABASE [Test_DB]
FILEGROUP = N’PRIMARY’ FROM DISK = N’N:\Location\Test_DB.BAK’
WITH RECOVERY,
MOVE ‘Ezis_prod_Data’ TO ‘S:\SQL Data\Test_DB_Data.mdf’,
MOVE ‘Ezis_prod_Log’ TO ‘X:\SQL Log\Test_DB_Log.ldf’;
GO
So it is possible.
Hello Sir,
I have a question..
Say suppose if I lost my x.mdf file and I have complete x.ldf file ( I mean that, all the modifications which are made on my database right from the creation of my database are present in my .ldf file).
Is it possible to create a dummy database with some name. And then taking that dummy.mdf and renaming that with the desired name (x.mdf) and replacing the dummy.ldf file with x.ldf. and then to attach the database?