SSMS doesn't rename files automatically when restoring a specific database. Why?

Question

I have several databases that when I restore using SSMS and choose a different destination name, the file names are also automatically renamed.

Example:

enter image description here

enter image description here

---

The problem I'm having though is with one specific database that when I choose a different destination name, the files do not auto-rename and I have to manually rename them every time. Is there a setting somewhere that's changing this behaviour?

Example:

enter image description here

enter image description here

• Eric Prévost: What's your SQL Server instance system collation?
• Ryan Mortier: Both databases are SQL_Latin1_General_CP1_CI_AS and the server is SQL_Latin1_General_CP1_CI_AS
• Eric Prévost: Which databases? I'm looking for master database's collation.
• Tibor Karaszi: The original filename seem to be "Prod...", but the restore creates a database named "prod...". It is possible that SSMS in this regard act as case sensitive, which is why it doesn't do the "smart renaming" of the file names. Furthermore, it is possible that case sensitivity in this regard is determined by the system database's collation case sensitivity, but a test (or the source code for SSMS) would be required to determine that part.
• Ryan Mortier: @EricPrévost master is SQL_Latin1_General_CP1_CI_AS too.

Answer 1

Based on tests I just performed using SSMS v18.2.1, it looks like this SSMS feature is case sensitive.

In your example, database name is production, but your files are Production.mdf and Production.ldf, so SSMS doesn't replace database name automatically in file names.

• Ryan Mortier: You're correct, once I restored the database with lowercase file names then subsequent backup and restores work correctly.

Answer 2

The SQL Server database engine never does this itself. It's "a feature" that SSMS does this sometimes when doing restores through its UI.

If SQL Server can restore the files to the exact same file path as the original database, it will restore the file in the original spot, without any changes.

If that original location doesn't exist on the restore target (or, I believe if you check the "relocate all files to folder" checkbox), then SSMS tries to dynamically construct the file path and name for the MOVE clause of the RESTORE DATABASE command, and in the process may rename your data file.

Depending on your use case and file naming conventions, this may be very helpful. Personally, I find it annoying and prefer to construct the RESTORE DATABASE T-SQL so that I can have full control/review of the location and file name.