SQL Server Performance

.mdf and .ldf

Discussion in 'Getting Started' started by lara99, Apr 14, 2008.

  1. lara99 New Member

    is there a way to rename the physical .mdf and .ldf files
    Thanks!
  2. Luis Martin Moderator

    Why do you need that?.
    BTW: Yes there is a way.
  3. lara99 New Member

    I am writing an automation process which auto creates databases with the same names.
    Would be thankful if you guid me through.
    Thakns!
  4. ghemant Moderator

    Hi,
    Assuming you are using SQL Server 2005, using Alter Database statement you can achieve this. But better to be cautious on production system.
  5. lara99 New Member

    I am using sql 2k and trying to renmae the .mdf and .ldf .
    for example \filepathabc.mdf i was to rename it to \filepathxyz.mdf
    do u think this is possible.
    Thanks.
  6. Luis Martin Moderator

    No it is no possible at all.
    You have to use Alter Database for that.
  7. lara99 New Member

    I think Alter database only works when we are trying to rename the logical files not the physical files right?
  8. Luis Martin Moderator

    Ooops!!!.
    Yeap. To change physical names you can dettach and attach or backup and restore with move option.
  9. jagblue New Member

    what you can do is use sp_detach_db and then you can rename or move file.
    you can use this method in both sql 2k and sql 2005
    and then you can cerate new blanck database
    Thanks
  10. ghemant Moderator

    Hi,
    Yes, we can very much change the physical file name as well,
    ALTER DATABASE testdb
    MODIFY FILE
    (
    NAME = test,
    filename='somepath
    ewphysical_filename.mdf'
    );
    go
  11. lara99 New Member

    Server: Msg 5037, Level 16, State 1, Line 1
    MODIFY FILE failed. Do not specify physical name.
    I end up getting this error.
    Thakns!
  12. jagblue New Member

    In sql 2000 and SQL 2005 you can run following satpes
      • Detaching the database.
    1. Moving the database files to the other server or disk.
      • Attaching the database by specifying the new location of the moved file or files.
    • IN sql 2005 follow these stapes
        • Run ALTER DATABASE database_name SET OFFLINE.
          • Move the file to the new location
        • Run ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path/os_file_name'.
          • Run ALTER DATABASE database_name SET ONLINE.

Share This Page