Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

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.

share|improve this answer
add comment

RETORE WITH REPLACE and DROP - RESTORE are pretty much the same operations. In both cases the same amount of data is written. The old file chains are released to the operating system in both cases.

The only real difference I can think of is the set of permissions required when executing a drop followed by a create (you need create permission to restore a dropped database) versus executing just a restore.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.