SQL SERVER – Restore or Attach Database Without .NDF or .MDF is Not Possible

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 (http://blog.sqlauthority.com)

About these ads

11 thoughts on “SQL SERVER – Restore or Attach Database Without .NDF or .MDF is Not Possible

  1. 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

  2. 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 Server\MSSQL.1\MSSQL\DATA\eCompassHK_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

  3. i think it is possible
    just need to write following sample statement

    CREATE DATABASE MyDB2
    ON PRIMARY
    (FILENAME = ‘c:\data\MyDB2_Prm.mdf’),
    (FILENAME = ‘c:\data\MyDB2.ldf’)
    for attach
    go

  4. 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.

  5. 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.

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #024 | SQL Server Journey with SQL Authority

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

  8. 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.

  9. 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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s