Tag: Rename SQL Database

Rename SQL Database Logical and Physical File Names

by Dev on

<<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

Rename SQL Database Logical and Physical File NameNow let’s check the database file details again by executing the first query.

Example result set:

Rename SQL Database Logical and Physical File Name

 

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

Rename Logical and Physical File Name

In SSMS the database status will look like below:

Rename Logical and Physical File Name

 

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.

 

Rename Logical and Physical File Name

Rename Logical And Physical File Name

 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.

Rename Logical and Physical File Name

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

Rename Logical and Physical File name

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:

Rename Logical and Physical File Name

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.

Tagged as: , ,

{ Add a Comment }

How To Rename SQL Server Database

by Dev on

Hi There! Today we are going to discuss How To Rename SQL Server Database the best way. Renaming a SQL Server database can be a straightforward process, but it is essential to follow the right steps to ensure data integrity and avoid disruptions. Trust me most of the DBAs think it is too easy to perform and end up missing an essential step which is discussed here.

Why Rename SQL Server Database?

Before jumping into the steps, it’s important to understand why you might need to rename a database. Common reasons are as follows:

  • Rebranding: Aligning the database name with a new organizational structure or branding.
  • Clarity: Improving the descriptiveness of the database name for better understanding among team members.
  • Organization: Consolidating database names for better management in multi-database environments.
  • Version Control: Adding version numbers or dates for better tracking.

Regardless of the reason, there may come a time when you want to or need to rename a database. In this tip we’ll look at the best option.

Prerequisites to Rename SQL Server Database:

First ensure the following:

  • Backups: Always have a recent backup of your database. This provides a safety net in case something goes wrong during the renaming process.
  • Permissions: Verify that you have the necessary permissions to rename the database. Typically, you need to be a member of the db_owner role or have ALTER permission
  • Downtime: The process needs downtime and hence always secure downtime approval/inform stake holders as per your organization process.
Two Different Methods can be used to Rename SQL Server Database:
  • Using SSMS (SQL Server Management Studio)
  • Using T-SQL

First of all let’s check and note the database details by running the following query:

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

The Results will be as shown below:

Rename SQL Database

Make a note of the highlighted details in the above example.

Rename SQL Database using SSMS:
Step 1: Connect to SQL Server

Open SQL Server Management Studio (SSMS) and connect to the SQL Server instance that contains the database you want to rename.

Step 2: Right click on the database and select the Rename option

Rename SQL Database

 

Step 3: Type in the new name you want for the database

Rename SQL Database

Now if you are not able to rename it because of the error message “The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)” then refer Database could not be exclusively locked

Rename SQL Database using T-SQL:
Step1: In SSMS open a new Query Window 
Step2: Execute the below Query:
USE master
GO
ALTER DATABASE [CurrentDatabaseName] MODIFY NAME = [NewDatabaseName] -- Change the CurrentDatabaseName and NewDatabaseName as required

For Example:
Rename SQL Database

This command works for SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016, 2017, 2019 and 2022. Alternatively you can use sp_renamedb as well.

USE master
GO
EXEC sp_renamedb 'CurrentDatabaseName','NewDatabaseName'

Most of us think that in this way we have completed the database renaming. In other words you’ll be able to see the new name in SSMS. Important to realize and point often overlooked is we have not changed the logical and physical file name. To be sure execute the very first query provided above to check the file names:

Result will be as shown below:

Rename SQL Database

As can be seen in the highlighted portion in green, only the database name got changed and there is no change for logical and physical file names. This may cause confusion in future even if we keep the technical issues aside. We can discuss it in another blog post.

Therefore you need to change the logical and physical file names as well. >>Continue Reading

Tagged as: ,

{ Add a Comment }