Do you know the difference between .mdf, .ndf and .ldf files?

This is a typical exam or interview questions e.g.

A SQL Server file with a .mdf extension is the following (tick all options):

• Primary data file
• Secondary data file
• Database log file

The answer could be all of them or none of them! SQL Server does not enforce file names, so you can name the database files to be anything you want with whatever extension that your fancy i.e.

USE [master]
GO

CREATE DATABASE [ConfusingDB] ON  PRIMARY
( NAME = N'TestDB_Primary', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ConfusingDB.ldf' , SIZE = 2512KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1KB )
LOG ON ( NAME = N'TestDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ConfusingDB.mdf' , SIZE = 512KB , MAXSIZE = 2048GB , FILEGROWTH = 1KB)
GO

If you look at the topic "Understanding Files and Filegroups" in Books Online, it  only says that the file extensions are recomended. If a file is used by an attached database you can determine what type of file it is by looking in sys.master_files.

This reminds me of an occasion when a friend rang me becase he was having problems attaching a database. So I asked him what error message he was getting, and it turned out to be the following:

Msg 1829, Level 16, State 3, Line 1The FOR ATTACH option requires that at least the primary file be specified.

Further questioning revealed that he had noticed that the .ldf file was very large, so he’d decided to detach the database, delete the log file and then re-attach the data file. Unfortunately the files names for the log file and data file had been transposed when someone had tried to move them. The error message was dead on the mark and tells you exactly what the problem was.

Read more: Martin Bell UK SQL Server MVP

Posted via email from .NET Info