{"id":18312,"date":"2017-10-12T13:38:00","date_gmt":"2017-10-12T20:38:00","guid":{"rendered":"http:\/\/joedantoni.wordpress.com\/?p=1918"},"modified":"2017-10-12T13:38:00","modified_gmt":"2017-10-12T20:38:00","slug":"restore-with-replace-what-does-it-really-do","status":"publish","type":"post","link":"https:\/\/www.dcac.com\/2017\/10\/12\/restore-with-replace-what-does-it-really-do\/","title":{"rendered":"RESTORE WITH REPLACE\u2014What Does it Really Do?"},"content":{"rendered":"

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\u2019t clear about the situation, so I went ahead and built a quick test case, using ProcMon from sysinternals<\/a>. If you aren\u2019t familar with the sysinternals tools, you should be\u2014they are a good way to get under the hood of your Windows Server to see what\u2019s going on, and if you\u2019re old like me, you probably used PSEXEC to \u201ctelnet\u201d into a Windows server to restart a service before RDP was a thing.<\/p>\n

Test Case<\/h3>\n

My test case was pretty easy\u2014I 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\u2019t want to see everything).<\/p>\n

\"image\"<\/a><\/p>\n

Next step, let\u2019s run a backup. <\/p>\n

backup database backupdemo to DISK=’C:\\temp\\backupdemo.bak’ with compression, stats=5<\/em><\/p>\n

Note: C:\\temp is not <\/strong>a good location for backup files. But it\u2019s easy to type for demos \"Smile\" <\/p>\n

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.. <\/p>\n

\"image\"<\/a><\/p>\n

So let\u2019s see what happens when we do the RESTORE WITH REPLACE. <\/p>\n

restore database backupdemo from DISK =’C:\\temp\\backupdemo.bak’ with REPLACE, STATS=5<\/em><\/p>\n

So what does sysinternals show?<\/p>\n

\"image\"<\/a><\/p>\n

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.<\/p>\n

\"\"<\/a> \"\"<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]\"\"<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[551,63,46],"tags":[],"class_list":["post-18312","post","type-post","status-publish","format-standard","hentry","category-blog","category-sql","category-syndication"],"yoast_head":"\nRESTORE WITH REPLACE\u2014What Does it Really Do? – Denny Cherry & Associates Consulting<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dcac.com\/2017\/10\/12\/restore-with-replace-what-does-it-really-do\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"RESTORE WITH REPLACE\u2014What Does it Really Do? – Denny Cherry & Associates Consulting\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dcac.com\/2017\/10\/12\/restore-with-replace-what-does-it-really-do\/\" \/>\n<meta property=\"og:site_name\" content=\"Denny Cherry & Associates Consulting\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DennyCherryAssociatesConsulting\/\" \/>\n<meta property=\"article:published_time\" content=\"2017-10-12T20:38:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/joedantoni.files.wordpress.com\/2017\/08\/image_thumb4.png?w=400&h=253\" \/>\n<meta name=\"author\" content=\"Joey D'Antoni\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dcacco\" \/>\n<meta name=\"twitter:site\" content=\"@dcacco\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joey D'Antoni\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dcac.com\/2017\/10\/12\/restore-with-replace-what-does-it-really-do\/\",\"url\":\"https:\/\/www.dcac.com\/2017\/10\/12\/restore-with-replace-what-does-it-really-do\/\",\"name\":\"RESTORE WITH REPLACE\u2014What Does it Really Do? – Denny Cherry & Associates Consulting\",\"isPartOf\":{\"@id\":\"https:\/\/www.dcac.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dcac.com\/2017\/10\/12\/restore-with-replace-what-does-it-really-do\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dcac.com\/2017\/10\/12\/restore-with-replace-what-does-it-really-do\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/joedantoni.files.wordpress.com\/2017\/08\/image_thumb4.png?w=400&h=253\",\"datePublished\":\"2017-10-12T20:38:00+00:00\",\"dateModified\":\"2017-10-12T20:38:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dcac.com\/#\/schema\/person\/ac58f2207d3f180aa3e4ee0ce6af0885\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dcac.com\/2017\/10\/12\/restore-with-replace-what-does-it-really-do\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dcac.com\/2017\/10\/12\/restore-with-replace-what-does-it-really-do\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dcac.com\/2017\/10\/12\/restore-with-replace-what-does-it-really-do\/#primaryimage\",\"url\":\"https:\/\/joedantoni.files.wordpress.com\/2017\/08\/image_thumb4.png?w=400&h=253\",\"contentUrl\":\"https:\/\/joedantoni.files.wordpress.com\/2017\/08\/image_thumb4.png?w=400&h=253\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dcac.com\/2017\/10\/12\/restore-with-replace-what-does-it-really-do\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.dcac.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"RESTORE WITH REPLACE\u2014What Does it Really Do?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dcac.com\/#website\",\"url\":\"https:\/\/www.dcac.com\/\",\"name\":\"Denny Cherry & Associates Consulting\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dcac.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dcac.com\/#\/schema\/person\/ac58f2207d3f180aa3e4ee0ce6af0885\",\"name\":\"Joey D'Antoni\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dcac.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/3fede03569772a55a091b587d76f78d4?s=96&r=x\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/3fede03569772a55a091b587d76f78d4?s=96&r=x\",\"caption\":\"Joey D'Antoni\"},\"url\":\"https:\/\/www.dcac.com\/author\/joeydcac-co\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"RESTORE WITH REPLACE\u2014What Does it Really Do? – Denny Cherry & Associates Consulting","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dcac.com\/2017\/10\/12\/restore-with-replace-what-does-it-really-do\/","og_locale":"en_US","og_type":"article","og_title":"RESTORE WITH REPLACE\u2014What Does it Really Do? – Denny Cherry & Associates Consulting","og_description":"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 […]","og_url":"https:\/\/www.dcac.com\/2017\/10\/12\/restore-with-replace-what-does-it-really-do\/","og_site_name":"Denny Cherry & Associates Consulting","article_publisher":"https:\/\/www.facebook.com\/DennyCherryAssociatesConsulting\/","article_published_time":"2017-10-12T20:38:00+00:00","og_image":[{"url":"https:\/\/joedantoni.files.wordpress.com\/2017\/08\/image_thumb4.png?w=400&h=253","type":"","width":"","height":""}],"author":"Joey D'Antoni","twitter_card":"summary_large_image","twitter_creator":"@dcacco","twitter_site":"@dcacco","twitter_misc":{"Written by":"Joey D'Antoni","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.dcac.com\/2017\/10\/12\/restore-with-replace-what-does-it-really-do\/","url":"https:\/\/www.dcac.com\/2017\/10\/12\/restore-with-replace-what-does-it-really-do\/","name":"RESTORE WITH REPLACE\u2014What Does it Really Do? – Denny Cherry & Associates Consulting","isPartOf":{"@id":"https:\/\/www.dcac.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dcac.com\/2017\/10\/12\/restore-with-replace-what-does-it-really-do\/#primaryimage"},"image":{"@id":"https:\/\/www.dcac.com\/2017\/10\/12\/restore-with-replace-what-does-it-really-do\/#primaryimage"},"thumbnailUrl":"https:\/\/joedantoni.files.wordpress.com\/2017\/08\/image_thumb4.png?w=400&h=253","datePublished":"2017-10-12T20:38:00+00:00","dateModified":"2017-10-12T20:38:00+00:00","author":{"@id":"https:\/\/www.dcac.com\/#\/schema\/person\/ac58f2207d3f180aa3e4ee0ce6af0885"},"breadcrumb":{"@id":"https:\/\/www.dcac.com\/2017\/10\/12\/restore-with-replace-what-does-it-really-do\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dcac.com\/2017\/10\/12\/restore-with-replace-what-does-it-really-do\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dcac.com\/2017\/10\/12\/restore-with-replace-what-does-it-really-do\/#primaryimage","url":"https:\/\/joedantoni.files.wordpress.com\/2017\/08\/image_thumb4.png?w=400&h=253","contentUrl":"https:\/\/joedantoni.files.wordpress.com\/2017\/08\/image_thumb4.png?w=400&h=253"},{"@type":"BreadcrumbList","@id":"https:\/\/www.dcac.com\/2017\/10\/12\/restore-with-replace-what-does-it-really-do\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dcac.com\/"},{"@type":"ListItem","position":2,"name":"RESTORE WITH REPLACE\u2014What Does it Really Do?"}]},{"@type":"WebSite","@id":"https:\/\/www.dcac.com\/#website","url":"https:\/\/www.dcac.com\/","name":"Denny Cherry & Associates Consulting","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dcac.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dcac.com\/#\/schema\/person\/ac58f2207d3f180aa3e4ee0ce6af0885","name":"Joey D'Antoni","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dcac.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/3fede03569772a55a091b587d76f78d4?s=96&r=x","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3fede03569772a55a091b587d76f78d4?s=96&r=x","caption":"Joey D'Antoni"},"url":"https:\/\/www.dcac.com\/author\/joeydcac-co\/"}]}},"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/posts\/18312","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/comments?post=18312"}],"version-history":[{"count":1,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/posts\/18312\/revisions"}],"predecessor-version":[{"id":18362,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/posts\/18312\/revisions\/18362"}],"wp:attachment":[{"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/media?parent=18312"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/categories?post=18312"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/tags?post=18312"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}