3

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.

2 Answers 2

3

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.

4

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.

1
  • good point on the db settings on a drop vs a non drop. +1. Sep 22, 2014 at 21:06

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.