Post reply

Rename database physical file

  • February 3, 2005 at 9:16 am

    #87389

    Is there a way to rename a database's physical file name? If so how? I can rename the logical file easily enough, however the physical file proves to be a challange. I have tried detaching the database, renaming the Data MDF file and then reattaching the database to no avail. Any help will be greatly appreciated.

  • February 3, 2005 at 9:52 am

    #540435

    Backup the database then restore using the WITH MOVE option, this will allow you to designate the name differently. If the above does not work.

  • February 3, 2005 at 9:52 am

    #540434

    Daniel,

    You don't mention if you want to rename and retain the log file as well as the data file.  If you want a new log file to be created when you attach the database, you must use sp_attach_single_file_db. 

    Example:  

    EXEC sp_detach_db @dbname = 'pubs'EXEC sp_attach_single_file_db @dbname = 'pubs',    @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

    If you want to retain the log file, you must specify both files when you attach.

    EXEC sp_attach_db @dbname = N'pubs',    @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',    @filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

    Hope this helps.
    Greg

    Greg

  • February 3, 2005 at 10:08 am

    #540446

    Thank you guys! You wouldn't believe how long I have been scratching my head over this. I ended up backing up the database and then doing a restore and changing the physical file name in the "restore as" column. Thanks goes out to both of you for such a quick response.

    Thanks, db

Viewing 4 posts - 1 through 4 (of 4 total)

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