SQLGeeksPro

<<Previous

This article is in continuation of How To Rename SQL Server Database and here we are going show how to Rename SQL Database Logical and Physical File Names.

I am sure you have already got the idea from the previous article on what is logical and physical file name. The query we used to check the database file details is as follows: We are going to use this multiple times to verify the file details.

USE SQLGeeksPro_NewName -- Change the db Name
GO
SELECT DB_NAME() AS DatabaseName, file_id, name AS logical_name, physical_name
FROM sys.database_files
GO 

Now we are going to rename SQL Database logical and physical file name.

Query to Rename SQL Database Logical File Name:

USE master
GO
ALTER DATABASE [SQLGeeksPro_NewName] MODIFY FILE (NAME = SQLGeeksPro, NEWNAME = SQLGeeksPro_NewName_Data);
GO
ALTER DATABASE [SQLGeeksPro_NewName] MODIFY FILE (NAME = SQLGeeksPro_log, NEWNAME = SQLGeeksPro_NewName_log);
GO

Now let’s check the database file details again by executing the first query.

Example result set:

As can be seen till now we are able to rename the database name and logical file name. Next and final step is to rename the physical file name.

Things become a bit tricky here as you can’t rename physical files while the database is online. Therefore I had mentioned in the first article that you need downtime.

Steps To Rename SQL Database Physical File Name:

To rename physical file name we have to take the database offline and for that we need exclusive access to the database and hence we’ll first put the database in Single User mode.  If there are any transactions running from application, it’s recommended to let them complete first.

In the below query, first statement will rollback any open transactions and set the database in single user mode. The second statement will put the database into offline state.

Step1: Put the database in Offline State

USE master
GO
ALTER DATABASE [SQLGeeksPro_NewName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [SQLGeeksPro_NewName] SET OFFLINE

In SSMS the database status will look like below:

Step2: Rename the Physical Files

Go to the physical locations of data and log file and rename them. Below screen shot shows example for primary data file. You have to rename all the data and log files in respective physical locations.

 Step3:Update System Catalog

You have to inform SQL Server Engine that physical file name have been changed for the data and log files so that when you bring up the database it can refer the metadata and identify the new names. Otherwise it will try to look for the the old physical file names and will fail to bring the database online.

USE master
GO
ALTER DATABASE [SQLGeeksPro_NewName] MODIFY FILE (NAME ='SQLGeeksPro_NewName_Data', FILENAME ='F:\DATA\SQLGeeksPro_NewName_Data.mdf')
GO
ALTER DATABASE [SQLGeeksPro_NewName] MODIFY FILE (NAME ='SQLGeeksPro_NewName_Log', FILENAME ='F:\DATA\SQLGeeksPro_NewName_Log.ldf')
GO

The message shows that the system catalogs have been updated and new path will be used after the database is started.

Step4: Bring up the database using the below query

USE master
GO
ALTER DATABASE [SQLGeeksPro_NewName] SET ONLINE
GO
ALTER DATABASE [SQLGeeksPro_NewName] SET MULTI_USER

Final step is to verify if all files have been renamed successfully as desired/as per organization standards.

You can execute the very first query provided in this article again:

Conclusion:

As can be seen above, renaming a SQL Server database can be done easily with a few steps. Rename not only SQL Server database alone but also logical and physical file names.

Feel free to leave your thoughts below in comment sections.

Please spread the word:

https://sqlgeekspro.com/rename-sql-database-logical-physical-file-names/