SQL Server Myth: 26 myths about restoring (restore)

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 restore (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 have been 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

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

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, I have a previous article on this has a more detailed explanation: blog post over on 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. I have written an article about this, please see: SQL Server 2008:does My database contain enterprise-only features?

i) with standby parameter will destroy 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 cause some strange problems, please see: Why could restoring a log-shipping log backup is 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 details, see: SQL Server myths about 30th-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 more data will 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 additional full backups that destroy the 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). Details you can see me this article: Search Engine q&a #22: Can all page types is 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. 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.