Database names can get out of sync. After you move a database from staging to production and back again, or if you’re pulling a backup from one server to put on another server, the names can get confused in all the shuffling.
Renaming the database in SQL Server Management Studio is simple enough if the cosmetic name change is all you need. Just right click on the database name, select rename from the context menu and change the name. Done.
But if you need to rename the physical files? You need a few more steps for that name change.
Versions
For this demo I’m using Visual Studio 2013 Ultimate (VS) and SQL Server Management Studio 2012 (SSMS)
Always make a backup of your database before you do these steps. Seriously.
Step 1 Open SSMS
Open SQL Server Management Studio and open a New Query window.
Step 2 Take database offline
Create and run the following query substituting {The DB} for the name of the database with the files you want to rename.
ALTER DATABASE {The DB} SET OFFLINE;
Step 3 Alter database file names
In this step you’re renaming the database reference to the physical file name. You’ll need the Logical names for the files and the full path to the files.
To get the Logical names and Paths
- Right click on the name of the database in the Object Explorer window of SSMS
- Select Properties in the Context Menu
- In the Database Properties window select “File”. It should be the second option in the list under General.
- The resulting grid will give you the Logical name and the File path.
With this information in hand you will need to create an “Alter Database” statement for each file. Here’s an example of a database that has three files. Note that your new file name needs to include the index number at the end (eg “_0”, “_1”) and the log file needs to include the “_log” as well.
ALTER DATABASE {The DB} MODIFY FILE (NAME = '{Logical Name}', FILENAME = '{Full Path}\{New Name}_0.mdf');
ALTER DATABASE {The DB} MODIFY FILE (NAME = '{Logical Name}', FILENAME = '{Full Path}\{New Name}_1.mdf');
ALTER DATABASE {The DB} MODIFY FILE (NAME = '{Logical Name}_log', FILENAME = '{Full Path}\{New Name}_2_log.ldf');
Step 4 Rename the files in the file system
Navigate to the files in the file system. Remember you already have the paths from Step 3. Rename the files to the new file names you established in Step 3.
Step 5 Bring the database back online
Run this script to bring the database back on line.
ALTER DATABASE pwCodeStage SET ONLINE;
Step 6 Change the Logical Names
You’ve changed the file names in the file system and the database properties, now you want the logical names to match. That way everybody stays sane. For this step the {Logical Name} is the original Logical Name from Step 3 and New Name from Step 3, but without the extension.
ALTER DATABASE {The DB} MODIFY FILE (NAME = {Logical name}, NEWNAME = {New Name}_0);
ALTER DATABASE {The DB} MODIFY FILE (NAME = {Logical name}, NEWNAME = {New Name}_1);
ALTER DATABASE {The DB} MODIFY FILE (NAME = {Logical name}_log, NEWNAME = {New Name}_2_log);
And that’s it. All your database files should now be in sync.