Click here to Skip to main content
15,860,943 members
Articles / Database Development / SQL Server / SQL Server 2008
Tip/Trick

Rename a Database and its MDF and LDF files in SQL Server

Rate me:
Please Sign up or sign in to vote.
5.00/5 (18 votes)
13 Mar 2012CPOL1 min read 255.7K   11   9
This tip shows how to rename a database and its MDF and LDF files in SQL Server.

It happens sometimes that we need to rename a database. It is good to know that what happens behind the scenes is different from what you may be expecting. SQL Server renames the presenting name of the DB only. The problem happens when you want to create a DB with the old name. For instance, imagine you have a DB called MyDB. You rename it to MyDB_OLD. Now you try to create a new DB called MyDB. This operation fails because the underlying files of the MyDB_OLD are yet MyDB.mdf and MyDB_log.ldf. If you take the DB offline and physically rename the files, when you try to bring it back online, it fails because the new file name does not match the one saved inside the file!

And here is the question again: how can we rename a DB and all its physical files? In order to do that, you need to follow these steps:

  1. Open Microsoft SQL Server Management Studio.
  2. Connect to the server wherein the DB you want to rename is located.
  3. Modify the following script and run it –
  4. SQL
    -- Replace all MyDBs with the name of the DB you want to change its name
    USE [MyDB];
    -- Changing Physical names and paths
    -- Replace all NewMyDB with the new name you want to set for the DB
    -- Replace 'C:\...\NewMyDB.mdf' with full path of new DB file to be used
    ALTER DATABASE MyDB MODIFY FILE (NAME = ' MyDB ', FILENAME = 'C:\...\NewMyDB.mdf');
    -- Replace 'C:\...\NewMyDB_log.ldf' with full path of new DB log file to be used
    ALTER DATABASE MyDB MODIFY FILE (NAME = ' MyDB _log', FILENAME = 'C:\...\NewMyDB_log.ldf');
    -- Changing logical names
    ALTER DATABASE MyDB MODIFY FILE (NAME = MyDB, NEWNAME = NewMyDB);
    ALTER DATABASE MyDB MODIFY FILE (NAME = MyDB _log, NEWNAME = NewMyDB_log);
  5. Right click on the DB and select Tasks>Take Offline
  6. Go to the location that MDF and LDF files are located and rename them exactly as you specified in first two alter commands. If you changed the folder path, then you need to move them there.
  7. Go back to Microsoft SQL Server Management Studio and right click on the DB and select Tasks>Bring Online.
  8. Now is the time to rename you DB to the new name.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
United States United States
I got my BS in Software Engineering from Iran, worked there for 4.5 years mainly in industrial automation field. Then I moved to Australia. In Australia, I had a great chance to work at some big companies. Since 2009 I have been living in the States. I received my MS in Information Systems from Illinois State University. Currently, I am a Senior Software Development Engineer.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Howard Wisnik20-Feb-23 7:25
Howard Wisnik20-Feb-23 7:25 
GeneralMy vote of 5 Pin
Paolo Botti15-Apr-20 7:15
professionalPaolo Botti15-Apr-20 7:15 
QuestionDoes not work Pin
mark4asp17-Dec-18 3:18
mark4asp17-Dec-18 3:18 
GeneralMy vote is 5 Pin
Sergiy Bogdancev26-Jul-15 19:45
Sergiy Bogdancev26-Jul-15 19:45 
QuestionDetach / ReAttach Database Pin
Bob Clegg20-Jun-14 21:45
Bob Clegg20-Jun-14 21:45 
QuestionThere is a much easier way... Pin
cn yee22-Dec-13 5:14
cn yee22-Dec-13 5:14 
AnswerRe: There is a much easier way... Pin
si6184-Aug-15 19:39
si6184-Aug-15 19:39 
QuestionThat logical name may be named differently Pin
cn yee7-Dec-13 23:31
cn yee7-Dec-13 23:31 
QuestionInvalid usage of the option _log in the CREATE/ALTER DATABASE statement. Pin
martin_ctc9-Oct-13 21:19
martin_ctc9-Oct-13 21:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.