MSSQL Paragraph restore script, SQL Server paragraph script _mssql

The paragraph restore remains checked to ensure that the database will be consistent at the end. After the restore sequence is completed, if the recovered file is valid and is consistent with the database, the recovered file becomes online directly.

Paragraph restores apply to all recovery modes, but are more flexible than in the simple recovery model under the full and bulk-logged recovery models.

All paragraph restores begin with the initial restore sequence, called partial restore order. Partial restore order restores and restores at least the primary filegroup, and all read/write filegroups are restored and restored under the simple recovery model. In the paragraph restore order, the entire database must be offline. The database is then brought online and the restored filegroup is in the available state. However, all files that are not restored will remain offline and cannot be accessed. However, for any offline filegroup, you can restore and go online later through a file restore.

The partial restore sequence begins with the RESTORE DATABASE statement, regardless of the recovery model that the database uses, and the statement restores the full backup and specifies the PARTIAL option. The PARTIAL option always initiates a new paragraph restore, so you can only specify PARTIAL once in the initial statement of the partial restore sequence. When partial restore sequences are complete and the database is online, the status of these files becomes "resumed pending" due to the delay in the recovery of the remaining files.

After that, a paragraph restore typically includes one or more restore sequences, which are referred to as "filegroup restore order." You can wait for a specific filegroup restore order to be performed, and the length of time you decide to wait. Each filegroup restore sequence restores and restores one or more offline filegroups to the same point as the database. The scheduling and number of file group restore orders depends on your recovery target, the number of offline filegroups you want to restore, and the number of offline filegroup restores in each filegroup restore order.

The exact requirements for performing a paragraph restore depend on the recovery model of the database.

Let's take a look at the examples below:

--Paragraph restore: the database is corrupted in scope, and we have to restore the entire database across multiple data files and even across filegroups. -At this point, if the database is particularly large, the database recovery time will be very long.
However, we can use the paragraph restore provided by SQL Server to recover the database gradually. --First back up the tail log: Backup log [AdventureWorks] to DISK =n ' D:\BACKUP_TEST\LOG_BACK_TAIL.trn ' with No_truncate, NORECOVERY, compression,stats=10--Partial restore PRIMARY filegroup primary:restore DATABASE [AdventureWorks] filegroup=n ' PRIMARY ' from Disk=n ' D:\BACKUP_ Test\ad_full.bak ' with partial,norecovery,stats=10--Restore the Sub filegroup mst:restore DATABASE [AdventureWorks] filegroup=n ' MST ' From Disk=n ' D:\BACKUP_TEST\AD_FULL.bak ' with norecovery,stats=10--restore Log in turn: Restore log [AdventureWorks] from Disk=n ' d:\ Backup_test\log_back_1.trn ' with norecovery,stats=10 RESTORE LOG [AdventureWorks] from Disk=n ' D:\BACKUP_TEST\LOG_BACK _2.trn ' with norecovery,stats=10-restores the tail log and restores the restore log [AdventureWorks] from Disk=n ' D:\BACKUP_TEST\LOG_BACK_TAIL.
TRN ' with recovery,stats=10-at this point the files in the primary and MST filegroups of the AdventureWorks database are already accessible.
-but tables in other filegroups, such as trn filegroups, are not accessible.

--Message 8653, Level 16, State 1, line 2nd-the query processor cannot generate a plan for the table or view "* * *" because the table resides in a filegroup that is not online. --Next Restore the sub-filegroup trn: RESTORE DATABASE [AdventureWorks] filegroup=n ' TRN ' from Disk=n ' D:\BACKUP_TEST\AD_FULL.bak ' with norecovery,stats=10- -If the database is not an Enterprise edition, the above restore prompts "the end of the log for database" AdventureWorks has not been backed up. "--you need to back up the tail of the log again, which means that the entire database is in the restoring state when you restore the TRN of the sub filegroups. So for the non-enterprise version, only offline paragraph reduction, the personal feeling is not very big ...--restore log in descending order: Restore log [AdventureWorks] from Disk=n ' D:\BACKUP_TEST\LOG_BACK_1. TRN ' with norecovery,stats=10 RESTORE LOG [AdventureWorks] from disk=n ' D:\BACKUP_TEST\LOG_BACK_2.TRN ' with NORECOVERY, STATS=10-Restores the tail log and restores the restore log [AdventureWorks] from Disk=n ' D:\BACKUP_TEST\LOG_BACK_TAIL.
TRN ' with recovery,stats=10-the table in the sub-filegroup TRN is already accessible.
 --paragraph Restore all completed