Share via

Attaching mdf and ndf from sqlserver 2000

CSE Teacher 21 Reputation points
2021-12-08T14:11:17.53+00:00

Hi folks!

I've an old sql2k database with mdf and ndf files I remember that the ndf files were created as secondary files on a partition different from the mdf. I have the files in a backup archive and want to put together the files and access the data. I Installed win2k3 server, sql2k, text editors, etc. Attaching the mdf file in the sql server brings up the db but does not ask for ndf files. Seemingly everything works but I know that I had much more data. I think they reside in the ndf files which are not loaded. Please help me to get all my data back in a single database. Thank you very much!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
0 comments
Accepted answer
  1. Seeya Xi-MSFT 16,461 Reputation points
    2021-12-09T03:32:53.553+00:00

    Hi @CSE Teacher ,

    Primary file contains startup information for the database and points to the other files in the database. Every database has one primary data file. The recommended file name extension for primary data files is .mdf.
    So, although the interface you see is like this, you can see your secondary data file in the box below.
    156133-1.png
    I did a test. I deleted the secondary data file, and then I added the main data file, you can see:
    156142-2.png
    Therefore, as long as you are in the normal attach process and do not see any wrong information, the attach is successful.

    For more information, please see:
    Database Detach and Attach (SQL Server)
    Attach a Database
    AND Different methods to attach SQL Server MDF files.
    The last link contains some screenshots, which are helpful for understanding, so I also posted it.

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 43,246 Reputation points
    2021-12-08T14:24:04.27+00:00

    In the primary database file all system informations are stored, also with database (secondary) files belongs to the database.
    If you attach the database and the database engine isn't complaining about missing NDF files, then because the NDF do not belong to the database.

    Have you check the database properties => Files to see if SQL Server attached the NDF as well?

    Please note, SQL Server 2000 is out of support since aeone.

    0 comments
  2. CSE Teacher 21 Reputation points
    2021-12-08T14:42:40.317+00:00

    Thank you for the quick reply,
    Yes, I checked the files and filegroups in the database properties. The ndf files are not there.

    0 comments
  3. Erland Sommarskog 107.2K Reputation points
    2021-12-08T22:35:50.383+00:00

    You can try this query: 

    SELECT 'SELECT COUNT(*) FROM ' + quotename(name)
FROM   sysobjects
WHERE type = 'U'

    Copy and paste the result. If that batch runs without errors, you have all data there is in this database.

    Which I think you have anyway, because as I recall, I don't think SQL 2000 permits a partial restore of only some files..

    Maybe these ndf files belonged to another database. Maybe one of them was incorrectly name .ndf, despite that it was a primary file. (SQL Server has no hard rules on the file extension:)

    0 comments