locked
Rename Filename RRS feed

  • Question

  • Hello,

    I am trying to change the name and filename of a database primary filegroup:

    ALTER DATABASE [$(DatabaseName)]
    MODIFY FILE
    (
     NAME = '$(DatabaseName)',
     NEWNAME = '$(DatabaseName)_Data',
     SIZE = 40MB,
     MAXSIZE = 2GB,
     FILEGROWTH = 20MB 
    );
    
    ALTER DATABASE [$(DatabaseName)]
    MODIFY FILE
    (
    	NAME = '$(DatabaseName)_Data',
     FILENAME = '$(DefaultDataPath)$(DatabaseName)_Data.mdf'
    );
    

    The filegroup is renamed but the file in the data folder is not.

    I tried various options including spiting this in two steps but it does not work.

    How can I do this?

    Thanks,

    Miguel

    Tuesday, October 5, 2010 1:06 AM

Answers

  • http://technet.microsoft.com/en-us/library/ms174269.aspx

    http://muhammadabbas.blogspot.com/2010/10/t-sql-renaming-physical-filename-for.html

     There are several ways to make this change, however to rename the physical database files at operating system level you will have to take the database offline

    1. Use SSMS to take the database Offline (right-click on Database, select Tasks, Take Offline), change the name of the files at the OS level and then Bring it Online.

    2. You could Detach the database, rename the files and then Attach the database pointing to the renamed files to do so.

    3. You could Backup the database and then restore, changing the file location during the restore process.

    4. using T SQL

    ALTER DATABASE databaseName SET OFFLINE
    GO


    ALTER DATABASE databaseNAme MODIFY FILE (NAME =db, FILENAME = 'C:\Program
    Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.mdf')
    GO

    --if changing log file name

    ALTER DATABASE  databaseNAme MODIFY FILE (NAME = db_log, FILENAME =
    'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.ldf')
    GO

    ALTER DATABASE databaseName SET ONLINE
    GO

    • Proposed as answer by Muhammad Abbas Tuesday, October 5, 2010 5:00 AM
    • Marked as answer by Ai-hua Qiu Wednesday, October 13, 2010 7:48 AM
    Tuesday, October 5, 2010 5:00 AM

All replies

  • Please always tell us what version you are using.

    I do not see anywhere in your code where you are changing the filegroup name, but yet you say the filegroup name has changed.

    What exactly is changing? And what does 'not work' mean? Are you getting an error message?


    HTH, Kalen Delaney www.SQLServerInternals.com
    Tuesday, October 5, 2010 2:37 AM
  • http://technet.microsoft.com/en-us/library/ms174269.aspx

    http://muhammadabbas.blogspot.com/2010/10/t-sql-renaming-physical-filename-for.html

     There are several ways to make this change, however to rename the physical database files at operating system level you will have to take the database offline

    1. Use SSMS to take the database Offline (right-click on Database, select Tasks, Take Offline), change the name of the files at the OS level and then Bring it Online.

    2. You could Detach the database, rename the files and then Attach the database pointing to the renamed files to do so.

    3. You could Backup the database and then restore, changing the file location during the restore process.

    4. using T SQL

    ALTER DATABASE databaseName SET OFFLINE
    GO


    ALTER DATABASE databaseNAme MODIFY FILE (NAME =db, FILENAME = 'C:\Program
    Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.mdf')
    GO

    --if changing log file name

    ALTER DATABASE  databaseNAme MODIFY FILE (NAME = db_log, FILENAME =
    'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.ldf')
    GO

    ALTER DATABASE databaseName SET ONLINE
    GO

    • Proposed as answer by Muhammad Abbas Tuesday, October 5, 2010 5:00 AM
    • Marked as answer by Ai-hua Qiu Wednesday, October 13, 2010 7:48 AM
    Tuesday, October 5, 2010 5:00 AM