Is this just me, or is it a wider issue. -- Turns out it is just me.
In SQL Server 2017 with CU15 (14.0.3162.1) you can restore a database over an existing database without the REPLACE option being used. The 'Overwrite the existing database (WITH REPLACE)' option is now irreverent and is ignored.
Confirmed with SSMS 17.8.1 & 17.9.1, both via the GUI and New Query window.
The backups being restored were from SQL Server 2014 (12.0.6024.0) and local backups (SQL Server 2017) exhibit the same behaviour.
So
RESTORE DATABASE [TMP1] FROM DISK = N'F:\tmp\TMP1.bak' WITH FILE = 1, NOUNLOAD, STATS = 5Is the same as
RESTORE DATABASE [TMP1] FROM DISK = N'F:\tmp\TMP1.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5The REPLACE option overrides several important safety checks that restore normally performs.
When doing a RESTORE, SQL Server checks for certain conditions that may lead to problems. As long as none of those conditions are applicable, it will perform the backup, even if that means overwriting an existing database. If any of the safety checks fail, it will give an error message. By adding the REPLACE option, you are telling SQL Server that it's okay to overwrite the files regardless of the safety checks.
------------ EDIT: ---------------
How does SQL Server determine whether the database being restored is the same as the database being overwritten? I did not know the answer when I started writing this, so I've done some research. For this purpose SQL Server uses the FamilyGUID. Every time you create a new database it is tattooed with a FamilyGUID. When a database is backed up, the FamilyGUID is carried along. When it comes time to restore, SQL Server compares the FamilyGUID of the backup to the database being overwritten. If they are the same, then there is no problem. If they are different, you need to use REPLACE.
Perhaps in your case, the FamilyGUIDs are the same.
To find the FamilyGUID of the backup, use:
RESTORE headeronly FROM DISK = N'Q:\MyBackup.bak'
To find the FamilyGUID of the database being overwritten, you can use:
SELECT DB_NAME(database_id) as DatabaseName, database_guid, family_guid
FROM master.sys.database_recovery_status
Are they the same?