"Restore With Replace" or drop/restore with Instant Initialization on?

Question

What exactly does the restore argument "With Replace" do? I'm looking at needing to restore a database back to a beginning point on a regular basis, and I've been trying to figure out if there are any disadvantages to using Restore With Replace versus Dropping/Deleting the database entirely and restoring it.

Will "With Replace" wipe the log files and reset whatever bits might be left in system databases as well? It seems that it would be much quicker, as I don't have to wait for the database to finish dropping (the database in question is around 2TB). I've already checked the TechNet article on the Restore arguments, it doesn't go into this specific question.

Answer 1

Paul Randal stated that a restore over the old DB will not require the pages that have identical data to be rewritten. This could save a lot of time, even with IFI enabled.

The "Database Created Date" will be updated if you drop the DB (Kendra Little SQL Webcast with Brent Ozar)

RLF mentioned that it skips certain safety checks.

Answer 2

A RESTORE DATABASE ... WITH REPLACE will completely overwrite the database and database log files without the need to drop them first.

On a replace the database settings are also repopulated in master. For example, if a database was in SIMPLE recovery model, but the RESTORE WITH REPLACE was in FULL recovery model, the restored database would still be in FULL recovery mode.

Additional note: RECOVERY can be considered risky, since it skips over certain safety checks. See REPLACE Option Impact at http://technet.microsoft.com/en-us/library/ms186858.aspx

But if you know your restore is replacing the correct files then it works like a charm.