MSSQL section restoration script, SQLSERVER section script, mssqlsqlserver

MSSQL section restoration script, SQLSERVER section script, mssqlsqlserver

The Section restore is retained to make sure that the database is consistent at the end. After the restoration order ends, if the recovered files are valid and consistent with the database, the recovered files will be directly online.

Paragraph restoration applies to all recovery modes, but it is more flexible in both full recovery mode and large-capacity log recovery mode than in simple recovery mode.

All paragraph restoration starts from the initial restoration order called "partial restoration order. Partial restoration sequence at least restores and restores the primary file group. In simple recovery mode, all read/write file groups are restored and restored. In the paragraph restoration sequence, the entire database must be offline. Then, the database will be online and all restored file groups will be available. However, all unrestored file groups will remain offline and cannot be accessed. However, any offline file group can be restored and online in the future through file restoration.

Regardless of the recovery mode used by the DATABASE, part of the Restoration sequence starts with the restore database statement. This statement restores the full backup and specifies the PARTIAL option. The PARTIAL Option always starts a new paragraph restoration. Therefore, you can only specify PARTIAL once in the initial statement of PARTIAL restoration order. After the restoration sequence is complete and the database is online, the recovery of the remaining files is postponed, and the status of these files changes to "recovered and suspended ".

After that, paragraph restoration usually includes one or more restoration sequences, which are called the "file group restoration sequence ". You can wait for a specific file group restoration sequence. The waiting duration is determined by you. Each file group restores one or more offline file groups and restores them to the point consistent with the database. The time schedule and quantity of file group restoration sequence depend on your recovery target, the number of offline file groups you want to restore, and the number of offline file groups restored in each file group restoration sequence.

The exact requirements for performing paragraph restoration depend on the database recovery mode.

Let's take a look at the instance:

-- Paragraph Restoration: the range of database corruption is relatively large. When there are multiple data files or even cross-file groups, we have to restore the entire database. -- If the database is very large, the database recovery time will be very long. However, we can use the section restoration provided by SQL Server to gradually restore the database. -- First back up the end LOG: backup log [AdventureWorks] to disk = n'd: \ BACKUP_TEST \ LOG_BACK_TAIL.trn 'WITH NO_TRUNCATE, NORECOVERY, COMPRESSION, STATS = 10 -- partially restore the master file group PRIMARY: restore database [AdventureWorks] FILEGROUP = n' PRIMARY 'from disk = n' D: \ BACKUP_TEST \ AD_FULL.bak' with partial, NORECOVERY, STATS = 10 -- RESTORE the sub-file group 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 the end LOG and RESTORE the restore log [AdventureWorks] from disk = n'd: \ BACKUP_TEST \ LOG_BACK_TAIL.TRN 'with recovery, STATS = 10 -- in this case, files in the PRIMARY and MST file groups in the AdventureWorks database can be accessed. -- However, tables in other file groups, such as TRN, cannot be accessed. -- Message 8653, level 16, status 1, 2nd rows -- the query processor cannot generate a plan for the table or view, because the table resides in a file group that is not online. -- Next, RESTORE the sub-file group 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 restoration will prompt the tail of the log "not backed up Database" AdventureWorks." -- The tail of the log needs to be backed up again, which means that the entire database is being restored when the secondary file group TRN is restored. Therefore, for non-Enterprise Edition, only offline paragraphs can be restored. I personally think it is not very significant ...... -- 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 the end LOG and restore log [AdventureWorks] from disk = n'd: \ BACKUP_TEST \ LOG_BACK_TAIL.TRN' with recovery, STATS = 10 -- now the table in the TRN of the sub-file group can be accessed. -- Paragraph restoration is complete

How to restore a database using SQL scripts in SQLServer2000

For example, back up a database named MyDatabase to the root directory of the d disk and restore it. The statement is:
Backup database MyDatabase to disk = 'd: \ MyDatabase. bak -- backup
Restore database MyDatabase from disk = 'd: \ MyDatabase. bak '-- restore
The backup log statement is:
Backup log MyDatabase to disk = 'd: \ MyDatabase_log.bak -- backup log

When writing SQL server scripts, you can stop running them.

You can set breakpoints in SQL Server Management Studio.
You can also use
Raiserror or noexec statement

Raiserror example

Set noexec off
Print 'Hi'
Go
Raiserror ('oh no a fatal error', 20,-1) with log
Go
Print 'ho'

Result
Message 2745, level 16, status 2, 1st rows
Process ID 52 triggers user error 50000, severity 20. SQL Server is terminating this process.
Message 2745, level 16, status 2, 1st rows
Process ID 52 triggers user error 50000, severity 20. SQL Server is terminating this process.
Message 50000, level 20, status 1, 1st rows
Oh no a fatal error
Message 0, level 20, state 0, 0th rows
The current command has a serious error. Discard any possible results.

Noexec example
Set noexec off

Print 'Hi'
Go

Print 'fatal error, script will not continue! '
Set noexec on
Go

Print 'ho'
Go

Result
Hi
Fatal error, script will not continue!