SQL Server misunderstanding 30-Day24-26 errors related to restoration (Restore)

This series of articles I saw in the sqlskill.com's PAUL blog, many misunderstandings are more typical and representative, the original from the T-SQL Tuesday #11: Misconceptions about... EVERYTHING !!, After translation and arrangement by our team, it is published on AgileSharp. Hope to help you.

This series of articles has never touched on the "Restore" topic, because once this topic is involved, it will involve a lot of misunderstandings, it's as long as I can't finish using an article.

In fact, I want to number every misunderstanding in alphabetical order. I hope you will not be sleepy. The following 26 misunderstandings are exposed.

 

Myth #24: 26 misunderstandings about restoration

All are errors.

 

24 a) The with stopat parameter can be used to restore a full backup and differential backup to a specific time point.

Of course not. Although this syntax looks like it can, the best practice of this syntax is that you take it when you restore logs to a specific time point, in this way, your restoration will not exceed this time point (for example, the first log backup to be restored does not include this time point, however, if you include this parameter, the log Backup will be completely restored until you restore the log backup that contains the time point without worrying about overhead restoration ), my previous article will be more helpful: Debunking a couple of myths around full database backups.

 

24 B) after the WITH CONTINUE_AFTER_ERROR option is used, the system can restore data in the established order.

Error. If your backup set is damaged and you have to use this option, your Restoration sequence will no longer exist. If the log is corrupted during log restoration, you need to think twice before using this option, because this may cause data inconsistency. In the worst case, the structure of the database is damaged. I do not recommend this option.

 

24 c) restore a part of the database to a specific time point

No. Each part of the database must be consistent with the time point of the primary file group; otherwise, the database cannot be online. Of course, except for read-only file groups.

 

24 d) different file groups of different databases can be restored to a new database.

No. Each database's file header page (Note: that is, the page with page number 0) has a GUID, this GUID can be restored unless it is consistent with the GUID of another database (this is certainly not possible ).

 

24 e) restoration can remove index fragments (or update statistics, etc)

No, what are you backing up and restoring? My previous article has A more detailed explanation for this: blog post over on our SQL Server Magazine Q & A blog.

 

24 f) database contraction can be performed during restoration.

No. Although everyone needs this function, it is very useful to restore a backup set that is mostly empty in the development environment. But it cannot.

 

24 GB) can restore the database to any lower version of the Instance

No, this is a common misunderstanding. An instance of a lower version may not be able to understand part of the content of a database of a higher version (for example, a database of SQL server 2005 cannot understand some of the content of the database of SQL Server 2008 ).

 

24 h) can restore the database to any version of SQL Server

For example, in SQL Server 2005, a database with table partitions can only be restored to the Enterprise Edition. In SQL Server 2008, databases that can only be restored to the Enterprise Edition include the following features: partition, transparent data encryption, CDC, and data compression. For more information, see SQL Server 2008: Does my database contain Enterprise-only features ?.

 

24 I) The WITH STANDBY parameter will destroy the restoration chain

No. This parameter ensures Database Transaction-level consistency during restoration. From the perspective of the Restoration sequence, the With Standby parameter with norecovery is not different. You can stop multiple times during restoration. This is also the mechanism for transferring transaction logs. It is often asked whether the log recovery process can be accessed on the secondary server of transaction transfer. At this point, you should know that read-only access is allowed. At the same time, this option may also cause some strange problems. Please refer to: Why cocould restoring a log-shipping log backup be slow ?.

 

24 j) if the backup database server does not enable the instant file initialization option, the recovered server cannot use this feature.

Whether instant file initialization can be performed depends entirely on whether the restored server has enabled this feature. The backup set does not contain any information about this. For more details, see the 30-day misunderstanding of SQL Server-Day3-the instant file initialization feature can be enabled and disabled on SQL Server.

 

24 k) restoration is the best way to recover from damage

No, not exactly. This depends on your backup type. If there is a large amount of corrupted data, restoration is a good idea. However, if the loss of data is small and some data losses are allowed, or when the secondary server that transmits transaction logs returns some logs, the downtime will be much less. The best solution is to repair as few downtimes as possible within the acceptable range of data loss.

 

24 l) You can back up tail logs after restoration.

No. Once restored, tail logs are no longer allowed to be backed up. Therefore, when a disaster occurs, the first thing is always to check whether tail logs need to be backed up.

 

24 m) You can restore to any time point within the scope of the backup log

This is not correct. If a log contains operations that only contain a small amount of logs (such as batch data import operations), these operations are atomic, either completely restored or not restored. This is because these operations have modified the partition, but the backup set does not record the time when these partitions were modified. You can run the following script to view the information contained in the log backup: New script: how much data will the next log backup include ?.

 

24 n) as long as the backup is successful, the backup set can be used for restoration.

No, no, no. The backup set is only a file stored in the IO subsystem, just like a database file. It may also be damaged. You need to regularly check whether the backup is damaged. Otherwise, you may not be able to withstand the disaster. See Importance of validating backups. In addition, it should be noted that the restoration sequence is not damaged by the additional full BACKUP. This example may give you a warning: backup WITH COPY_ONLY-how to avoid breaking the BACKUP chain.

 

24 o) All SQL Server Page types can be restored through single-page recovery

No, some pages for bitmap allocation (such as GAM, SGMA, and FPS pages) you cannot restore a single page (such pages can be automatically repaired using an SQL Server 2008 image ). For more information, see my article: Search Engine Q & A #22: Can all page types be single-page restored ?.

 

24 p) RESTORE... with verifyonly option will verify the entire backup set

No, this option only checks whether the backup header is correct. Only with checksum can be used to check the complete backup set.

 

24 q) You can restore a transparent data-encrypted database without restoring the certificate.

No. Remember the most important aspect of transparent data encryption. If the certificate is lost, the entire database will be gone.

 

24 r) after the restoration process is completed, the restoration will perform Redo and Undo operations.

After each Restoration Operation, the Redo operation is actually executed. The Undo operation is performed only after the entire restoration process is completed.

 

24 s) compression backup set can only be restored to SQL Server 2008 Enterprise Edition

No. All versions can restore the compressed backup. From SQL Server 2008 R2, the Standard Edition can also be compressed and backed up.

 

24 t) you can skip the upgrade process by restoring a database of a lower version to an instance of a higher version.

No. During data restoration and attachment, the necessary upgrade and recovery processes cannot be skipped.

 

24 u) Databases backed up under 32-bit instances cannot be recovered to 64-bit instances. And vice versa

Error. The internal format of the database has nothing to do with the CPU architecture.

 

24 v) as long as the data is restored, the program can be executed normally.

No, just like image failover in high availability and transaction log transfer to the secondary server, there are still many additional steps that need to be done to ensure normal program execution. Including the secondary database and correct login name.

 

24 w) to restore damaged files, you must restore them from multiple file groups.

No, this is true in SQL Server 2000, but it is completely unnecessary in Versions later than SQL Server 2005.

 

24 x) you can restore the database to any instance of the latest version.

No, the database can only be restored to one or two of its new versions. (For example, the database under SQL Server 7.0 cannot be restored to SQL Server 2008 ).

 

24 y) The restoration time is the same as the restoration time.

No, many factors will affect the restoration time, such as whether a long transaction needs to be rolled back or whether the instant file initialization feature is enabled.

 

24 z) Drop the restored database before restoring the database

No. If you Drop the restored database before restoring the database, the restoration process requires instant file initialization, you 'd better keep copies of the restored database to minimize the loss if the restoration fails.