RESTORE WITH REPLACE—What Does it Really Do?

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).

image

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 Smile 

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..

image

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?

image

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.

Share

One Response

  1. 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.

Leave a Reply to Robert L DavisCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?