(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 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!). Last Saturday’s post busted 6 page checksum myths, and last Sunday’s busted 5 FILESTREAM myths so I need to beat those today.
In fact, I’m going to do one myth for each letter of the alphabet as everyone else is still asleep here – it’s another multi-myth-busting extravaganza!
Myth #24: twenty-six myths around restore operations…
All of them are FALSE!
24a) it is possible to do a point-in-time restore using WITH STOPAT on a full or differential backup
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 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.
24b) it is possible to continue with a restore sequence after having to use WITH CONTINUE_AFTER_ERROR
No. If a backup is corrupt such that you must use WITH CONTINUE_AFTER_ERROR 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.
24c) it is possible to restore different parts of a database to different points-in-time
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.
24d) it is possible to restore filegroups from different databases together in a new database
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.
24e) restore removes index fragmentation (or updates statistics, etc)
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.
24f) it is possible to shrink a database during a restore
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.
24g) you can restore a database to any downlevel version of SQL Server
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).
24h) you can always restore a database to any edition of SQL Server
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 in the blog post SQL Server 2008: Does my database contain Enterprise-only features?
24i) using WITH STANDBY breaks the restore sequence
No. The WITH STANDBY 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. You can stop as many times as you like using WITH STANDBY. 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 might cause some seemingly-weird behavior – see Why could restoring a log-shipping log backup be slow?
24j) instant file initialization during a restore doesn’t work if the database wasn’t backed up on a server with instant file initialization enabled
No. 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.
24k) restore is the best way to recover from corruption
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.
24l) you can take a tail-of-the-log backup after starting a restore sequence
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.
24m) you can always do a point-in-time restore to a time covered by a log backup
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?
24n) as long as the backup completes successfully, the restore will work too
No, no, no, no. 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. 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.
24o) all SQL Server page types can be single-page restored
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? explains more.
24p) using RESTORE … WITH VERIFYONLY validates the entire backup
No. Using VERIFYONLY only validates the backup header looks like a backup header. It’s only when you take the backup using WITH CHECKSUM and do RESTORE … WITH VERIFYONLY *and* using WITH CHECKSUM that the restore does more extensive checks, including the checksum over the entire backup.
24q) it is possible to restore a backup of an encrypted database without first having restored the server certificate
No. That’s the whole point of transparent data encryption. Lose the server certificate, lose the database.
24r) a restore operation performs all REDO and UNDO operations when the restore sequence is completed
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.
24s) a compressed backup can only be restored using Enterprise Edition in SQL Server 2008
No. All editions can restore a compressed backup. New in SQL Server 2008 R2, Standard Edition can create a compressed backup as well as Enterprise Edition.
24t) the restore of a database from an earlier version of SQL Server can be made to skip the upgrade process
No. It is not possible to skip any necessary upgrade or recovery during a database restore or attach.
24u) a backup taken on a 32-bit instance cannot be restored on a 64-bit instance, and vice-versa
No. There is not difference in the database format on different CPU architectures.
24v) restoring the database is everything the application needs to continue
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.
24w) to restore a damaged file from a multi-file filegroup you must restore the entire filegroup
No. This used to be the case before SQL Server 2000, but not any more.
24x) you can restore a backup to any up-level version of SQL Server
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).
24y) a restore operation will always take the same time as the backup operation
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.
24z) you should always drop a database before restoring
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.
6 thoughts on “A SQL Server DBA myth a day: (24/30) twenty six restore myths”
Good style! I’m glad you followed each myth with the word "No".
I’m guessing that you worried about people skipping or not reading the intro that says each myth is false.
The word "No" makes it clear to even the most casual skimmers of this article.
I think database backup taken on 32 bit can be restored on 64 bit. I restored a backup of 32 bit SQLServer 2005 to 64 bit SQLServer 2005.
> New in SQL Server 2008 R2, Standard Edition can create a compressed backup as well as Enterprise Edition.
This is great news. :)
Farhan – yes, it’s a myth that you can’t – that’s the point of the post….
24f) it is possible to shrink a database during a restore
Yeah that’s something I would have liked to do a few times. Let’s hope it will be implemented in a future version.
Great list of myths you debunked. Will definitely use the copy_only option going forward.