0

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

Improve this question
5
  • What's your SQL Server instance system collation?
    – Eric Prévost
    Commented Aug 22, 2022 at 13:55
  • Both databases are SQL_Latin1_General_CP1_CI_AS and the server is SQL_Latin1_General_CP1_CI_AS
    – Ryan Mortier
    Commented Aug 22, 2022 at 13:56
  • Which databases? I'm looking for master database's collation.
    – Eric Prévost
    Commented Aug 22, 2022 at 13:58
  • 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.
    – Tibor Karaszi
    Commented Aug 22, 2022 at 14:18
  • @EricPrévost master is SQL_Latin1_General_CP1_CI_AS too.
    – Ryan Mortier
    Commented Aug 22, 2022 at 14:39

2 Answers 2

Reset to default
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.

Improve this answer
1
  • You're correct, once I restored the database with lowercase file names then subsequent backup and restores work correctly.
    – Ryan Mortier
    Commented Aug 24, 2022 at 19:05
4

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.

Improve this answer

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.