SQL Server Performance

Renaming data files in 2005

Discussion in 'SQL Server 2005 General DBA Questions' started by jinugeorge05, Oct 17, 2007.

  1. jinugeorge05 New Member

    I am planning to rename the data files using the steps outlined below...Could you let me know if that is the standard way of doing it on 2005?
    Database name- testdatabase
    Datafiles- testdatabase_1.mdf , testdatabase_2.ndf , testdatabase_3.ldf
    I need to rename the physical files to testdatabase_data.mdf , testdatabase_sec.ndf , testdatabase_log.ldf
    Steps:
    1) Take the database offline
    2) Rename the actual physical files from testdatabase_1.mdf , testdatabase_2.ndf , testdatabase_3.ldf to testdatabase_data.mdf , testdatabase_sec.ndf , testdatabase_log.ldf
    3) Run the following commands to update the file names in sysdatabases
    Alter database testdatabasr
    Modify file (name=Testdatabase_Data,filename= 'G:MSSQLData' testdatabase_data.mdf ')
    Alter database FlatironsDSL
    Modify file (name=SECONDARY,filename= 'G:MSSQLData'testdatabase_sec.ndf' )
    Alter database FlatironsDSL
    Modify file (name=Testdatabase_log,filename= 'G:MSSQLData'testdatabase_log.ldf)

    4) execute sp_helpdb <dbname> to ensure tha new file names are updated
    5) Bring the database online
    This works on my test server.But i thought of confirming this before I do this on the production box.
    I need to do this to comply to naming conventions.
    Let me know if there is an alternate way to do this.
    jinugeorge05, Oct 17, 2007
    #2

  2. satya Moderator

    As you are prepared for a downtime on the database, best way is to detach the databses rename the physical name of the files and reattach again.
    Detach - SP_DETACH_DB
    Attach - SP_ATTACH_DB
    For more information and code examples for these SPs refer to Books online (updated0.
    satya, Oct 17, 2007
    #3

  3. jinugeorge05 New Member

    Hi Satya,
    I tried that.
    - Detach the DB
    - Renamed the physical files
    - Attach it back
    This fails because the sysdatabases still holds the old file names.The database fails to start up because the old files are no longer there (i renamed it)
    Thanks!!
    jinugeorge05, Oct 17, 2007
    #4

  4. satya Moderator

    satya, Oct 17, 2007
    #5

  5. jinugeorge05 New Member

    Hi Sathya ,
    Reattaching the database was failing when I tried it from the GUI.But when I tried it using the commands, it worked...Thinking about it....what goes on at the back is the same as the initial steps I outlined in the initial post...
    Does renaming the data file cause the database to go corrupt at any stage :)
    jinugeorge05, Oct 17, 2007
    #6

  6. satya Moderator

    Thats why I have referred to do with TSQL [:)] and in addition to that for reference on your first post linked the relevant KBA.
    As far as your other question is concerned, not at all. If you have attached the DB successful and see no warnings or errors on the log... good to go.
    satya, Oct 17, 2007
    #7

  7. techbabu303 New Member

    Nice suggestion Satya, kind of forgot about attach and deattach...porbably thats why you are GURU [:)]
    Regards
    Sat
    techbabu303, Oct 18, 2007
    #8

  8. satya Moderator

    Thanks, hey I'm still learning new things in SQL... so nothing stops you from learning [:)].
    satya, Oct 19, 2007
    #9

  9. techbabu303 New Member

    I guess thats the only way to do it.
    Regards
    Sat
    techbabu303, Oct 17, 2007
    #10

Share This Page