0

I have an older copy of a database. I retrieved it from customer system by taking a backup and restore it on my local PC. I would like to have a look at a more recent copy of that same database. Obviously I cannot do that, working on the same database, because my older copy would be overwritten, so I have first renamed my database from "Firm_Customer" to "Firm_Customer_orig".

Now I would like to restore the more recent copy of the database, but this failed as explained in following error message:

System.Data.SqlClient.SqlError: The file 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS01\MSSQL\DATA\Firm_Customer.mdf' cannot be overwritten. It is being used by database 'Firm_Customer_orig'. (Microsoft.SqlServer.SmoExtended)

I was expecting the DB rename from "Firm_Customer" to "Firm_Customer_orig" also to cause the renaming of the file 'C:\Program Files\Microsoft SQL Server...\Firm_Customer.mdf', but apparently the DB rename is just "partial".

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")?

Oh, before I forget: I'm working with Microsoft SQL Server Management Studio, version "v18.12".

Thanks in advance

1

4 Answers 4

5

This is how it is designed by Microsoft, if you rename database only database name changes - neither logical nor physical name changes. This is described in the documentation Rename a Database - Limitations and restrictions (emphasis added):

  • The database name cannot be changed while other users are accessing the database.
  • Renaming a database does not change the physical name of the database files on disk, or the logical names of the files. For more information, see Database Files and Filegroups.

For changing physical/logical path or file name, you need to bring database offline and then change the location and details, perform alter database and bring it back online.

The same is described step-by-step in Renaming Physical Database File Names for a SQL Server Database by Manvendra Singh.

2
  • 1
    Although I can imagine the mentioned article being very interesting, the effort to be spent is tremendous, and all that just for a simple rename.
    – Dominique
    Jul 18, 2022 at 9:55
  • @Dominique It's not really a tremendous amount of work. If you don't like this answer, I've provided an alternative. Mine is not necessarily a better than this answer, just a different way of thinking about the problem. Jul 18, 2022 at 11:11
5

It sounds like the names of each database are not largely important, as you've mentioned they're copies you've restored to your local PC. If that's the case, a much easier approach may be to rename the incoming database at restore time.

  1. Specify a new name for the database in the RESTORE DATABASE command. For example: RESTORE DATABASE [Database_new] FROM ....

  2. Use the WITH MOVE option in the same restore command to add _new to all file names. For example: WITH MOVE 'DataFile1' TO D:\data\DataFile1_new.mdf. You'd have to perform this on each file.

If you're not sure of all the files to be renamed, you can use the RESTORE FILELISTONLY command.

RESTORE FILELISTONLY FROM DISK = 'D:\Backups\yourbackup.bak'
2
  • Good Point - actually I was thinking to cover this aspect however I stopped myself considering line "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")?" Jul 18, 2022 at 11:14
  • 1
    @Learning_DBAdmin fair point. In all respect, you gave him the correct answer for the question asked. Jul 18, 2022 at 11:37
0

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

  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.

-1

Database data file and log file are locked as long as database is online and can't be changed just as many other files opened by an application.

If you're using SSMS when restoring, load the backup device and click Files and tick Relocate all files... and change filename under Restore As.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.