Home Dashboard Directory Help

Change in Naming Conventions between SS 2000 and SS 2005 by joeller


 as Not Reproducible Help for as Not Reproducible

Sign in
to vote
Type: Suggestion
ID: 774958
Opened: 12/21/2012 6:27:21 AM
Access Restriction: Public


In SS 2000 and 7.0, the SQL Server default when Enterprise Manager created a database was to name the data files DBName_Data.mdf and DBName_Log.ldf.    The logical names were DBName_Data and DBName_Log. Since SS 2005, it seems that the interface is by default naming the data files, DBName.mdf, DBName_log.ldf and using the logical names DBName and DBName_log.

This has caused inconsistency between those databases using the 2000 default convention and those of using 2005 convention.

This gives us issues when trying to use a dynamic SQL script to restore all the database from backup because the logical names and file names use differing conventions. (as well as those following no convention.) I would like to request that the Navy DBA here mandate that one convention or the other be used. But unless I can give a reason based on the rationale used by Microsoft to change this convention, I can not make such a request as it would entail incurring a cost which the government would not authorize without a pressing need.

I entered a forum thread in SS Forums, but the only responses state that they do it a specifc way or that I should establish a requirement that all databases do it in a specific way not understanding the restrictions that prevent me from doing so. No Microsoft employee responded to the thread to provide their rationale. Finally, someone told me to go here to get the answer, (which I find odd as this is a place for making suggestions or reporting bugs not getting answers.)

So I am again asking my question. Why did the default db naming conventions change between 2000 and 2005?
Sign in to post a comment.
Posted by joeller on 2/4/2013 at 7:32 AM
We were using Enterprise Manager. I don't see anything in the MSDN description of the Storage Engine that states that it is creating database files. So I am confused while SSMS was designed to match this capability of the storage engine.
Posted by Microsoft on 1/25/2013 at 4:51 PM
Were the SQL 2000 databases created by the SQL 2000 client IDE? It looks like the older IDE appended "_Data" while the storage engine used to also just create DBName.mdf, later changes to the client IDE (SQL Server Management Studio) were updated to be consistent with the storage engine behavior.
Sign in to post a workaround.
Posted by Nicholas Cain on 12/21/2012 at 8:43 PM
Use the RESTORE FILELISTONLY into a temp table to get the logical and physical file information, use that in your dynamic SQL statement.
Example at http://sirsql.net/2012/6/12/generic-database-restores.html
Posted by Nicholas Cain on 12/21/2012 at 8:42 PM
Read in the file information using a RESTORE FILELISTONLY into a temp table. Extract the logical and physical names from there and use that in your dynamic SQL statement. Example code at http://sirsql.net/2012/6/12/generic-database-restores.html