Changing default logical filename in SQL 2005

Question

I have a issue about creating databases in SQL 2005.

I want to be able to change the default logical filename for the mdf file. At the moment the log logical filename ends in _log by default. I want the data logical filename to automatically end with _data for consistency.

Is there a way i can set this?

Andrew

Answer 1

The only way to be certain what the database logical name(s) will be is to specify them when the database is created. If you script your database creation statements, this is trivial, as per this template (sorry, but I like having "_log" on my log files):

CREATE DATABASE MyDB
  on primary
   (name = MyDB,      filename = 'C:\SQL_DataFiles\MyDB.mdf'
    ,size = 10MB, maxsize = unlimited, filegrowth = 20%)
  log on
   (name = MyDB_log,  filename = 'C:\SQL_DataFiles\MyDB_log.ldf'
    ,size = 2MB, maxsize = unlimited, filegrowth = 10%)

I'd have to imagine that SSMS also allows you to specify the logical name when you use the GUI to create a database. If you don't specify, SQL has to "guess" at appropriate file names, and since all names within a database have to be distinct from each other, tagging a "_log" on the end of the log file(s) is an obvious default choice by Microsoft.

To change the current names is simple, though you have to know the current name to be changed:

ALTER DATABASE MyDB
 modify file (name = WrongName, newname = CorrectName)