RESTORE WITH REPLACE—What Does it Really Do?

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.

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

The SQL Herald稿源:The SQL Herald (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » RESTORE WITH REPLACE—What Does it Really Do?

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录