{"id":703,"date":"2010-04-24T08:42:00","date_gmt":"2010-04-24T08:42:00","guid":{"rendered":"\/blogs\/paul\/post\/A-SQL-Server-DBA-myth-a-day-(2430)-twenty-six-restore-myths.aspx"},"modified":"2017-07-26T11:43:10","modified_gmt":"2017-07-26T18:43:10","slug":"a-sql-server-dba-myth-a-day-2430-twenty-six-restore-myths","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2430-twenty-six-restore-myths\/","title":{"rendered":"A SQL Server DBA myth a day: (24\/30) twenty six restore myths"},"content":{"rendered":"

(Look in the Misconceptions<\/a><\/em> blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list<\/a>, plus my online Myths and Misconceptions training course<\/a><\/em> on Pluralsight.)<\/p>\n

One area I haven’t touched on yet in the series is RESTORE – and there are a *ton* of misconceptions here (so many, in fact, that I can’t cover them all in a single post!).\u00a0Last Saturday’s post busted 6 page checksum myths<\/a>, and last Sunday’s\u00a0busted 5 FILESTREAM<\/em> myths<\/a> so I need to beat\u00a0those today.<\/p>\n

In fact, I’m going to do one myth for each letter of the alphabet as everyone else is still asleep here\u00a0–\u00a0it’s another multi-myth-busting extravaganza!<\/p>\n

Myth #24:<\/strong> twenty-six myths around restore operations…<\/em><\/p>\n

All of them are FALSE!<\/em><\/u><\/strong><\/p>\n

24a)<\/strong> it is possible to do a point-in-time restore using WITH STOPAT on a full or differential backup<\/em><\/p>\n

No. The syntax looks like it allows it, but it’s just a syntactical nicety to allow you to do the best practice of using WITH STOPAT<\/em> on every restore operation in the point-in-time restore sequence so you don’t accidentally go past it. I go into more details in the old blog post Debunking a couple of myths around full database backups<\/a><\/em>.<\/p>\n

24b)<\/strong> it is possible to continue with a restore sequence after having to use WITH CONTINUE_AFTER_ERROR<\/em><\/p>\n

No. If a backup is corrupt such that you must use WITH CONTINUE_AFTER_ERROR<\/em> to restore it, that’s restore terminates your restore sequence. If you’re restoring a bunch of transaction log backups and one is corrupt, you may want to think carefully on whether you want to force it to restore or not. Forcing a corrupt log backup to restore could mean you’ve got inconsistent data in the database, or worst case, structural corruption. I’d most likely recommend not restoring it.<\/p>\n

24c)<\/strong> it is possible to restore different parts of a database to different points-in-time<\/em><\/p>\n

No. A portion of the database cannot be brought online unless it is at the same point in time as the primary filegroup. The exception, of course, is a read-only filegroup.<\/p>\n

24d)<\/strong> it is possible to restore filegroups from different databases together in a new database<\/em><\/p>\n

No. All the files in a database have a GUID in the file header page. Unless the GUID matches that of data file ID 1 in the database, it cannot be restored as part of the same database.<\/p>\n

24e)<\/strong> restore removes index fragmentation (or updates statistics, etc) <\/em><\/p>\n

No. What you backup is what you get when you restore. I explain this a bit more in a blog post over on our SQL Server Magazine Q&A blog<\/a>.<\/p>\n

24f)<\/strong> it is possible to shrink a database during a restore <\/em><\/p>\n

No. This is an often-requested feature in SQL Server – be able to restore a very large, but mostly empty, database on a dev or QA server and have it only be the size of the data in the original database. But you can’t.<\/p>\n

24g)<\/strong> you can restore a database to any\u00a0downlevel version of SQL Server<\/em><\/p>\n

No. This is one of the most pervasive myths. SQL Server cannot understand databases from more recent versions (e.g. SQL Server 2005 cannot understand a SQL Server 2008 database). I already explained about this a bunch in A DBA myth a day: (13\/30) you cannot run DMVs when in the 80 compat mode (T-SQL Tuesday #005<\/em>)<\/a>.<\/p>\n

24h)<\/strong> you can always restore a database to any edition of SQL Server <\/em><\/p>\n

No. In SQL Server 2005, if there’s an table\/index partitioning in the database, it can only be restored on Enterprise (or Enterprise Eval or Developer) Edition. On SQL Server 2008 the list is partitioning, transparent data encryption, change data capture, and data compression. I blogged about this issue, the new DMV you can use in SQL Server 2008, and an example script\u00a0in the blog post SQL Server 2008: Does my database contain Enterprise-only features?<\/a><\/em><\/p>\n

24i)<\/strong> using\u00a0WITH STANDBY breaks the restore sequence<\/em><\/p>\n

No. The WITH STANDBY<\/em> option allows you to get a read-only transactionally-consistent look at the database in the middle of the restore sequence. As far as the restore sequence is concerned, it’s as if you used WITH NORECOVERY<\/em>. You can stop as many times as you like using WITH STANDBY<\/em>. This is what log shipping uses when you ask it to allow access to a log-shipping secondary between log backup restores. Beware though, that using WITH STANDBY<\/em> might cause some seemingly-weird behavior – see Why could restoring a log-shipping log backup be slow?<\/a><\/em><\/p>\n

24j)<\/strong> instant file initialization during a restore doesn’t work if the database wasn’t backed up on a server with instant file initialization enabled<\/em>
\nNo. Whether instant file initialization is used is entirely dependent on whether the SQL Server instance performing the restore has it enabled. There is nothing in the backup itself that controls this. You can read a bunch about instant file initialization starting in the blog post A SQL Server DBA myth a day: (3\/30) instant file initialization can be controlled from within SQL Server<\/a><\/em>.<\/p>\n

24k)<\/strong> restore is the best way to recover from corruption<\/em><\/p>\n

No, not necessarily. Depending on what backups you have, restore may be the best way to recover with zero or minimal data loss, but it may be waaaay slower than running a repair and accepting some data loss, or pulling damaged\/lost data back from a log shipping secondary. The best way to recover from corruption is the one that allows you to best meet your downtime and data-loss service level agreements.<\/p>\n

24l)<\/strong> you can take a tail-of-the-log backup after starting a restore sequence<\/em><\/p>\n

No. As soon as you start to restore over a database you lose the ability to backup the tail-of-the-log. The very first thing in a disaster recovery plan should always be to check whether a tail-of-the-log backup needs to be taken, just in case.<\/p>\n

24m)<\/strong> you can always do a point-in-time restore to a time covered by a log backup <\/em><\/p>\n

No. If the log backup contains a minimally-logged operation then you cannot stop at a point in time covered by that log backup. You can only restore it in its entirety. This is because a log backup following a minimally-logged operation must include the data extents that were changed by the operation, but there’s nothing in the backup that says *when* the extents were changed (that would be the transaction log – that wasn’t generated because the operation was minimally logged!). You can figure out how much data will be included in such a log backup using the script in New script: how much data will the next log backup include?<\/a><\/em><\/p>\n

24n)<\/strong> as long as the backup completes successfully, the restore will work too <\/em><\/p>\n

No, no, no, no.<\/em><\/u><\/strong> A backup file is just like a data file – it sits on an I\/O subsystem. And what causes most corruptions? I\/O subsystems. You must periodically check that your backups are still valid otherwise you could be in for a nasty surprise when disaster strikes. See Importance of validating backups<\/a><\/em>. The other thing to consider is that an out-of-band full or log backup could have been taken that breaks your restore sequence if it’s not available. See BACKUP WITH COPY_ONLY – how to avoid breaking the backup chain<\/a><\/em>.<\/p>\n

24o)<\/strong> all SQL Server page types can be single-page restored<\/em><\/p>\n

No. Various allocation bitmaps and critical metadata pages cannot be single-page restored (or fixed using automatic page repair with database mirroring in SQL Server 2008). My blog post Search Engine Q&A #22: Can all page types be single-page restored?<\/a><\/em> explains more.<\/p>\n

24p)<\/strong> using RESTORE … WITH VERIFYONLY validates the entire backup<\/em><\/p>\n

No.\u00a0Using VERIFYONLY<\/em> only validates the backup header looks like a backup header. It’s only when you take the backup using WITH CHECKSUM<\/em> and do RESTORE … WITH VERIFYONLY<\/em> *and* using WITH CHECKSUM<\/em> that the restore does more extensive checks, including the checksum over the entire backup.<\/p>\n

24q)<\/strong> it is possible to restore a backup of an encrypted database without first having restored the server certificate <\/em><\/p>\n

No. That’s the whole point of transparent data encryption. Lose the server certificate, lose the database.<\/p>\n

24r)<\/strong> a restore operation performs all REDO and UNDO operations when the restore sequence is completed<\/em><\/p>\n

No. The REDO portion of recovery is performed for each restore operation in the restore sequence. The UNDO portion is not done until the restore sequence is completed.<\/p>\n

24s)<\/strong> a compressed backup can only be restored using Enterprise Edition in SQL Server 2008 <\/em><\/p>\n

No. All editions can restore a compressed backup. New in SQL Server 2008 R2,\u00a0 Standard Edition can create a compressed backup as well as Enterprise Edition.<\/p>\n

24t)<\/strong> the restore of a database from an earlier version of SQL Server can be made to skip the upgrade process<\/em><\/p>\n

No. It is not possible to skip any necessary upgrade or recovery during a database restore or attach.<\/p>\n

24u)<\/strong> a backup taken on a 32-bit instance cannot be restored on a 64-bit instance, and vice-versa<\/em><\/p>\n

No. There is not difference in the database format on different CPU architectures.<\/p>\n

24v)<\/strong> restoring the database is everything the application needs to continue<\/em><\/p>\n

No. Just like with a high-availability failover to a database mirror or log shipping secondary, everything in (what I call) the application ecosystem must be there for the application to work. That may include ancillary databases, logins, jobs, stored procedures etc.<\/p>\n

24w)<\/strong> to restore a damaged file from a multi-file filegroup you must restore the entire filegroup<\/em><\/p>\n

No. This used to be the case before SQL Server 2000, but not any more.<\/p>\n

24x)<\/strong> you can restore a backup to any up-level version of SQL Server<\/em><\/p>\n

No. You can only restore a database from two versions back (i.e. you cannot directly restore a SQL Server 7.0 database to SQL Server 2008).<\/p>\n

24y)<\/strong> a restore operation will always take the same time as the backup operation<\/em><\/p>\n

No. There are a ton of things that can affect restore time – like whether there’s a long-running transaction that need to be rolled back, or whether the database files need to be created and zero-initialized. There’s no guarantee.<\/p>\n

24z)<\/strong> you should always drop a database before restoring<\/em><\/p>\n

No. If you drop the database then the database files need to be created and zero initialized (or at least the log file does if you have instant file initialization enabled). Also, you should *always* have a copy of the damaged database just in case the restore fails for some reason.<\/p>\n","protected":false},"excerpt":{"rendered":"

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) One area I haven’t touched on yet in the series […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,61],"tags":[],"class_list":["post-703","post","type-post","status-publish","format-standard","hentry","category-backuprestore","category-misconceptions"],"yoast_head":"\nA SQL Server DBA myth a day: (24\/30) twenty six restore myths - 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\/a-sql-server-dba-myth-a-day-2430-twenty-six-restore-myths\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A SQL Server DBA myth a day: (24\/30) twenty six restore myths - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) One area I haven’t touched on yet in the series […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2430-twenty-six-restore-myths\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2010-04-24T08:42:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-07-26T18:43:10+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=\"9 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\/a-sql-server-dba-myth-a-day-2430-twenty-six-restore-myths\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2430-twenty-six-restore-myths\/\",\"name\":\"A SQL Server DBA myth a day: (24\/30) twenty six restore myths - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2010-04-24T08:42:00+00:00\",\"dateModified\":\"2017-07-26T18:43:10+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2430-twenty-six-restore-myths\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2430-twenty-six-restore-myths\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2430-twenty-six-restore-myths\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A SQL Server DBA myth a day: (24\/30) twenty six restore myths\"}]},{\"@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":"A SQL Server DBA myth a day: (24\/30) twenty six restore myths - 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\/a-sql-server-dba-myth-a-day-2430-twenty-six-restore-myths\/","og_locale":"en_US","og_type":"article","og_title":"A SQL Server DBA myth a day: (24\/30) twenty six restore myths - Paul S. Randal","og_description":"(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) One area I haven’t touched on yet in the series […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2430-twenty-six-restore-myths\/","og_site_name":"Paul S. Randal","article_published_time":"2010-04-24T08:42:00+00:00","article_modified_time":"2017-07-26T18:43:10+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"9 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2430-twenty-six-restore-myths\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2430-twenty-six-restore-myths\/","name":"A SQL Server DBA myth a day: (24\/30) twenty six restore myths - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2010-04-24T08:42:00+00:00","dateModified":"2017-07-26T18:43:10+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2430-twenty-six-restore-myths\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2430-twenty-six-restore-myths\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2430-twenty-six-restore-myths\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"A SQL Server DBA myth a day: (24\/30) twenty six restore myths"}]},{"@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\/703","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=703"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/703\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=703"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=703"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=703"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}