Database File names

Question

Database File names

David Wilson-432897

SSC Veteran

Points: 206
More actions
April 24, 2007 at 5:53 am

#173098

SQL Server data seems to live in two files in the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder, eg XXXX.mdf and XXXX_log.ldf
my question is how to get XXXX to be the same name as the Database name I am using inside MS SQL Server management Studio Express?
right clicking on the database, then properties/Files shows me the File name in the Logical Name column, and I can change it, but this does not change the name of the actual file
help will be appreciated

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello David,

First take a full backup with the name you desired. Now you restore back the backed-up database with the name desired and here you can change the file names.

Hope this helps.

Thanks

Lucky

David Wilson-432897 SSC Veteran Points: 206 More actions · Answer 2

GOT IT!

a diabolical interface! but if you persist in through the Database Properties/Files - there is a column into which you can enter the new names of the db & log files, & you must Delete the Database first (which is a bit twitchy)

I think the backup idea might have worked too, but now the job is done

thanks & regards

Chris Chilvers SSCrazy Points: 2260 More actions · Answer 3

Another way is to detach the files, rename them in explorer, then reattach them

If the database is called Foo

and has the files:

Foo.mdf

Foo_log.ldf

and you want to rename to

Foo.mdf

Foo.ldf

EXEC

master.dbo.sp_detach_db @dbname = N'Foo', @keepfulltextindexfile=N'true'

GO

--Rename foo_log.ldf to foo.ldf

CREATE

DATABASE Foo ON

( FILENAME = N'C:\path\foo.mdf' ),

( FILENAME = N'C:\path\foo.ldf')

FOR ATTACH

This can also be done via the GUI as well using the detach/attach commands.

When you locate the mdf file when reattaching it will tell you the files are missing, in the bottom half, scroll right and you can tell it the new location of the files.

David Wilson-432897 SSC Veteran Points: 206 More actions · Answer 4

agradecido 🙂 for a comprehensive, explicit, and most useful summary

jezemine SSCrazy Eights Points: 8059 More actions · Answer 5

Generally you do not want to have the data/log files on the C: drive. that's the OS partition, so if your database gets large and the C: drive fills up, you'll bring your entire server down.

---------------------------------------
elsasoft.org