{"id":4986,"date":"2019-04-30T10:38:30","date_gmt":"2019-04-30T17:38:30","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4986"},"modified":"2019-04-30T10:40:19","modified_gmt":"2019-04-30T17:40:19","slug":"the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk\/","title":{"rendered":"The Curious Case of&#8230; trying to find an MDF file in a RAW disk"},"content":{"rendered":"<p style=\"text-align: justify;\"><em>(The Curious Case of\u2026<\/em>\u00a0used to be part of our bi-weekly\u00a0<a href=\"https:\/\/www.sqlskills.com\/join-the-sqlskills-insider-community\/\" target=\"_blank\" rel=\"noopener noreferrer\">newsletter<\/a>\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)<\/p>\n<p style=\"text-align: justify;\">Continuing on from my theme last time of using a hex editor to find information, this morning I had an email from someone who was trying to find an MDF file in a RAW disk without a file system. Their client had suffered a catastrophic I\/O subsystem failure and had ended up with disks that could only be accessed using the RAW file system, and the only valid backups were from 2018.<\/p>\n<p style=\"text-align: justify;\">I explained that I was teaching so unable to consult, but as usual with nasty corruption cases, I&#8217;d be happy to swap a few emails to see if I could provide some help anyway.<\/p>\n<p style=\"text-align: justify;\">The first thing is to find the start of the file. My thoughts on this were:<\/p>\n<ul>\n<li style=\"text-align: justify;\">Look at the boot page from the old database (<em>dbcc page (name, 1, 9, 3)<\/em>), and find the offset of the string with the name of the database from the start of the boot page.<\/li>\n<li style=\"text-align: justify;\"><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2019\/04\/bootpage.jpg\"><img fetchpriority=\"high\" decoding=\"async\" class=\"alignnone size-full wp-image-4987\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2019\/04\/bootpage.jpg\" alt=\"\" width=\"1007\" height=\"228\" srcset=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2019\/04\/bootpage.jpg 1007w, https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2019\/04\/bootpage-300x68.jpg 300w, https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2019\/04\/bootpage-768x174.jpg 768w\" sizes=\"(max-width: 1007px) 100vw, 1007px\" \/><\/a><\/li>\n<li style=\"text-align: justify;\">Above is an screenshot of part of a <em>DBCC PAGE<\/em> dump of the boot page for a database called <em>salesdb<\/em>. You can see that the name of the database starts on the third line with &#8216;7300&#8217;, where the cursor is. This is the byte reversed Unicode for lowercase s. So the name starts at offset 0x34 into the boot page record, which itself starts at offset 0x60 into the page. So the name string starts at offset 0x94, or 148, bytes from the start of the boot page.<\/li>\n<li style=\"text-align: justify;\">Search from the start of the raw disk for the first occurrence of the pattern of bytes with the name of the database, then go back\u00a0148 bytes and look to see if you&#8217;ve found the boot page by checking the page ID (the\u00a06 bytes starting at offset 32 from the start of the page; 4 byte page number, byte reversed; 2 byte file number, byte reversed). Now go back 73728 bytes in the disk and hopefully that should be the file header page, page zero.<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Then you can continue looking for pages from there, searching for the first two bytes being &#8216;0100&#8217; and then a valid page ID as described above.<\/p>\n<p style=\"text-align: justify;\">You could even build some automation to look for specific allocation unit IDs in the page header\u00a0<em>m_objId<\/em> field (4 bytes, byte reversed at offset 24 in a page) and <em>m_indexId<\/em> field (2 bytes, byte reversed at offset 6 in a page), using the following code (from a disaster recovery case I worked on a few years ago):<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\n-- Plug in the allocation unit ID you're interested in\r\nDECLARE @alloc BIGINT = requiredallocunitID;\r\nDECLARE @index BIGINT;\r\n\r\nSELECT @index =\r\n    CONVERT (BIGINT,\r\n    CONVERT (FLOAT, @alloc)\r\n        * (1 \/ POWER (2.0, 48)) -- right shift, reciprocal of left shift\r\n    );\r\n\r\nSELECT\r\n    CONVERT (BIGINT,\r\n    CONVERT (FLOAT, @alloc - (@index * CONVERT (BIGINT, POWER (2.0, 48))))\r\n        * (1 \/ POWER (2.0, 16)) -- right shift, reciprocal of left shift\r\n    ) AS &#x5B;m_objId],\r\n    @index AS &#x5B;m_indexId];\r\nGO\r\n<\/pre>\n<p style=\"text-align: justify;\">You might run into false positives, but you should be able to find all the pages. I heard back from the original sender that he&#8217;d found several million pages from the MDF so far, using a similar method.<\/p>\n<p style=\"text-align: justify;\">This reminds me of another heroic data recovery case I blogged about\u00a0back in 2011 &#8211; see <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/unbelievable-tale-of-disaster-and-recovery\/\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a>\u00a0&#8211; where someone did the same thing to salvage a database whose most recent valid backup was from *SIX* years previously.<\/p>\n<p style=\"text-align: justify;\">Bottom line: up-to-date and valid backups will prevent someone having to do this to get your data back, but, with a little ingenuity and lots of time, nearly anything is possible :-)<\/p>\n","protected":false},"excerpt":{"rendered":"<p>(The Curious Case of\u2026\u00a0used to be part of our bi-weekly\u00a0newsletter\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.) Continuing on from [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[30,35,115],"tags":[],"class_list":["post-4986","post","type-post","status-publish","format-standard","hentry","category-corruption","category-disaster-recovery","category-the-curious-case-of"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>The Curious Case of... trying to find an MDF file in a RAW disk - Paul S. Randal<\/title>\n<meta name=\"description\" content=\"The Curious Case of... trying to find an MDF file in a RAW disk\" \/>\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.sqlskills.com\/blogs\/paul\/the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Curious Case of... trying to find an MDF file in a RAW disk - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"The Curious Case of... trying to find an MDF file in a RAW disk\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2019-04-30T17:38:30+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-04-30T17:40:19+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2019\/04\/bootpage.jpg\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk\/\",\"name\":\"The Curious Case of... trying to find an MDF file in a RAW disk - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2019\/04\/bootpage.jpg\",\"datePublished\":\"2019-04-30T17:38:30+00:00\",\"dateModified\":\"2019-04-30T17:40:19+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"description\":\"The Curious Case of... trying to find an MDF file in a RAW disk\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk\/#primaryimage\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2019\/04\/bootpage.jpg\",\"contentUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2019\/04\/bootpage.jpg\",\"width\":1007,\"height\":228},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Curious Case of&#8230; trying to find an MDF file in a RAW disk\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"The Curious Case of... trying to find an MDF file in a RAW disk - Paul S. Randal","description":"The Curious Case of... trying to find an MDF file in a RAW disk","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.sqlskills.com\/blogs\/paul\/the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk\/","og_locale":"en_US","og_type":"article","og_title":"The Curious Case of... trying to find an MDF file in a RAW disk - Paul S. Randal","og_description":"The Curious Case of... trying to find an MDF file in a RAW disk","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk\/","og_site_name":"Paul S. Randal","article_published_time":"2019-04-30T17:38:30+00:00","article_modified_time":"2019-04-30T17:40:19+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2019\/04\/bootpage.jpg","type":"","width":"","height":""}],"author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk\/","name":"The Curious Case of... trying to find an MDF file in a RAW disk - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2019\/04\/bootpage.jpg","datePublished":"2019-04-30T17:38:30+00:00","dateModified":"2019-04-30T17:40:19+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"description":"The Curious Case of... trying to find an MDF file in a RAW disk","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk\/#primaryimage","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2019\/04\/bootpage.jpg","contentUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2019\/04\/bootpage.jpg","width":1007,"height":228},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-trying-to-find-an-mdf-file-in-a-raw-disk\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"The Curious Case of&#8230; trying to find an MDF file in a RAW disk"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4986","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=4986"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4986\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4986"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4986"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4986"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}