{"id":1009,"date":"2008-09-02T10:08:00","date_gmt":"2008-09-02T10:08:00","guid":{"rendered":"\/blogs\/paul\/post\/Can-the-hack-the-suspect-database-into-the-server-trick-work-for-corrupt-file-headers.aspx"},"modified":"2013-01-01T19:21:13","modified_gmt":"2013-01-02T03:21:13","slug":"can-the-hack-the-suspect-database-into-the-server-trick-work-for-corrupt-file-headers","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/can-the-hack-the-suspect-database-into-the-server-trick-work-for-corrupt-file-headers\/","title":{"rendered":"Can the hack-the-suspect-database-into-the-server trick work for corrupt file headers?"},"content":{"rendered":"

<\/p>\n

\n(Quickie post #3 while it's Kimberly's turn to lecture this morning – better pay attention now before I get into trouble)\n<\/p>\n

\nGail asked a (paraphrased) question about the trick to hacking a detached suspect database into the server again – will it work for a detached database with multiple data files in the primary filegroup where one of the secondary data files has a corrupt file header page?<\/em>\n<\/p>\n

\nWell, based on my experiences and investigations of file header corruption (see my previous post here<\/a>) my initial reaction was to say "it will attach but you won't be able to access the database". However, I'd like to try it first – so I'm trying it while writing this post. I've taken the DemoSuspect and added a file, then detached the database and corrupted the first page in the ndf file using my trusty hex editor. Trying to attach the database again gives:\n<\/p>\n

\n

\n\tEXEC sp_attach_db @dbname = N'DemoSuspect',
\n\t   @filename1 = N'c:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\DemoSuspect.mdf',
\n\t   @filename2 = N'c:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\DemoSuspect_log.ldf',
\n\t   @filename3 = N'c:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\DemoSuspect.ndf';
\n\tGO <\/font>\n\t<\/p>\n

\n\tMsg 5172, Level 16, State 15, Line 1
\n\tThe header for file 'c:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\DemoSuspect.ndf' is not a valid database file header. The PageAudit property is incorrect. <\/font>\n\t<\/p>\n<\/blockquote>\n

\nOk – just what I expected. Now I'll copy off the files, create the dummy database, shutdown the server, swap in the corrupt files, and restart the server. So what happens?\n<\/p>\n

\n

\n\tUSE DemoSuspect;
\n\tGO <\/font>\n\t<\/p>\n

\n\tMsg 945, Level 14, State 2, Line 1
\n\tDatabase 'DemoSuspect' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. <\/font>\n\t<\/p>\n

\n\tSELECT DATABASEPROPERTYEX ('DemoSuspect', 'STATUS') AS Status;
\n\tGO <\/font>\n\t<\/p>\n

\n\tStatus
\n\t——–
\n\tSUSPECT <\/font>\n\t<\/p>\n

\n\tALTER DATABASE DemoSuspect SET EMERGENCY;
\n\tGO <\/font>\n\t<\/p>\n

\n\tMsg 5172, Level 16, State 15, Line 1
\n\tThe header for file 'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\DemoSuspect.ndf' is not a valid database file header. The PageAudit property is incorrect.
\n\tMsg 5120, Level 16, State 9, Line 1
\n\tUnable to open the physical file "C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\DemoSuspect.ndf". Operating system error 0: "(null)". <\/font>\n\t<\/p>\n<\/blockquote>\n

\nWell, the database gets hacked back into the server, but the corrupt secondary file header trumps everything else – the whole database is unusable because that data file in the primary filegroup is essentially offline. This is because an offline (or inaccessible) file means the filegroup it is part of is offline (or inaccessible) – and an offline primary filegroup means the whole database is offline.\n<\/p>\n

\nIf the secondary file is in a non-primary filegroup, then that filegroup can be manually set offline and the rest of the database is available, in Enterprise Edition – partial database availability.\n<\/p>\n

\nSo, the hack method will work, but whether the database is accessible or not depends on how the database is corrupt in the first place.\n<\/p>\n

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

(Quickie post #3 while it's Kimberly's turn to lecture this morning – better pay attention now before I get into trouble) Gail asked a (paraphrased) question about the trick to hacking a detached suspect database into the server again – will it work for a detached database with multiple data files in the primary filegroup […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,30,35],"tags":[],"class_list":["post-1009","post","type-post","status-publish","format-standard","hentry","category-bad-advice","category-corruption","category-disaster-recovery"],"yoast_head":"\nCan the hack-the-suspect-database-into-the-server trick work for corrupt file headers? - Paul S. Randal<\/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.sqlskills.com\/blogs\/paul\/can-the-hack-the-suspect-database-into-the-server-trick-work-for-corrupt-file-headers\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Can the hack-the-suspect-database-into-the-server trick work for corrupt file headers? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(Quickie post #3 while it's Kimberly's turn to lecture this morning – better pay attention now before I get into trouble) Gail asked a (paraphrased) question about the trick to hacking a detached suspect database into the server again – will it work for a detached database with multiple data files in the primary filegroup […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/can-the-hack-the-suspect-database-into-the-server-trick-work-for-corrupt-file-headers\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2008-09-02T10:08:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-02T03:21:13+00:00\" \/>\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\/can-the-hack-the-suspect-database-into-the-server-trick-work-for-corrupt-file-headers\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/can-the-hack-the-suspect-database-into-the-server-trick-work-for-corrupt-file-headers\/\",\"name\":\"Can the hack-the-suspect-database-into-the-server trick work for corrupt file headers? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2008-09-02T10:08:00+00:00\",\"dateModified\":\"2013-01-02T03:21:13+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/can-the-hack-the-suspect-database-into-the-server-trick-work-for-corrupt-file-headers\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/can-the-hack-the-suspect-database-into-the-server-trick-work-for-corrupt-file-headers\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/can-the-hack-the-suspect-database-into-the-server-trick-work-for-corrupt-file-headers\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Can the hack-the-suspect-database-into-the-server trick work for corrupt file headers?\"}]},{\"@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":"Can the hack-the-suspect-database-into-the-server trick work for corrupt file headers? - Paul S. Randal","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\/can-the-hack-the-suspect-database-into-the-server-trick-work-for-corrupt-file-headers\/","og_locale":"en_US","og_type":"article","og_title":"Can the hack-the-suspect-database-into-the-server trick work for corrupt file headers? - Paul S. Randal","og_description":"(Quickie post #3 while it's Kimberly's turn to lecture this morning – better pay attention now before I get into trouble) Gail asked a (paraphrased) question about the trick to hacking a detached suspect database into the server again – will it work for a detached database with multiple data files in the primary filegroup […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/can-the-hack-the-suspect-database-into-the-server-trick-work-for-corrupt-file-headers\/","og_site_name":"Paul S. Randal","article_published_time":"2008-09-02T10:08:00+00:00","article_modified_time":"2013-01-02T03:21:13+00:00","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\/can-the-hack-the-suspect-database-into-the-server-trick-work-for-corrupt-file-headers\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/can-the-hack-the-suspect-database-into-the-server-trick-work-for-corrupt-file-headers\/","name":"Can the hack-the-suspect-database-into-the-server trick work for corrupt file headers? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2008-09-02T10:08:00+00:00","dateModified":"2013-01-02T03:21:13+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/can-the-hack-the-suspect-database-into-the-server-trick-work-for-corrupt-file-headers\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/can-the-hack-the-suspect-database-into-the-server-trick-work-for-corrupt-file-headers\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/can-the-hack-the-suspect-database-into-the-server-trick-work-for-corrupt-file-headers\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Can the hack-the-suspect-database-into-the-server trick work for corrupt file headers?"}]},{"@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\/1009","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=1009"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1009\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1009"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1009"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1009"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}