Answering Your Questions
How to tell Microsoft SQL Server Management Studio that a DB rename must be done completely
and
Is there a way to enforce the DB rename to perform a FULL rename (everything, related to "Firm_Customer" MUST be renamed to "Firm_Customer_orig")?
No, there is no way to tell SQL Server Management Studio 18.x (SSMS) to perform a complete rename of all the "related database" items.
The reason for this is pretty straight-forward once you know your way around a SQL Server instance. Let's start with a pseudo database....
Create A Sausage Database
CREATE DATABASE [Sausage]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'LogicalNameSausageDataFile', FILENAME = N'C:\SQL\SQL_DATA\PhysicalNameSausageData.mdf' , SIZE = 32768KB , FILEGROWTH = 32768KB )
LOG ON
( NAME = N'LogicalNameSausageTlogFile', FILENAME = N'C:\SQL\SQL_LOGS\PhyscialNameSausageTlog.ldf' , SIZE = 98304KB , FILEGROWTH = 32768KB )
GO
We now have a database named Sausage and various names for the different parts that comprise a database. Let's have a look at the data stored in the SQL Server instance internally...
sys.databases & sys.master_files
The Database Management Views (dmv) sys.databases and sys.master_files contain some information about the databases on a system. I'll query the dmvs for the new database Sausage.
SELECT sdb.name AS DatabaseName,
smf.name AS LogicalName,
smf.physical_name AS PhysicalName,
smf.state_desc AS DatabaseState
FROM sys.databases AS sdb
JOIN sys.master_files AS smf
ON smf.database_id = sdb.database_id
AND sdb.name = 'Sausage';
This delivers (in my case) the following information:
+--------------+----------------------------+---------------------------------------------+---------------+
| DatabaseName | LogicalName | PhysicalName | DatabaseState |
+--------------+----------------------------+---------------------------------------------+---------------+
| Sausage | LogicalNameSausageDataFile | C:\SQL\SQL_DATA\PhysicalNameSausageData.mdf | ONLINE |
| Sausage | LogicalNameSausageTlogFile | C:\SQL\SQL_LOGS\PhyscialNameSausageTlog.ldf | ONLINE |
+--------------+----------------------------+---------------------------------------------+---------------+
As you can see for yourself, the names of the files do not have to correlate with the name of the database. So renaming the database will not automatically rename the physical names of the database files, nor will it change the logical names of the database files.
In a worst case scenario the logical files could be named something totally different to the database itself...
Hypothetical Database [Meat]
Here a very hypothetical database naming convention:
CREATE DATABASE [Meat]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'Apple', FILENAME = N'C:\SQL\SQL_DATA\MecedesBenz.mdf' , SIZE = 32768KB , FILEGROWTH = 32768KB )
LOG ON
( NAME = N'Salad', FILENAME = N'C:\SQL\SQL_LOGS\Scania.ldf' , SIZE = 98304KB , FILEGROWTH = 32768KB )
GO
Results in the following information stored in the system's dmvs:
+--------------+-------------+---------------------------------+---------------+
| DatabaseName | LogicalName | PhysicalName | DatabaseState |
+--------------+-------------+---------------------------------+---------------+
| Meat | Apple | C:\SQL\SQL_DATA\MecedesBenz.mdf | ONLINE |
| Meat | Salad | C:\SQL\SQL_LOGS\Scania.ldf | ONLINE |
+--------------+-------------+---------------------------------+---------------+
The information that make up a database can vary from not a lot, to something totally different.
How would SQL Server Management Studio know what to rename?
That's pretty much impossible.
Possible Solutions
When restoring a database, restore to a new sub-directory, instead of just into the default location.
Advantage: You can have the same database files for different databases.
Disadvantage: You have to manually create the sub-directories, before you restore the database.
When restoring a database use the MOVE parameter to have the files restored as different names, than what is provided in the backup file.
Advantage: You can have totally different names than the database itself.
Disadvantage: It requires you to learn about the MOVE parameter.
Follow the guidelines outlined in the article Restore a Database to a New Location (SQL Server) (Microsoft | SQL Docs)
There is a section that already points out that the database files could be an issue:
- To specify the new location of the database files, select the Files page, and then click Relocate all files to folder. Provide a new location for the Data file folder and Log file folder. For more information about this grid, see Restore Database (Files Page).
Advantage: This is a step that can be done during the GUI restore in SSMS.
Disadvantage: None, that I can see.
Instead of renaming the database, perform a backup and then a restore on the same server.
Good luck.
