I recently read something that said using the RESTORE WITH REPLACE command could be faster than dropping a database and then performing a RESTORE, because the shell of the file could be used and therefore skip file initialization. I did not think that was the case, but books online wasn’t clear about the situation, so I went ahead and built a quick test case, using ProcMon from sysinternals. If you aren’t familar with the sysinternals tools, you should be—they are a good way to get under the hood of your Windows Server to see what’s going on, and if you’re old like me, you probably used PSEXEC to “telnet” into a Windows server to restart a service before RDP was a thing.
Test Case
My test case was pretty easy—I was simply going to execute a backup and restore of a new database and observe what happens with procmon. Our first step is to add a procmon filter for sqlservr.exe. This means you will only see actions from the SQL Server process. (Windows is really, really busy, and you don’t want to see everything).
Next step, let’s run a backup.
backup database backupdemo to DISK=’C:\temp\backupdemo.bak’ with compression, stats=5
Note: C:\temp is not a good location for backup files. But it’s easy to type for demos 
So during my backup, I can see SQL Server creating and writing to the backup file, and reading and writing from the log file and data file..
So let’s see what happens when we do the RESTORE WITH REPLACE.
restore database backupdemo from DISK =’C:\temp\backupdemo.bak’ with REPLACE, STATS=5
So what does sysinternals show?
We see a file getting created, and eventually written to, we later see the same behavior for the log file. So just to be clear SQL Server is overwriting your file when you do a RESTORE WITH REPLACE, it is not actually writing pages into your existing shell of a file.
One Response
The first step of a restore operation is to create the files. Restore with replace allows it to skip the file creation and restore over top of the existing files. As you know, Instant File Initialization does not work for log files (also some people may not have IFI enabled). This is where the savings come in.
To prove it, create a database with a large log file and time the restore with and without replace.
I learned this tidbit from Paul Randal.