mdf and ldf file extensions

Hello All,
One of my customers created a database in SQL 2K SP3a. He gave the data
file LDF extension and log file MDF extension.
When I do sp_helpdb 'dbname', the extensions are pointing to the correct
files, meaning LDF is pointing to the data file and MDF is pointing to the
log file just as he designed.
My question is: is it just a standard suggested by Microsoft to name the
data file with MDF extension and log file with LDF and NDF extensions? Is
there going to be any repercussions later on if we name the files with wrong
extensions.
I though it was odd that SQL Server let the files be named with wrong
extensions in the first place.
Any ideas?
Thanks,
BivaThis will not confuse SQL Server. SQL Server doesn't rely
on the file name extension to determine the type of the
file or how it deals with the file. But it may be
confusing to a DBA, and wouldn't be considered best
practice from a naming convention standpoint.
If you don't like the current naming, you can detach the
database, rename the files to the conventional extensions,
and then attach the database using the renamed files.
Linchi
>--Original Message--
>Hello All,
>One of my customers created a database in SQL 2K SP3a.
He gave the data
>file LDF extension and log file MDF extension.
>When I do sp_helpdb 'dbname', the extensions are pointing
to the correct
>files, meaning LDF is pointing to the data file and MDF
is pointing to the
>log file just as he designed.
>My question is: is it just a standard suggested by
Microsoft to name the
>data file with MDF extension and log file with LDF and
NDF extensions? Is
>there going to be any repercussions later on if we name
the files with wrong
>extensions.
>I though it was odd that SQL Server let the files be
named with wrong
>extensions in the first place.
>Any ideas?
>Thanks,
>Biva
>
>.
>|||> My question is: is it just a standard suggested by Microsoft to name the
> data file with MDF extension and log file with LDF and NDF extensions?
The pedantic part of me is tugging to correct this. By default, at least.
Data files have MDF (and NDF extensions, in the case of multiple files).
Log files have LDF extensions (whether singular or multiple).
You can certainly override this behavior. I'm not certain that there are
any advantages of doing so, and I can certainly think of some drawbacks.
But there it is.
--
Aaron Bertrand
SQL Server MVP|||Hi Biva,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
You concern is if the MDF, NDF, LDF is just a suggested standard by
Microsoft and if there problem when change the extension to these files,
right?
When create a database, you should set a filename of the data, log, etc.
These fine name and extension should be specified (they are 'os_file_name'
when you refer it to 'create database' in BOL, SQL Server Books Online).
Although 'os_file_name' can be any valid operating system file name, the
name more clearly reflects the purpose of the file if you use the following
recommended extensions.
You can create a database like this:
USE master
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\saledat.mdfx',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\salelog.ldfx',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
As we know, the system table 'master' will control the user databases and
operation of SQL Server as a whole by keeping track of all information of
them. When you use:
Use master
Go
Select * from sysdatabases
You will get the fileinformation of sales with the extension you defined,
that is
'c:\program files\microsoft sql server\mssql\data\saledat.mdfx'
and you can create table or execute other operations on it.
But if you revise the extension of the file and thenuse the database again,
the SQL Server will notify you that the file is not exist although there
may be someway de recover. It is strongly suggested you to keep the
suggested file extension by Microsoft.
Hope this answered your questions. If you still have more concern about it,
please feel free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.