How to rename MS SQL database name and its files name: mdf and LDF

When you want to rename your existing MS SQL database name for example because you want create a new  DB with the same name then you can fall into a trap. Be careful when you use Rename option from context menu that appear when you right click on the database in Management Studio. This option does not change the database file names. To  change logical filenames for DATA and LOG files you can also use a Management Studio interface but unfortunately sometimes it does not work (to do it properly read third paragraph in this post). Of course you can try this approach so Right Click on Database in Object Explorer and Select “Properties”, then, go to “Files” Tab:  in “Database files” grid should appear two rows. Rename the names in cells in first column (first row is a DATA file and second row is for a LOG file).

To figure out if the logical files have changed go to directory where the files are stored (by default it should be in: \Program Files\Microsoft SQL Server\[name_of_your_DB_engine]\MSSQL\DATA ).

Ok so let’s do it properly. If you have renamed your database from context menu to new name by using Rename option you should rename it again to old name (it is not necessary, but when you not do it the next steps could be for you a little complex). Follow of these steps:

  1. Detach database:
    Using Management Studio, right-click on database > go to “Tasks” > “Detach”, click OK to detach a database (note: that the DB can not be used to detach it)
  2. Rename Physical files:
    Once the database is detached the physical files are unlocked and you can rename them using Windows Explorer:
    RenameFilesExplorer
  3. Attaching database with New Name:
    For it use T SQL:

    USE [master]
    CREATE DATABASE [SqlAndMe] ON
    ( FILENAME = N’C:\…\NewName.mdf’),
    ( FILENAME = N’C:\…\NewName_log.LDF’)
    FOR ATTACH

  4. Rename Logical file names
    Execute this T SQL:

    USE [NewName]
    ALTER DATABASE [NewName]
    MODIFY FILE (NAME=N’OldName’, NEWNAME=N’NewName’)
    ALTER DATABASE [NewName]
    MODIFY FILE (NAME=N’OldName_log’, NEWNAME=N’NewName_log’)
    SELECT      name, physical_name
    FROM        [NewName].sys.database_files

It should works!

Please give me any feedback if you use it!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s