Post reply

Database File names

  • 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

  • April 24, 2007 at 8:34 am

    #702181

    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

  • April 24, 2007 at 9:43 am

    #702194

    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

  • April 25, 2007 at 5:40 am

    #702335

    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.

  • April 26, 2007 at 4:13 am

    #702557

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

  • April 28, 2007 at 10:03 pm

    #703137

    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

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

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