SQL Server - WITH REPLACE

What is the difference when using or not using "With Replace" when restoring database. I know that With Replace, I would get identical database as backup file even log file would be replaced/overwritten and current content / transactions would be broken.

But, what is actual difference; I read somewhere that some configuration for database like recovery model is stored in Master database; With Replace - it can be overwritten! What else? I cannot find much on what is the use of With Replace? Why it is used a lot, most of the developer restore databases using "With Replace".

I want to clear my concept of restore database.

Thanks

Using WITH REPLACE allows you to overwrite the DB without backing up the tail log, which means you can lose commited work.

Additionally, if you use WITH REPLACE you can, and will, overwrite whatever database you are restoring on top of. This means you could restore a database over another database that is used for something completely different, which can be quite dangerous if you aren't careful.

From the docs:


REPLACE should be used rarely and only after careful consideration.
Restore normally prevents accidentally overwriting a database with a
different database. If the database specified in a RESTORE statement
already exists on the current server and the specified database family
GUID differs from the database family GUID recorded in the backup set,
the database is not restored. This is an important safeguard.

For example, you backup Server1.DB1 and then restore it to Server2.DB2 but DB2 in this case is NOT an old restore from DB1. It's actually a completely different database used for something completely different. WITH REPLACE would replace DB2 with DB1 despite them not being the "same" database.

StackExchange Database Administrators Q#216112, answer score: 2