{"id":679,"date":"2010-06-18T12:11:00","date_gmt":"2010-06-18T12:11:00","guid":{"rendered":"\/blogs\/paul\/post\/Disaster-recovery-101-hack-attach-a-damaged-database.aspx"},"modified":"2017-04-13T09:54:42","modified_gmt":"2017-04-13T16:54:42","slug":"disaster-recovery-101-hack-attach-a-damaged-database","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-hack-attach-a-damaged-database\/","title":{"rendered":"Disaster recovery 101: hack-attach a damaged database"},"content":{"rendered":"<p>(Check out my online training courses:\u00a0<a href=\"http:\/\/www.pluralsight.com\/courses\/sqlserver-database-corruption\" target=\"_blank\">SQL Server: Detecting and Correcting Database Corruption<\/a>\u00a0and\u00a0<a href=\"http:\/\/www.pluralsight.com\/courses\/sqlserver-advanced-corruption-recovery-techniques\" target=\"_blank\">SQL Server: Advanced Corruption Recovery Techniques<\/a>. We can also\u00a0<a href=\"https:\/\/www.sqlskills.com\/services\/sql-server-disaster-recovery\/\" target=\"_blank\">help you<\/a>\u00a0with disaster recovery.)<\/p>\n<p>As a follow-up to my previous post <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-backing-up-the-tail-of-the-log\/\">Disaster recovery 101: backing up the tail of the log<\/a>, I want to describe how to re-attach a\u00a0damaged database if someone&#8217;s detached it accidentally while performing disaster recovery. A couple of years ago I blogged a detailed post on <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/creating-detaching-re-attaching-and-fixing-a-suspect-database\/\">Creating, detaching, re-attaching, and fixing a suspect database<\/a>\u00a0but now I&#8217;d like to do a short, concise post for reference.<\/p>\n<p>There are many reasons why you may have a damaged database that you&#8217;re trying to attach to SQL Server:<\/p>\n<ul>\n<li>It may be a <span style=\"font-family: 'courier new', courier;\">SUSPECT<\/span> database that someone is erroneously trying to fix by detaching+attaching<\/li>\n<li>It may be a damaged database from a server that is inaccessible that needs to be attached for disaster recovery<\/li>\n<li>It may be a database you&#8217;ve been sent\/given that was not cleanly shut down (i.e. there were active transactions) and you were not given the transaction log file<\/li>\n<\/ul>\n<p>In any of these scenarios,\u00a0\u00a0a damaged database cannot be attached to SQL Server using the normal methods: <span style=\"font-family: 'courier new', courier;\">sp_attach_db<\/span>, <span style=\"font-family: 'courier new', courier;\">CREATE DATABASE &#8230; FOR ATTACH<\/span>, or <span style=\"font-family: 'courier new', courier;\">CREATE DATABASE &#8230; FOR ATTACH_REBUILD_LOG<\/span> (the latter two being in SQL Server 2005 onwards). Either the data file damage will prevent the attach, or the log file damage will prevent crash recovery from being started or completed.<\/p>\n<p>To attach the database you need to fool SQL Server into thinking it&#8217;s already attached. The set of steps to go through are as follows:<\/p>\n<ol>\n<li><strong>This only works if the server instance you&#8217;re using is the same version as the database you&#8217;re trying to attach<\/strong><\/li>\n<li>Create a dummy database, with the same name, the same number of data and log files, with the <strong>exact<\/strong> same file names <strong>(extremely important!)<\/strong> and same file IDs (make sure you have instant file initialization enabled so the file creations don&#8217;t take ages &#8211; see <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-330-instant-file-initialization-can-be-controlled-from-within-sql-server\/\">this blog post<\/a>). This can be tricky to do if you had added or removed files to the database before it was corrupted, but <strong>you need to make sure the file IDs are exactly the same<\/strong>.<\/li>\n<li>Set the dummy database offline (<span style=\"font-family: 'courier new', courier;\">ALTER DATABASE RealDatabaseName SET OFFLINE<\/span>) or shut the server down<\/li>\n<li>Delete <span style=\"text-decoration: underline;\"><strong>all<\/strong><\/span> the data and log files from the dummy database<\/li>\n<li>Drop in as many of the data and log files from the\u00a0damaged database as possible<\/li>\n<li>Set the dummy database online (<span style=\"font-family: 'courier new', courier;\">ALTER DATABASE RealDatabaseName SET ONLINE<\/span>) or start the server if you shut it down for step 2<\/li>\n<\/ol>\n<p>At this point SQL Server will be fooled into thinking the database is attached (as it thinks it&#8217;s just restarting the dummy one you created) and you&#8217;ll be able to take further measures to recover the database.<\/p>\n<p>Further measures may include:<\/p>\n<ul>\n<li>Taking a tail-of-the-log backup to use as part of a restore sequence (see <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-backing-up-the-tail-of-the-log\/\">this blog post<\/a>)<\/li>\n<li>Use <span style=\"font-family: 'courier new', courier;\">EMERGENCY<\/span> mode to extract data into a new database<\/li>\n<li>Using <span style=\"font-family: 'courier new', courier;\">EMERGENCY<\/span> mode repair to repair a damaged transaction log (see <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort\/\">this blog post<\/a>)<\/li>\n<\/ul>\n<p><strong>Note:<\/strong> it&#8217;s entirely possible that your database may be so damaged that not even <span style=\"font-family: 'courier new', courier;\">EMERGENCY<\/span> mode repair can fix it. If you cannot even get the database into <span style=\"font-family: 'courier new', courier;\">EMERGENCY<\/span> mode (e.g. if the database boot page, or the primary file&#8217;s file header page is damaged) then there is no way for SQL Server to access the database. At this point the only option is to restore from backups. If you don&#8217;t have backups, game over.<\/p>\n<p>Hope this helps!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>(Check out my online training courses:\u00a0SQL Server: Detecting and Correcting Database Corruption\u00a0and\u00a0SQL Server: Advanced Corruption Recovery Techniques. We can also\u00a0help you\u00a0with disaster recovery.) As a follow-up to my previous post Disaster recovery 101: backing up the tail of the log, I want to describe how to re-attach a\u00a0damaged database if someone&#8217;s detached it accidentally while [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[35,100],"tags":[],"class_list":["post-679","post","type-post","status-publish","format-standard","hentry","category-disaster-recovery","category-undocumented-commands"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Disaster recovery 101: hack-attach a damaged database - 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\/disaster-recovery-101-hack-attach-a-damaged-database\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Disaster recovery 101: hack-attach a damaged database - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(Check out my online training courses:\u00a0SQL Server: Detecting and Correcting Database Corruption\u00a0and\u00a0SQL Server: Advanced Corruption Recovery Techniques. We can also\u00a0help you\u00a0with disaster recovery.) As a follow-up to my previous post Disaster recovery 101: backing up the tail of the log, I want to describe how to re-attach a\u00a0damaged database if someone&#8217;s detached it accidentally while [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-hack-attach-a-damaged-database\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2010-06-18T12:11:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:54:42+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\/disaster-recovery-101-hack-attach-a-damaged-database\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-hack-attach-a-damaged-database\/\",\"name\":\"Disaster recovery 101: hack-attach a damaged database - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2010-06-18T12:11:00+00:00\",\"dateModified\":\"2017-04-13T16:54:42+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-hack-attach-a-damaged-database\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-hack-attach-a-damaged-database\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-hack-attach-a-damaged-database\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Disaster recovery 101: hack-attach a damaged database\"}]},{\"@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":"Disaster recovery 101: hack-attach a damaged database - 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\/disaster-recovery-101-hack-attach-a-damaged-database\/","og_locale":"en_US","og_type":"article","og_title":"Disaster recovery 101: hack-attach a damaged database - Paul S. Randal","og_description":"(Check out my online training courses:\u00a0SQL Server: Detecting and Correcting Database Corruption\u00a0and\u00a0SQL Server: Advanced Corruption Recovery Techniques. We can also\u00a0help you\u00a0with disaster recovery.) As a follow-up to my previous post Disaster recovery 101: backing up the tail of the log, I want to describe how to re-attach a\u00a0damaged database if someone&#8217;s detached it accidentally while [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-hack-attach-a-damaged-database\/","og_site_name":"Paul S. Randal","article_published_time":"2010-06-18T12:11:00+00:00","article_modified_time":"2017-04-13T16:54:42+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\/disaster-recovery-101-hack-attach-a-damaged-database\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-hack-attach-a-damaged-database\/","name":"Disaster recovery 101: hack-attach a damaged database - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2010-06-18T12:11:00+00:00","dateModified":"2017-04-13T16:54:42+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-hack-attach-a-damaged-database\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-hack-attach-a-damaged-database\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-hack-attach-a-damaged-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Disaster recovery 101: hack-attach a damaged database"}]},{"@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\/679","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=679"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/679\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=679"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=679"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=679"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}