MSSQL paragraph restore script, sqlserver paragraph script

Time:2021-9-18

Paragraph restore remains checked to ensure that the database will be consistent at the end. After the restore sequence ends, if the recovered files are valid and consistent with the database, the recovered files will directly become online.

Paragraph restore is applicable to all recovery modes, but it is more flexible in full recovery mode and bulk log recovery mode than in simple recovery mode.

All paragraph restores begin with an initial restore sequence called partial restore sequence. The partial restore sequence restores and recovers at least the primary filegroup, and all read / write filegroups in simple recovery mode. In the paragraph restore order, the entire database must be offline. The database will then be online and the restored filegroups will be available. However, all unrecovered filegroups will remain offline and inaccessible. However, any offline filegroup can be restored and brought online later through file restore.

Regardless of the recovery mode of the database, the partial restore sequence starts with the restore database statement, which will restore the full backup and specify the partial option. The partial option always starts a new paragraph restore; Therefore, partial can only be specified once in the initial statement of partial restore order. When the partial restore sequence is completed and the database is online, the status of the remaining files will change to “recovery suspended” because the recovery of the remaining files is delayed.

Thereafter, paragraph restore usually includes one or more restore orders, which are called “filegroup restore order”. You can wait to perform a specific filegroup restore sequence for as long as you decide. Each filegroup restore order restores and restores one or more offline filegroups to a point consistent with the database. The timing and number of filegroup restore orders depend on your recovery destination, the number of offline filegroups you want to restore, and the number of offline filegroups restored in each filegroup restore order.

The precise requirements for performing a paragraph restore depend on the recovery mode of the database.

Let’s take a look at an example:

--Paragraph restore: the damage scope of the database is relatively large. When it spans multiple data files or even file groups, we have to restore the whole database.
--At this time, if the database is very large, the database recovery time will be very long. However, we can use the paragraph restore provided by SQL server to recover the database step by step.
--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:
RESTORE DATABASE [AdventureWorks] FILEGROUP=N'PRIMARY' FROM DISK=N'D:\BACKUP_TEST\AD_FULL.bak' WITH PARTIAL,NORECOVERY,STATS=10
--Restore secondary filegroup MST:
RESTORE DATABASE [AdventureWorks] FILEGROUP=N'MST' FROM DISK=N'D:\BACKUP_TEST\AD_FULL.bak' WITH NORECOVERY,STATS=10
--Restore logs in sequence:
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
--Restore tail log and recover
RESTORE LOG [AdventureWorks] FROM DISK=N'D:\BACKUP_TEST\LOG_BACK_TAIL.TRN' WITH RECOVERY,STATS=10
--At this time, the files in the primary and MST filegroups in the AdventureWorks database are already accessible.
--However, tables in other filegroups, such as TRN filegroups, cannot be accessed.
--Message 8653, level 16, status 1, line 2
--The query processor cannot generate a plan for table or view '* * * *' because the table resides in a filegroup that is not online.

--Next, restore the secondary 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 version, the above restore will prompt "the tail of the log of the database" AdventureWorks "has not been backed up."
--The tail of the log needs to be backed up again, which means that the entire database is being restored when the secondary filegroup TRN is restored. Therefore, for the non enterprise version, it can only be restored offline. Personally, I don't think it is of great significance
--Restore logs in sequence:
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
--Restore tail log and recover
RESTORE LOG [AdventureWorks] FROM DISK=N'D:\BACKUP_TEST\LOG_BACK_TAIL.TRN' WITH RECOVERY,STATS=10
--At this point, the table in the secondary filegroup TRN is already accessible.
--Paragraph restore complete