locked
The easiest way to rename database files RRS feed

  • Question

  • Hi,

    I have to rename the database files of my DBs (both log and data). What would be the easiest way to do this?

    I can stop the SQLServer service.

    I am aware that I can backup the database and restore it again with different file name. I am wondering if there is any easier alternative.

    Thank you.

    Friday, January 14, 2011 11:09 PM

Answers

  • Hello,

    Read Best Practice for renaming a SQL Server Database article which discussed about renaming Logical and Physical files of a database.

    Hope that Helps!

    Thanks
    Ashish Kumar Mehta

    Please click the Mark as Answer button if a post solves your problem!

    • Proposed as answer by Ashish Kumar Mehta Saturday, January 15, 2011 10:23 AM
    • Marked as answer by Max_2010 Thursday, January 20, 2011 2:15 PM
    Saturday, January 15, 2011 10:23 AM
  • Hi ,

    Sankar is absolutely right with the logical file name change. But for changing the database physical file names I would prefer using detach and attach process rather than setting the database offline/online , the problem I see with the second approach is that when you set the database offline you may not  able to update the system catalog for this database for new file path and SET ONLINE option cannot have any other parameters to it unlike attach db.

    Follow these steps,

    1. Detach you database - which means your physical files at the disk level are released by sqlservr.exe

    2. Change the filename at the OS level

    3. Use sp_attach_db with parameters and attach your database with the new physical file name ( Metadata gets updated with newer path or file name)

    Having said that to use detach/attach option you should be careful if the databases have trustworthy and cross db ownership chaining because these properties are turned off during detach / attach process and DBAs have to manually turn it on post the attach.


    Thanks, Leks
    • Marked as answer by Max_2010 Thursday, January 20, 2011 2:16 PM
    Saturday, January 15, 2011 9:31 PM
    Answerer
  •  
     1) When you detach the database, the file ownership is changed to the person who performed the detach.  Attaching the database may require changing the permissions on the data files before they can be attached. 
     
    2) Database ownership is changed to the person who attaches the database.
     

    Hi Jeff,

    The above statement may be not true in some scenarios. When you are detaching or attaching a database, it (the ownership change) depends on which type of authentication mode you use (Windows or Mixed) and whether the Database Engine could impersonate the account of the connection performing the operations.

    For more information, you can see Detaching and Attaching a Database section in Securing Data and Log Files on Books Online.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Max_2010 Thursday, January 20, 2011 2:15 PM
    Monday, January 17, 2011 8:01 AM

All replies

  • From your description, I can't tell if you are looking to change the logical file names or the physical file names.

    If logical you could do below.

    ALTER DATABASE DBNAME MODIFY FILE (NAME = 'OLDNAME', NEWNAME = 'NEWNAME')

    If physical file names then may be below. *Untested*

    SET DB OFFLINE

    RENAME FILES AT OS level

    ALTER DB WITH NEW LOCATION

    SET DB ONLINE

    As the above is untested, please test it yourself on a test box before applying this anywhere. In other words, use it at your own risk.

     


    http://SankarReddy.com/
    Saturday, January 15, 2011 12:03 AM
  • Hello,

    Read Best Practice for renaming a SQL Server Database article which discussed about renaming Logical and Physical files of a database.

    Hope that Helps!

    Thanks
    Ashish Kumar Mehta

    Please click the Mark as Answer button if a post solves your problem!

    • Proposed as answer by Ashish Kumar Mehta Saturday, January 15, 2011 10:23 AM
    • Marked as answer by Max_2010 Thursday, January 20, 2011 2:15 PM
    Saturday, January 15, 2011 10:23 AM
  • Hi ,

    Sankar is absolutely right with the logical file name change. But for changing the database physical file names I would prefer using detach and attach process rather than setting the database offline/online , the problem I see with the second approach is that when you set the database offline you may not  able to update the system catalog for this database for new file path and SET ONLINE option cannot have any other parameters to it unlike attach db.

    Follow these steps,

    1. Detach you database - which means your physical files at the disk level are released by sqlservr.exe

    2. Change the filename at the OS level

    3. Use sp_attach_db with parameters and attach your database with the new physical file name ( Metadata gets updated with newer path or file name)

    Having said that to use detach/attach option you should be careful if the databases have trustworthy and cross db ownership chaining because these properties are turned off during detach / attach process and DBAs have to manually turn it on post the attach.


    Thanks, Leks
    • Marked as answer by Max_2010 Thursday, January 20, 2011 2:16 PM
    Saturday, January 15, 2011 9:31 PM
    Answerer
  •  
    I disagree, using the detach/rename/attach method has a couple of issues:
     
    1) When you detach the database, the file ownership is changed to the person who performed the detach.  Attaching the database may require changing the permissions on the data files before they can be attached. 
     
    2) Database ownership is changed to the person who attaches the database.
     
    I prefer using OFFLINE/ONLINE which will avoid the above issues.  The method you use is:
     
    ALTER DATABASE {database} MODIFY FILE (Name = {logical name}, Filename = {new filename});
    GO
     
    ALTER DATABASE {database} SET OFFLINE WITH ROLLBACK IMMEDIATE;
    GO
     
    /* rename/move files here */
     
    ALTER DATABASE {database} SET ONLINE;
    GO
     
    When you change the filename, it won’t take effect until you restart SQL Server or take the database offline and bring it back online.
     
    Jeff
     
    "Lekss" wrote in message news:79d1f756-98f9-4d92-8128-9c18342a283d...

    Hi ,

    Sankar is absolutely right with the logical file name change. But for changing the database physical file names I would prefer using detach and attach process rather than setting the database offline/online , the problem I see with the second approach is that when you set the database offline you may not  able to update the system catalog for this database for new file path and SET ONLINE option cannot have any other parameters to it unlike attach db.

    Follow these steps,

    1. Detach you database - which means your physical files at the disk level are released by sqlservr.exe

    2. Change the filename at the OS level

    3. Use sp_attach_db with parameters and attach your database with the new physical file name ( Metadata gets updated with newer path or file name)

    Having said that to use detach/attach option you should be careful if the databases have trustworthy and cross db ownership chaining because these properties are turned off during detach / attach process and DBAs have to manually turn it on post the attach.


    Thanks, Leks
    Saturday, January 15, 2011 10:00 PM
  •  
     1) When you detach the database, the file ownership is changed to the person who performed the detach.  Attaching the database may require changing the permissions on the data files before they can be attached. 
     
    2) Database ownership is changed to the person who attaches the database.
     

    Hi Jeff,

    The above statement may be not true in some scenarios. When you are detaching or attaching a database, it (the ownership change) depends on which type of authentication mode you use (Windows or Mixed) and whether the Database Engine could impersonate the account of the connection performing the operations.

    For more information, you can see Detaching and Attaching a Database section in Securing Data and Log Files on Books Online.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Max_2010 Thursday, January 20, 2011 2:15 PM
    Monday, January 17, 2011 8:01 AM