RESTORE WITH REPLACE—What Does it Really Do?
October 12, 2017 Leave a comment
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.
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:tempbackupdemo.bak’ with compression, stats=5
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:tempbackupdemo.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.