Skip to main content
formatted output; fixed line-break;
Source Link
John K. N.
  • 18.3k
  • 14
  • 54
  • 115
+--------------+----------------------------+---------------------------------------------+---------------+
|  name  DatabaseName |            name   LogicalName         |                physical_namePhysicalName                 | state_descDatabaseState |
+--------------+----------------------------+---------------------------------------------+---------------+
| Sausage      | LogicalNameSausageDataFile | C:\SQL\SQL_DATA\PhysicalNameSausageData.mdf | ONLINE        |
| Sausage      | LogicalNameSausageTlogFile | C:\SQL\SQL_LOGS\PhyscialNameSausageTlog.ldf | ONLINE        |
+--------------+----------------------------+---------------------------------------------+---------------+
  1. 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.

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

  3. 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:

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

  4. Instead of renaming the database, perform a backup and then a restore on the same server.

+---------+----------------------------+---------------------------------------------+------------+
|  name   |            name            |                physical_name                | state_desc |
+---------+----------------------------+---------------------------------------------+------------+
| Sausage | LogicalNameSausageDataFile | C:\SQL\SQL_DATA\PhysicalNameSausageData.mdf | ONLINE     |
| Sausage | LogicalNameSausageTlogFile | C:\SQL\SQL_LOGS\PhyscialNameSausageTlog.ldf | ONLINE     |
+---------+----------------------------+---------------------------------------------+------------+
  1. 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.

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

  3. 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:

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

  4. Instead of renaming the database, perform a backup and then a restore on the same server.

+--------------+----------------------------+---------------------------------------------+---------------+
| DatabaseName |        LogicalName         |                PhysicalName                 | DatabaseState |
+--------------+----------------------------+---------------------------------------------+---------------+
| Sausage      | LogicalNameSausageDataFile | C:\SQL\SQL_DATA\PhysicalNameSausageData.mdf | ONLINE        |
| Sausage      | LogicalNameSausageTlogFile | C:\SQL\SQL_LOGS\PhyscialNameSausageTlog.ldf | ONLINE        |
+--------------+----------------------------+---------------------------------------------+---------------+
  1. 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.

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

  3. 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:

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

  4. Instead of renaming the database, perform a backup and then a restore on the same server.

Source Link
John K. N.
  • 18.3k
  • 14
  • 54
  • 115

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:

+---------+----------------------------+---------------------------------------------+------------+
|  name   |            name            |                physical_name                | state_desc |
+---------+----------------------------+---------------------------------------------+------------+
| 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

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

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

  3. 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:

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

  4. Instead of renaming the database, perform a backup and then a restore on the same server.

Good luck.