SQL Server myth 30th talk about the 24th day 26 about restoring (Restore) Error _mssql

This series of articles has not touched on the topic of "restore", because once involved in this topic will involve a large number of misunderstandings, more than I can not pass an article to finish the point.
In fact, I would like to use the order of the alphabet for each error number, I hope you do not sleepy. Below starts to debunk these 26 misunderstandings.

Myth #24: 26 myths about restoration (restore)
It's all wrong.

( a) can be restored to a specific point in time by using the WITH STOPAT parameter on the basis of full and differential backups
Of course not. Although this syntax may look like it, the best practice for this syntax is to take it with you when you are restoring a log to a specific point in time. So your restore will not exceed this point of time (translator Note: For example, restore the first log backup does not contain this point in time, but you take this parameter, this log backup will be all restored Until you revert to a log backup that contains a point-in-time without worrying about reverting to it, a previous article of mine would be more helpful: debunking a couple of myths around full database backups。

after using the WITH CONTINUE_AFTER_ERROR option, you can also restore in accordance with the established restore sequence
Error. If your backup set is corrupted and you have to use this option, your restore sequence will disappear. When logs are corrupted during a log restore, you need to think twice before using this option, as this is likely to result in inconsistent data issues. In the worst case, the structure in the database is corrupted, and I don't recommend using this option.

c) You can restore a portion of a database to a specific point in time
No, each part of the database needs to be in line with the primary filegroup, otherwise it cannot be online. Except, of course, read-only filegroups.

d) You can restore different filegroups from different databases to a new database
No, the file header page of each database (the page number 0) has a GUID, unless the GUID is consistent with the GUID of the other database (which of course is not possible).

(e) Restore can remove index fragmentation (or update statistics, and so on)
No, you back up what is restored is what, my previous article on this has a more detailed explanation: blog post over in our SQL Server Magazine q&a blog。

( f) database shrinkage can be performed during the restore process
No, although everyone needs this functionality, it is useful to recover a largely empty backup set in the development environment. But just can't.

g) You can restore a database to an instance of any lower version
No, this is a common misconception. An instance of a lower version may not be understandable for some of the contents of a higher version of the database (for example, SQL Server 2005 databases cannot understand some of the contents of the SQL Server 2008 database).

h) You can restore a database to any version of SQL Server
errors, such as SQL Server 2005, a database containing table partitions can only be restored to the Enterprise edition. The database that can only be restored to the enterprise version of SQL Server 2008 includes the following features: partitioning, transparent data encryption, CDC, data compression. About here I have written an article, please see: SQL Server 2008:does My database contain enterprise-only features?。

i) The with standby parameter destroys the restore chain
No, the function of this parameter is to make the database transaction level consistent during the restore process. The with standby parameter with NORECOVERY is no different from the point of view of the restore sequence. You can stop n times in the process of restoring. This is also the mechanism for transaction log shipping. It is often asked whether the process of log recovery in a transactional secondary server can be accessed, so you should know that it is readable. At the same time, this option may also create some strange problems, please see: Why could restoring a log-shipping log backup be slow?。

if the server that backs up the database does not have the Instant file initialization option turned on, then the restored server cannot take advantage of this feature
The ability to do instant file initialization depends entirely on whether the server being restored is turned on by this feature. The backup set does not contain any information about this. For more detailed information, please see: SQL Server misunderstanding 30th about-day3-The instant file initialization feature can be opened and closed in SQL Server。

k) restore is the best way to recover from damage
No, not exactly. It depends on the type of backup you have. If the corrupted data is more, then using restore is a good idea, but if the loss of data is less and allow some data loss, or by the transaction log shipping secondary server return some logs, then downtime will be much less. The best approach is to repair the damage in as few downtime as possible within the range of acceptable data loss.

( L) Back-end logs can also be backed up after the restore starts
No, once you start restoring, you no longer allow back end logs. So when the disaster happens, the first thing is always to see if you need to back up the end log.

you can revert to any point in time within the log range of the backup
This is wrong. If the log contains those operations with only a small amount of log (such as a bulk data import operation), such operations are atomic, either fully restored or not restored. This is because such operations are modified for the zone, but the backup set does not record when the extents were modified. You can view the amount of information that a log backup contains by using the following script: New script:how Much data would the next log backup include?。

N) You can use this backup set to restore as long as the backup is successful
No,no,no. A backup set is just a file stored in the IO subsystem, just like a database file. It also has the possibility of damage. You need to check regularly if the backup is damaged, or you will not be able to withstand the surprises when the disaster occurs. Please see: importance of validating backups。 Another caveat is to avoid the extra full backup damage recovery sequence, and this example may give you a little warning: backup with Copy_only-how to avoid breaking the backup chain。

o) All SQL Server page types can be restored through a single page restore
No, some pages that assign bitmaps (such as gam,sgma,fps pages, etc.) cannot be restored by a single page (such pages can be repaired automatically through a mirror of SQL Server 2008). For details you can read this article: Search Engine q&a #22: Can All page types be single-page restored?。

p) RESTORE ... The WITH VERIFYONLY option validates the entire backup set
No, this option simply checks to see if the backup header is correct. The checksum of a full backup set can only be checked with the with checksum.

Q You can restore a database that is encrypted with transparent data without restoring the certificate
No, the most important thing about transparent data encryption is to remember that the loss of a certificate means that the entire database is gone.

R) When the restore process is complete, the restore is redo and undo
After each restore operation, the redo operation is performed, and undo is only done after the entire restore process has been completed.

( s) compressed backup sets can only be restored to SQL Server 2008 Enterprise Edition
No, all versions can restore a compressed backup. Starting with SQL Server 2008 R2, the Standard Edition can also compress backups.

( t) restores a lower version of the database to an instance of a higher version to skip the upgrade process
It is not allowed to skip the necessary upgrade and recovery procedures during data restore and attach.

u) The database backed up under 32-bit instances cannot be restored to a 64-bit instance. and vice versa
Error, the internal format of the database has nothing to do with the CPU architecture.

v) As long as the data restore, you can guarantee the normal execution of the program
No, just as mirroring failover and transaction log shipping in high availability are transferred to a secondary server, there are a number of additional steps that need to be made to ensure that the program executes properly. Includes the secondary database and the correct login name, and so on.

To restore a damaged file from more than one filegroup, you must restore all of the related filegroups
No, that's true in SQL Server 2000, but the version after SQL Server 2005 is completely out of the box.

You can restore the database to any current version of the instance
No, the database can only be restored to one or two newer versions. (such as databases under SQL Server 7.0 cannot be restored to SQL Server 2008).

y) recovery time and restore time are the same
No, many factors affect the time of the restore, such as whether there are long transactions that need to be rolled back, or whether the instant file initialization feature is turned on.

z) You need to drop the restored database before restoring the database
No, if you drop the restored database before restoring the database, the restore process requires instant file initialization first, and you'd better keep a copy of the restored database to minimize the loss if the restore fails.