Restore، Point-in-Time، File/Page و Piecemeal Restore در SQL Server

Chapter 12 — Restore, Point-in-Time, File/Page and Piecemeal Restore

نویسنده: Peter A. Carter

زبان منبع: انگلیسی

محدوده: صفحات PDF 444 تا 469

تاریخ ترجمه: 2026-08-11

اعتبار ترجمه: ترجمه با کمک هوش مصنوعی

یادداشت حقوقی: کاربر حق ترجمه و بازنشر این اثر را برای پروژه تأیید کرده است.

مقاله قبلی | بازگشت به فهرست مادر | مقاله بعدی

PAGE-444

Filegroup Backup و Multiple Devices

در Enterprise می‌توان Full/Differential/Log را روی Device/Pathهای جدا نگه داشت. Backup Striping روی چند Device توان عملیاتی را افزایش می‌دهد و فایل واحد را میان Media Familyها تقسیم می‌کند؛ برای Restore همه Stripeها لازم‌اند.

BACKUP DATABASE Chapter12 FILEGROUP = 'PRIMARY'
TO DISK = 'D:\Backups\fg_primary.bak';

BACKUP DATABASE Chapter12
TO DISK='D:\Backups\stripe1.bak',
   DISK='E:\Backups\stripe2.bak';

PAGE-445

Mirrored Media Set

Backup Mirroring یک نسخه یکسان از Backup را همزمان روی مجموعه Device دوم می‌نویسد. Mirror با Striping قابل ترکیب است، اما تعداد Device در هر Mirror باید یکسان باشد. این قابلیت جای Copy خارج از Server/Offsite را نمی‌گیرد.

Figure 12-5 — بازنمایی از صفحه اصلی PDF 445

PAGE-446

Restore Database — General

Restore Wizard Source، Destination، Backup Set و Recovery State را انتخاب می‌کند. SQL Server از Backup History در msdb برای پیشنهاد Sequence استفاده می‌کند، ولی DBA باید Chain و هدف Recovery را مستقل تأیید کند.

Figure 12-6 — شکل/تصویر منبع، صفحه PDF 446

PAGE-447

Backup Timeline

برای Point-in-Time Restore، Timeline زمان Backupهای Full/Diff/Log و Target Time را نمایش می‌دهد. Restore Sequence باید از Full مناسب شروع، سپس Differential اختیاری و Logها به‌ترتیب LSN اعمال شوند.

Figure 12-7 — شکل/تصویر منبع، صفحه PDF 447

PAGE-448

Files

صفحه Files امکان Relocate Data/Log را هنگام Restore می‌دهد. این موضوع در Migration یا Restore به Server جدید ضروری است، زیرا Pathهای مبدا ممکن است وجود نداشته باشند. WITH MOVE معادل T-SQL آن است.

Figure 12-8 — شکل/تصویر منبع، صفحه PDF 448

PAGE-449

Options و Recovery State

WITH RECOVERY Database را Online می‌کند و Log بعدی قابل Restore نیست؛ NORECOVERY Database را Restoring نگه می‌دارد؛ STANDBY امکان Read-Only میان Log Restoreها می‌دهد و Undo File نیاز دارد. PARTIAL برای Piecemeal Restore استفاده می‌شود.

Figure 12-8 — شکل/تصویر منبع، صفحه PDF 449

PAGE-450

Restore Optionهای مهم
گزینه	معنا
RECOVERY	پایان Sequence و Online شدن
NORECOVERY	آماده دریافت Backup بعدی
STANDBY	Read-only با امکان ادامه Log Restore
REPLACE	اجازه جایگزینی Database با کنترل‌های کمتر؛ پرریسک
MOVE	تغییر مسیر Logical File
STOPAT	توقف Log Restore در زمان مشخص

Table 12-8 — بازنمایی متن فنی جدول منبع
--- PDF PAGE 450 --- 436 Restoring via T-SQL When using the RESTORE command in T-SQL, in addition to restoring a database, the options detailed in Table 12-8 are available. Table 12-8. Restore Options Restore Option Description RESTORE FILELISTONLY Returns a list of all files in the backup device. RESTORE HEADERONLY Returns the backup headers for all backup sets within a backup device. RESTORE LABELONLY Returns information regarding the media set and media family to which the backup device belongs. RESTORE REWINDONLY Closes and rewinds the tape. Only works if the backup device is a tape. RESTORE VERIFYONLY Checks that all backup devices exist and are readable. Also performs other high-level verification checks, such as ensuring there is enough space on the destination drive, checking the CHECKSUM (providing the backup was taken with CHECKSUM), and checking key Page Header fields. When using the RESTORE command to perform a restore, you can use many arguments to allow many restore scenarios to take place. These arguments can be categorized as follows: • Restore arguments (described in Table 12-9) • WITH options (described in Table 12-10) • Backup set options (described in Table 12-11) • Media set options (described in Table 12-12) • Error management options (described in Table 12-13) • Miscellaneous options (described in Table 12-14) Chapter 12 Backups and Restores

--- PDF PAGE 450 ---
436
Restoring via T-SQL
When using the RESTORE command in T-SQL, in addition to restoring a database, the 
options detailed in Table 12-8 are available.
Table 12-8.  Restore Options
Restore Option
Description
RESTORE FILELISTONLY
Returns a list of all files in the backup device.
RESTORE HEADERONLY
Returns the backup headers for all backup sets within  
a backup device.
RESTORE LABELONLY
Returns information regarding the media set and media family to 
which the backup device belongs.
RESTORE REWINDONLY
Closes and rewinds the tape. Only works if the backup device  
is a tape.
RESTORE VERIFYONLY
Checks that all backup devices exist and are readable.  
Also performs other high-level verification checks, such as  
ensuring there is enough space on the destination drive, checking 
the CHECKSUM (providing the backup was taken with CHECKSUM), 
and checking key Page Header fields.
When using the RESTORE command to perform a restore, you can use many 
arguments to allow many restore scenarios to take place. These arguments can be 
categorized as follows:
•	
Restore arguments (described in Table 12-9)
•	
WITH options (described in Table 12-10)
•	
Backup set options (described in Table 12-11)
•	
Media set options (described in Table 12-12)
•	
Error management options (described in Table 12-13)
•	
Miscellaneous options (described in Table 12-14)
Chapter 12  Backups and Restores

Table 12-9 — بازنمایی متن فنی جدول منبع
--- PDF PAGE 450 --- 436 Restoring via T-SQL When using the RESTORE command in T-SQL, in addition to restoring a database, the options detailed in Table 12-8 are available. Table 12-8. Restore Options Restore Option Description RESTORE FILELISTONLY Returns a list of all files in the backup device. RESTORE HEADERONLY Returns the backup headers for all backup sets within a backup device. RESTORE LABELONLY Returns information regarding the media set and media family to which the backup device belongs. RESTORE REWINDONLY Closes and rewinds the tape. Only works if the backup device is a tape. RESTORE VERIFYONLY Checks that all backup devices exist and are readable. Also performs other high-level verification checks, such as ensuring there is enough space on the destination drive, checking the CHECKSUM (providing the backup was taken with CHECKSUM), and checking key Page Header fields. When using the RESTORE command to perform a restore, you can use many arguments to allow many restore scenarios to take place. These arguments can be categorized as follows: • Restore arguments (described in Table 12-9) • WITH options (described in Table 12-10) • Backup set options (described in Table 12-11) • Media set options (described in Table 12-12) • Error management options (described in Table 12-13) • Miscellaneous options (described in Table 12-14) Chapter 12 Backups and Restores --- PDF PAGE 451 --- 437 Table 12-9. Restore Arguments Argument Description DATABASE/LOG Specify DATABASE to which to restore all or some of the files that constitute the database. Specify LOG to restore a transaction log backup. database_name Specifies the name of the target database that will be restored. file_or_filegroup_ or_pages Specifies a comma-separated list of the files, filegroups, or pages to be restored. If restoring pages, use the format PAGE = FileID:PageID. In SIMPLE recovery model, files and filegroups can only be specified if they are read-only or if you are performing a partial restore using WITH PARTIAL. READ_WRITE_FILEGROUPS Restores all read/write filegroups but no read-only filegroups. FROM A comma-separated list of backup devices that contains the backup set to restore or the name of the database snapshot from which you wish to restore. Database snapshots are discussed in Chapter 16. Table 12-10. WITH Options Argument Description PARTIAL Indicates that this is the first restore in a piecemeal restore, which is discussed later in this chapter. RECOVERY/NORECOVERY/ STANDBY Specifies the state that the database should be left in when the restore operation completes. RECOVERY indicates that the database will be brought online. NORECOVERY indicates that the database will remain in a restoring state so that subsequent restores can be applied. STANDBY indicates that the database will be brought online in read-only mode. MOVE Used to specify the file system location that the files should be restored to if this is different from the original location. CREDENTIAL Used when performing a restore from a Windows Azure Blob. (continued) Chapter 12 Backups and Restores

--- PDF PAGE 450 ---
436
Restoring via T-SQL
When using the RESTORE command in T-SQL, in addition to restoring a database, the 
options detailed in Table 12-8 are available.
Table 12-8.  Restore Options
Restore Option
Description
RESTORE FILELISTONLY
Returns a list of all files in the backup device.
RESTORE HEADERONLY
Returns the backup headers for all backup sets within  
a backup device.
RESTORE LABELONLY
Returns information regarding the media set and media family to 
which the backup device belongs.
RESTORE REWINDONLY
Closes and rewinds the tape. Only works if the backup device  
is a tape.
RESTORE VERIFYONLY
Checks that all backup devices exist and are readable.  
Also performs other high-level verification checks, such as  
ensuring there is enough space on the destination drive, checking 
the CHECKSUM (providing the backup was taken with CHECKSUM), 
and checking key Page Header fields.
When using the RESTORE command to perform a restore, you can use many 
arguments to allow many restore scenarios to take place. These arguments can be 
categorized as follows:
•	
Restore arguments (described in Table 12-9)
•	
WITH options (described in Table 12-10)
•	
Backup set options (described in Table 12-11)
•	
Media set options (described in Table 12-12)
•	
Error management options (described in Table 12-13)
•	
Miscellaneous options (described in Table 12-14)
Chapter 12  Backups and Restores

--- PDF PAGE 451 ---
437
Table 12-9.  Restore Arguments
Argument
Description
DATABASE/LOG
Specify DATABASE to which to restore all or some of the 
files that constitute the database. Specify LOG to restore a 
transaction log backup.
database_name
Specifies the name of the target database that will be restored.
file_or_filegroup_ 
or_pages
Specifies a comma-separated list of the files, filegroups, or 
pages to be restored. If restoring pages, use the format PAGE 
= FileID:PageID. In SIMPLE recovery model, files and 
filegroups can only be specified if they are read-only or if you 
are performing a partial restore using WITH PARTIAL.
READ_WRITE_FILEGROUPS
Restores all read/write filegroups but no read-only filegroups.
FROM
A comma-separated list of backup devices that contains the 
backup set to restore or the name of the database snapshot 
from which you wish to restore. Database snapshots are 
discussed in Chapter 16.
Table 12-10.  WITH Options
Argument
Description
PARTIAL
Indicates that this is the first restore in a piecemeal restore, which 
is discussed later in this chapter.
RECOVERY/NORECOVERY/ 
STANDBY
Specifies the state that the database should be left in when 
the restore operation completes. RECOVERY indicates that the 
database will be brought online. NORECOVERY indicates that 
the database will remain in a restoring state so that subsequent 
restores can be applied. STANDBY indicates that the database will 
be brought online in read-only mode.
MOVE
Used to specify the file system location that the files should be 
restored to if this is different from the original location.
CREDENTIAL
Used when performing a restore from a Windows Azure Blob.
(continued)
Chapter 12  Backups and Restores

Table 12-10 — بازنمایی متن فنی جدول منبع
--- PDF PAGE 450 --- 436 Restoring via T-SQL When using the RESTORE command in T-SQL, in addition to restoring a database, the options detailed in Table 12-8 are available. Table 12-8. Restore Options Restore Option Description RESTORE FILELISTONLY Returns a list of all files in the backup device. RESTORE HEADERONLY Returns the backup headers for all backup sets within a backup device. RESTORE LABELONLY Returns information regarding the media set and media family to which the backup device belongs. RESTORE REWINDONLY Closes and rewinds the tape. Only works if the backup device is a tape. RESTORE VERIFYONLY Checks that all backup devices exist and are readable. Also performs other high-level verification checks, such as ensuring there is enough space on the destination drive, checking the CHECKSUM (providing the backup was taken with CHECKSUM), and checking key Page Header fields. When using the RESTORE command to perform a restore, you can use many arguments to allow many restore scenarios to take place. These arguments can be categorized as follows: • Restore arguments (described in Table 12-9) • WITH options (described in Table 12-10) • Backup set options (described in Table 12-11) • Media set options (described in Table 12-12) • Error management options (described in Table 12-13) • Miscellaneous options (described in Table 12-14) Chapter 12 Backups and Restores --- PDF PAGE 451 --- 437 Table 12-9. Restore Arguments Argument Description DATABASE/LOG Specify DATABASE to which to restore all or some of the files that constitute the database. Specify LOG to restore a transaction log backup. database_name Specifies the name of the target database that will be restored. file_or_filegroup_ or_pages Specifies a comma-separated list of the files, filegroups, or pages to be restored. If restoring pages, use the format PAGE = FileID:PageID. In SIMPLE recovery model, files and filegroups can only be specified if they are read-only or if you are performing a partial restore using WITH PARTIAL. READ_WRITE_FILEGROUPS Restores all read/write filegroups but no read-only filegroups. FROM A comma-separated list of backup devices that contains the backup set to restore or the name of the database snapshot from which you wish to restore. Database snapshots are discussed in Chapter 16. Table 12-10. WITH Options Argument Description PARTIAL Indicates that this is the first restore in a piecemeal restore, which is discussed later in this chapter. RECOVERY/NORECOVERY/ STANDBY Specifies the state that the database should be left in when the restore operation completes. RECOVERY indicates that the database will be brought online. NORECOVERY indicates that the database will remain in a restoring state so that subsequent restores can be applied. STANDBY indicates that the database will be brought online in read-only mode. MOVE Used to specify the file system location that the files should be restored to if this is different from the original location. CREDENTIAL Used when performing a restore from a Windows Azure Blob. (continued) Chapter 12 Backups and Restores --- PDF PAGE 452 --- 438 Table 12-11. Backup Set Options Argument Description FILE Indicates the sequential number of the backup set, within the media set, to be used. PASSWORD If you are restoring a backup that was taken in SQL Server 2008 or earlier where a password was specified during the backup operation, then you need to use this argument to be able to restore the backup. Table 12-12. Media Set Options Argument Description MEDIANAME If you use this argument, then the MEDIANAME must match the name of the media set allocated during the creation of the media set. MEDIAPASSWORD If you are restoring from a media set created using SQL Server 2008 or earlier and a password was specified for the media set, then you must use this argument during the restore operation. BLOCKSIZE Specifies the block size to use for the restore operation, in bytes, to override the default value of 65,536 for tape and 512 for disk or URL. Argument Description REPLACE If a database already exists on the instance with the target database name that you have specified in the restore statement, or if the files already exist in the operating system with the same name or location, then REPLACE indicates that the database or files should be overwritten. RESTART Indicates that if the restore operation is interrupted, it should be restarted from that point. RESTRICTED_USER Indicates that only administrators and members of the db_owner and db_creator roles should have access to the database after the restore operation completes. Table 12-10. (continued) Chapter 12 Backups and Restores

--- PDF PAGE 450 ---
436
Restoring via T-SQL
When using the RESTORE command in T-SQL, in addition to restoring a database, the 
options detailed in Table 12-8 are available.
Table 12-8.  Restore Options
Restore Option
Description
RESTORE FILELISTONLY
Returns a list of all files in the backup device.
RESTORE HEADERONLY
Returns the backup headers for all backup sets within  
a backup device.
RESTORE LABELONLY
Returns information regarding the media set and media family to 
which the backup device belongs.
RESTORE REWINDONLY
Closes and rewinds the tape. Only works if the backup device  
is a tape.
RESTORE VERIFYONLY
Checks that all backup devices exist and are readable.  
Also performs other high-level verification checks, such as  
ensuring there is enough space on the destination drive, checking 
the CHECKSUM (providing the backup was taken with CHECKSUM), 
and checking key Page Header fields.
When using the RESTORE command to perform a restore, you can use many 
arguments to allow many restore scenarios to take place. These arguments can be 
categorized as follows:
•	
Restore arguments (described in Table 12-9)
•	
WITH options (described in Table 12-10)
•	
Backup set options (described in Table 12-11)
•	
Media set options (described in Table 12-12)
•	
Error management options (described in Table 12-13)
•	
Miscellaneous options (described in Table 12-14)
Chapter 12  Backups and Restores

--- PDF PAGE 451 ---
437
Table 12-9.  Restore Arguments
Argument
Description
DATABASE/LOG
Specify DATABASE to which to restore all or some of the 
files that constitute the database. Specify LOG to restore a 
transaction log backup.
database_name
Specifies the name of the target database that will be restored.
file_or_filegroup_ 
or_pages
Specifies a comma-separated list of the files, filegroups, or 
pages to be restored. If restoring pages, use the format PAGE 
= FileID:PageID. In SIMPLE recovery model, files and 
filegroups can only be specified if they are read-only or if you 
are performing a partial restore using WITH PARTIAL.
READ_WRITE_FILEGROUPS
Restores all read/write filegroups but no read-only filegroups.
FROM
A comma-separated list of backup devices that contains the 
backup set to restore or the name of the database snapshot 
from which you wish to restore. Database snapshots are 
discussed in Chapter 16.
Table 12-10.  WITH Options
Argument
Description
PARTIAL
Indicates that this is the first restore in a piecemeal restore, which 
is discussed later in this chapter.
RECOVERY/NORECOVERY/ 
STANDBY
Specifies the state that the database should be left in when 
the restore operation completes. RECOVERY indicates that the 
database will be brought online. NORECOVERY indicates that 
the database will remain in a restoring state so that subsequent 
restores can be applied. STANDBY indicates that the database will 
be brought online in read-only mode.
MOVE
Used to specify the file system location that the files should be 
restored to if this is different from the original location.
CREDENTIAL
Used when performing a restore from a Windows Azure Blob.
(continued)
Chapter 12  Backups and Restores

--- PDF PAGE 452 ---
438
Table 12-11.  Backup Set Options
Argument
Description
FILE
Indicates the sequential number of the backup set, within the media set, to be used.
PASSWORD
If you are restoring a backup that was taken in SQL Server 2008 or earlier where 
a password was specified during the backup operation, then you need to use this 
argument to be able to restore the backup.
Table 12-12.  Media Set Options
Argument
Description
MEDIANAME
If you use this argument, then the MEDIANAME must match the name of the 
media set allocated during the creation of the media set.
MEDIAPASSWORD
If you are restoring from a media set created using SQL Server 2008 or 
earlier and a password was specified for the media set, then you must use 
this argument during the restore operation.
BLOCKSIZE
Specifies the block size to use for the restore operation, in bytes, to override 
the default value of 65,536 for tape and 512 for disk or URL.
Argument
Description
REPLACE
If a database already exists on the instance with the target 
database name that you have specified in the restore statement, 
or if the files already exist in the operating system with the same 
name or location, then REPLACE indicates that the database or 
files should be overwritten.
RESTART
Indicates that if the restore operation is interrupted, it should be 
restarted from that point.
RESTRICTED_USER
Indicates that only administrators and members of the db_owner 
and db_creator roles should have access to the database after 
the restore operation completes.
Table 12-10.  (continued)
Chapter 12  Backups and Restores

Table 12-11 — بازنمایی متن فنی جدول منبع
--- PDF PAGE 450 --- 436 Restoring via T-SQL When using the RESTORE command in T-SQL, in addition to restoring a database, the options detailed in Table 12-8 are available. Table 12-8. Restore Options Restore Option Description RESTORE FILELISTONLY Returns a list of all files in the backup device. RESTORE HEADERONLY Returns the backup headers for all backup sets within a backup device. RESTORE LABELONLY Returns information regarding the media set and media family to which the backup device belongs. RESTORE REWINDONLY Closes and rewinds the tape. Only works if the backup device is a tape. RESTORE VERIFYONLY Checks that all backup devices exist and are readable. Also performs other high-level verification checks, such as ensuring there is enough space on the destination drive, checking the CHECKSUM (providing the backup was taken with CHECKSUM), and checking key Page Header fields. When using the RESTORE command to perform a restore, you can use many arguments to allow many restore scenarios to take place. These arguments can be categorized as follows: • Restore arguments (described in Table 12-9) • WITH options (described in Table 12-10) • Backup set options (described in Table 12-11) • Media set options (described in Table 12-12) • Error management options (described in Table 12-13) • Miscellaneous options (described in Table 12-14) Chapter 12 Backups and Restores --- PDF PAGE 452 --- 438 Table 12-11. Backup Set Options Argument Description FILE Indicates the sequential number of the backup set, within the media set, to be used. PASSWORD If you are restoring a backup that was taken in SQL Server 2008 or earlier where a password was specified during the backup operation, then you need to use this argument to be able to restore the backup. Table 12-12. Media Set Options Argument Description MEDIANAME If you use this argument, then the MEDIANAME must match the name of the media set allocated during the creation of the media set. MEDIAPASSWORD If you are restoring from a media set created using SQL Server 2008 or earlier and a password was specified for the media set, then you must use this argument during the restore operation. BLOCKSIZE Specifies the block size to use for the restore operation, in bytes, to override the default value of 65,536 for tape and 512 for disk or URL. Argument Description REPLACE If a database already exists on the instance with the target database name that you have specified in the restore statement, or if the files already exist in the operating system with the same name or location, then REPLACE indicates that the database or files should be overwritten. RESTART Indicates that if the restore operation is interrupted, it should be restarted from that point. RESTRICTED_USER Indicates that only administrators and members of the db_owner and db_creator roles should have access to the database after the restore operation completes. Table 12-10. (continued) Chapter 12 Backups and Restores

--- PDF PAGE 450 ---
436
Restoring via T-SQL
When using the RESTORE command in T-SQL, in addition to restoring a database, the 
options detailed in Table 12-8 are available.
Table 12-8.  Restore Options
Restore Option
Description
RESTORE FILELISTONLY
Returns a list of all files in the backup device.
RESTORE HEADERONLY
Returns the backup headers for all backup sets within  
a backup device.
RESTORE LABELONLY
Returns information regarding the media set and media family to 
which the backup device belongs.
RESTORE REWINDONLY
Closes and rewinds the tape. Only works if the backup device  
is a tape.
RESTORE VERIFYONLY
Checks that all backup devices exist and are readable.  
Also performs other high-level verification checks, such as  
ensuring there is enough space on the destination drive, checking 
the CHECKSUM (providing the backup was taken with CHECKSUM), 
and checking key Page Header fields.
When using the RESTORE command to perform a restore, you can use many 
arguments to allow many restore scenarios to take place. These arguments can be 
categorized as follows:
•	
Restore arguments (described in Table 12-9)
•	
WITH options (described in Table 12-10)
•	
Backup set options (described in Table 12-11)
•	
Media set options (described in Table 12-12)
•	
Error management options (described in Table 12-13)
•	
Miscellaneous options (described in Table 12-14)
Chapter 12  Backups and Restores

--- PDF PAGE 452 ---
438
Table 12-11.  Backup Set Options
Argument
Description
FILE
Indicates the sequential number of the backup set, within the media set, to be used.
PASSWORD
If you are restoring a backup that was taken in SQL Server 2008 or earlier where 
a password was specified during the backup operation, then you need to use this 
argument to be able to restore the backup.
Table 12-12.  Media Set Options
Argument
Description
MEDIANAME
If you use this argument, then the MEDIANAME must match the name of the 
media set allocated during the creation of the media set.
MEDIAPASSWORD
If you are restoring from a media set created using SQL Server 2008 or 
earlier and a password was specified for the media set, then you must use 
this argument during the restore operation.
BLOCKSIZE
Specifies the block size to use for the restore operation, in bytes, to override 
the default value of 65,536 for tape and 512 for disk or URL.
Argument
Description
REPLACE
If a database already exists on the instance with the target 
database name that you have specified in the restore statement, 
or if the files already exist in the operating system with the same 
name or location, then REPLACE indicates that the database or 
files should be overwritten.
RESTART
Indicates that if the restore operation is interrupted, it should be 
restarted from that point.
RESTRICTED_USER
Indicates that only administrators and members of the db_owner 
and db_creator roles should have access to the database after 
the restore operation completes.
Table 12-10.  (continued)
Chapter 12  Backups and Restores

Table 12-12 — بازنمایی متن فنی جدول منبع
--- PDF PAGE 450 --- 436 Restoring via T-SQL When using the RESTORE command in T-SQL, in addition to restoring a database, the options detailed in Table 12-8 are available. Table 12-8. Restore Options Restore Option Description RESTORE FILELISTONLY Returns a list of all files in the backup device. RESTORE HEADERONLY Returns the backup headers for all backup sets within a backup device. RESTORE LABELONLY Returns information regarding the media set and media family to which the backup device belongs. RESTORE REWINDONLY Closes and rewinds the tape. Only works if the backup device is a tape. RESTORE VERIFYONLY Checks that all backup devices exist and are readable. Also performs other high-level verification checks, such as ensuring there is enough space on the destination drive, checking the CHECKSUM (providing the backup was taken with CHECKSUM), and checking key Page Header fields. When using the RESTORE command to perform a restore, you can use many arguments to allow many restore scenarios to take place. These arguments can be categorized as follows: • Restore arguments (described in Table 12-9) • WITH options (described in Table 12-10) • Backup set options (described in Table 12-11) • Media set options (described in Table 12-12) • Error management options (described in Table 12-13) • Miscellaneous options (described in Table 12-14) Chapter 12 Backups and Restores --- PDF PAGE 452 --- 438 Table 12-11. Backup Set Options Argument Description FILE Indicates the sequential number of the backup set, within the media set, to be used. PASSWORD If you are restoring a backup that was taken in SQL Server 2008 or earlier where a password was specified during the backup operation, then you need to use this argument to be able to restore the backup. Table 12-12. Media Set Options Argument Description MEDIANAME If you use this argument, then the MEDIANAME must match the name of the media set allocated during the creation of the media set. MEDIAPASSWORD If you are restoring from a media set created using SQL Server 2008 or earlier and a password was specified for the media set, then you must use this argument during the restore operation. BLOCKSIZE Specifies the block size to use for the restore operation, in bytes, to override the default value of 65,536 for tape and 512 for disk or URL. Argument Description REPLACE If a database already exists on the instance with the target database name that you have specified in the restore statement, or if the files already exist in the operating system with the same name or location, then REPLACE indicates that the database or files should be overwritten. RESTART Indicates that if the restore operation is interrupted, it should be restarted from that point. RESTRICTED_USER Indicates that only administrators and members of the db_owner and db_creator roles should have access to the database after the restore operation completes. Table 12-10. (continued) Chapter 12 Backups and Restores

--- PDF PAGE 450 ---
436
Restoring via T-SQL
When using the RESTORE command in T-SQL, in addition to restoring a database, the 
options detailed in Table 12-8 are available.
Table 12-8.  Restore Options
Restore Option
Description
RESTORE FILELISTONLY
Returns a list of all files in the backup device.
RESTORE HEADERONLY
Returns the backup headers for all backup sets within  
a backup device.
RESTORE LABELONLY
Returns information regarding the media set and media family to 
which the backup device belongs.
RESTORE REWINDONLY
Closes and rewinds the tape. Only works if the backup device  
is a tape.
RESTORE VERIFYONLY
Checks that all backup devices exist and are readable.  
Also performs other high-level verification checks, such as  
ensuring there is enough space on the destination drive, checking 
the CHECKSUM (providing the backup was taken with CHECKSUM), 
and checking key Page Header fields.
When using the RESTORE command to perform a restore, you can use many 
arguments to allow many restore scenarios to take place. These arguments can be 
categorized as follows:
•	
Restore arguments (described in Table 12-9)
•	
WITH options (described in Table 12-10)
•	
Backup set options (described in Table 12-11)
•	
Media set options (described in Table 12-12)
•	
Error management options (described in Table 12-13)
•	
Miscellaneous options (described in Table 12-14)
Chapter 12  Backups and Restores

--- PDF PAGE 452 ---
438
Table 12-11.  Backup Set Options
Argument
Description
FILE
Indicates the sequential number of the backup set, within the media set, to be used.
PASSWORD
If you are restoring a backup that was taken in SQL Server 2008 or earlier where 
a password was specified during the backup operation, then you need to use this 
argument to be able to restore the backup.
Table 12-12.  Media Set Options
Argument
Description
MEDIANAME
If you use this argument, then the MEDIANAME must match the name of the 
media set allocated during the creation of the media set.
MEDIAPASSWORD
If you are restoring from a media set created using SQL Server 2008 or 
earlier and a password was specified for the media set, then you must use 
this argument during the restore operation.
BLOCKSIZE
Specifies the block size to use for the restore operation, in bytes, to override 
the default value of 65,536 for tape and 512 for disk or URL.
Argument
Description
REPLACE
If a database already exists on the instance with the target 
database name that you have specified in the restore statement, 
or if the files already exist in the operating system with the same 
name or location, then REPLACE indicates that the database or 
files should be overwritten.
RESTART
Indicates that if the restore operation is interrupted, it should be 
restarted from that point.
RESTRICTED_USER
Indicates that only administrators and members of the db_owner 
and db_creator roles should have access to the database after 
the restore operation completes.
Table 12-10.  (continued)
Chapter 12  Backups and Restores

Table 12-13 — بازنمایی متن فنی جدول منبع
--- PDF PAGE 450 --- 436 Restoring via T-SQL When using the RESTORE command in T-SQL, in addition to restoring a database, the options detailed in Table 12-8 are available. Table 12-8. Restore Options Restore Option Description RESTORE FILELISTONLY Returns a list of all files in the backup device. RESTORE HEADERONLY Returns the backup headers for all backup sets within a backup device. RESTORE LABELONLY Returns information regarding the media set and media family to which the backup device belongs. RESTORE REWINDONLY Closes and rewinds the tape. Only works if the backup device is a tape. RESTORE VERIFYONLY Checks that all backup devices exist and are readable. Also performs other high-level verification checks, such as ensuring there is enough space on the destination drive, checking the CHECKSUM (providing the backup was taken with CHECKSUM), and checking key Page Header fields. When using the RESTORE command to perform a restore, you can use many arguments to allow many restore scenarios to take place. These arguments can be categorized as follows: • Restore arguments (described in Table 12-9) • WITH options (described in Table 12-10) • Backup set options (described in Table 12-11) • Media set options (described in Table 12-12) • Error management options (described in Table 12-13) • Miscellaneous options (described in Table 12-14) Chapter 12 Backups and Restores --- PDF PAGE 453 --- 439 Table 12-13. Error Management Options Argument Description CHECKSUM/NOCHECKSUM If CHECKSUM was specified during the backup operation, then specifying CHECKSUM during the restore operation will verify page integrity during the restore operation. Specifying NOCHECKSUM disables this verification. CONTINUE_AFTER_ERROR/ STOP_ON_ERROR STOP_ON_ERROR causes the restore operation to terminate if any damaged pages are discovered. CONTINUE_AFTER_ERROR causes the restore operation to continue, even if damaged pages are discovered. Table 12-14. Miscellaneous Options Argument Description BUFFERCOUNT The total number of IO buffers used for the restore operation. MAXTRANSFERSIZE The largest possible unit of transfer between SQL Server and the backup media, specified in bytes. STATS Specifies how often progress messages should be displayed. The default is to display a progress message in 5% increments. FILESTREAM (DIRECTORY_NAME) Specifies the name of the folder to which FILESTREAM data should be restored. KEEP_REPLICATION Preserves the replication settings. Use this option when configuring log shipping with replication. KEEP_CDC Preserves the change data capture (CDC) settings of a database when it is being restored. Only relevant if CDC was enabled at the time of the backup operation. (continued) Chapter 12 Backups and Restores

--- PDF PAGE 450 ---
436
Restoring via T-SQL
When using the RESTORE command in T-SQL, in addition to restoring a database, the 
options detailed in Table 12-8 are available.
Table 12-8.  Restore Options
Restore Option
Description
RESTORE FILELISTONLY
Returns a list of all files in the backup device.
RESTORE HEADERONLY
Returns the backup headers for all backup sets within  
a backup device.
RESTORE LABELONLY
Returns information regarding the media set and media family to 
which the backup device belongs.
RESTORE REWINDONLY
Closes and rewinds the tape. Only works if the backup device  
is a tape.
RESTORE VERIFYONLY
Checks that all backup devices exist and are readable.  
Also performs other high-level verification checks, such as  
ensuring there is enough space on the destination drive, checking 
the CHECKSUM (providing the backup was taken with CHECKSUM), 
and checking key Page Header fields.
When using the RESTORE command to perform a restore, you can use many 
arguments to allow many restore scenarios to take place. These arguments can be 
categorized as follows:
•	
Restore arguments (described in Table 12-9)
•	
WITH options (described in Table 12-10)
•	
Backup set options (described in Table 12-11)
•	
Media set options (described in Table 12-12)
•	
Error management options (described in Table 12-13)
•	
Miscellaneous options (described in Table 12-14)
Chapter 12  Backups and Restores

--- PDF PAGE 453 ---
439
Table 12-13.  Error Management Options
Argument
Description
CHECKSUM/NOCHECKSUM
If CHECKSUM was specified during the backup operation, then 
specifying CHECKSUM during the restore operation will verify page 
integrity during the restore operation. Specifying NOCHECKSUM 
disables this verification.
CONTINUE_AFTER_ERROR/ 
STOP_ON_ERROR
STOP_ON_ERROR causes the restore operation to terminate if 
any damaged pages are discovered. CONTINUE_AFTER_ERROR 
causes the restore operation to continue, even if damaged pages 
are discovered.
Table 12-14.  Miscellaneous Options
Argument
Description
BUFFERCOUNT
The total number of IO buffers used for the restore operation.
MAXTRANSFERSIZE
The largest possible unit of transfer between SQL Server and the 
backup media, specified in bytes.
STATS
Specifies how often progress messages should be displayed. The 
default is to display a progress message in 5% increments.
FILESTREAM 
(DIRECTORY_NAME)
Specifies the name of the folder to which FILESTREAM data should be 
restored.
KEEP_REPLICATION
Preserves the replication settings. Use this option when configuring log 
shipping with replication.
KEEP_CDC
Preserves the change data capture (CDC) settings of a database when 
it is being restored. Only relevant if CDC was enabled at the time of the 
backup operation.
(continued)
Chapter 12  Backups and Restores

Table 12-14 — بازنمایی متن فنی جدول منبع
--- PDF PAGE 450 --- 436 Restoring via T-SQL When using the RESTORE command in T-SQL, in addition to restoring a database, the options detailed in Table 12-8 are available. Table 12-8. Restore Options Restore Option Description RESTORE FILELISTONLY Returns a list of all files in the backup device. RESTORE HEADERONLY Returns the backup headers for all backup sets within a backup device. RESTORE LABELONLY Returns information regarding the media set and media family to which the backup device belongs. RESTORE REWINDONLY Closes and rewinds the tape. Only works if the backup device is a tape. RESTORE VERIFYONLY Checks that all backup devices exist and are readable. Also performs other high-level verification checks, such as ensuring there is enough space on the destination drive, checking the CHECKSUM (providing the backup was taken with CHECKSUM), and checking key Page Header fields. When using the RESTORE command to perform a restore, you can use many arguments to allow many restore scenarios to take place. These arguments can be categorized as follows: • Restore arguments (described in Table 12-9) • WITH options (described in Table 12-10) • Backup set options (described in Table 12-11) • Media set options (described in Table 12-12) • Error management options (described in Table 12-13) • Miscellaneous options (described in Table 12-14) Chapter 12 Backups and Restores --- PDF PAGE 453 --- 439 Table 12-13. Error Management Options Argument Description CHECKSUM/NOCHECKSUM If CHECKSUM was specified during the backup operation, then specifying CHECKSUM during the restore operation will verify page integrity during the restore operation. Specifying NOCHECKSUM disables this verification. CONTINUE_AFTER_ERROR/ STOP_ON_ERROR STOP_ON_ERROR causes the restore operation to terminate if any damaged pages are discovered. CONTINUE_AFTER_ERROR causes the restore operation to continue, even if damaged pages are discovered. Table 12-14. Miscellaneous Options Argument Description BUFFERCOUNT The total number of IO buffers used for the restore operation. MAXTRANSFERSIZE The largest possible unit of transfer between SQL Server and the backup media, specified in bytes. STATS Specifies how often progress messages should be displayed. The default is to display a progress message in 5% increments. FILESTREAM (DIRECTORY_NAME) Specifies the name of the folder to which FILESTREAM data should be restored. KEEP_REPLICATION Preserves the replication settings. Use this option when configuring log shipping with replication. KEEP_CDC Preserves the change data capture (CDC) settings of a database when it is being restored. Only relevant if CDC was enabled at the time of the backup operation. (continued) Chapter 12 Backups and Restores --- PDF PAGE 454 --- 440 Table 12-14. (continued) Argument Description ENABLE_BROKER/ ERROR_BROKER_ CONVERSATIONS/NEW BROKER ENABLE_BROKER specifies that service broker message delivery will be enabled after the restore operation completes so that messages can immediately be sent. ERROR_BROKER_CONVERSATIONS specifies that all conversations will be terminated with an error message before message delivery is enabled. NEW_BROKER specifies that conversations will be removed without throwing an error and the database will be assigned a new Service Broker identifier. Only relevant if Service Broker was enabled when the backup was created. STOPAT/STOPATMARK/ STOPBEFOREMARK Used for point-in-time recovery and only supported in FULL recovery model. STOPAT specifies a datetime value, which will determine the time of the last transaction to restore. STOPATMARK specifies either an LSN (log sequence number) to restore to or the name of a marked transaction, which will be the final transaction that is restored. STOPBEFOREMARK restores up to the transaction prior to the LSN or marked transaction specified. To perform the same restore operation that we performed through SSMS, we use the command in Listing 12-10. Before running the script, change the path of the backup devices to match your own configuration. Listing 12-10. Restoring a Database USE master GO --Back Up the tail of the log BACKUP LOG Chapter12 TO DISK = N'H:\MSSQL\Backup\Chapter12_LogBackup_2012-02-16_12-17-49.bak' WITH NOFORMAT, NAME = N'Chapter12_LogBackup_2012-02-16_12-17-49', NORECOVERY , STATS = 5 ; --Restore the full backup Chapter 12 Backups and Restores

--- PDF PAGE 450 ---
436
Restoring via T-SQL
When using the RESTORE command in T-SQL, in addition to restoring a database, the 
options detailed in Table 12-8 are available.
Table 12-8.  Restore Options
Restore Option
Description
RESTORE FILELISTONLY
Returns a list of all files in the backup device.
RESTORE HEADERONLY
Returns the backup headers for all backup sets within  
a backup device.
RESTORE LABELONLY
Returns information regarding the media set and media family to 
which the backup device belongs.
RESTORE REWINDONLY
Closes and rewinds the tape. Only works if the backup device  
is a tape.
RESTORE VERIFYONLY
Checks that all backup devices exist and are readable.  
Also performs other high-level verification checks, such as  
ensuring there is enough space on the destination drive, checking 
the CHECKSUM (providing the backup was taken with CHECKSUM), 
and checking key Page Header fields.
When using the RESTORE command to perform a restore, you can use many 
arguments to allow many restore scenarios to take place. These arguments can be 
categorized as follows:
•	
Restore arguments (described in Table 12-9)
•	
WITH options (described in Table 12-10)
•	
Backup set options (described in Table 12-11)
•	
Media set options (described in Table 12-12)
•	
Error management options (described in Table 12-13)
•	
Miscellaneous options (described in Table 12-14)
Chapter 12  Backups and Restores

--- PDF PAGE 453 ---
439
Table 12-13.  Error Management Options
Argument
Description
CHECKSUM/NOCHECKSUM
If CHECKSUM was specified during the backup operation, then 
specifying CHECKSUM during the restore operation will verify page 
integrity during the restore operation. Specifying NOCHECKSUM 
disables this verification.
CONTINUE_AFTER_ERROR/ 
STOP_ON_ERROR
STOP_ON_ERROR causes the restore operation to terminate if 
any damaged pages are discovered. CONTINUE_AFTER_ERROR 
causes the restore operation to continue, even if damaged pages 
are discovered.
Table 12-14.  Miscellaneous Options
Argument
Description
BUFFERCOUNT
The total number of IO buffers used for the restore operation.
MAXTRANSFERSIZE
The largest possible unit of transfer between SQL Server and the 
backup media, specified in bytes.
STATS
Specifies how often progress messages should be displayed. The 
default is to display a progress message in 5% increments.
FILESTREAM 
(DIRECTORY_NAME)
Specifies the name of the folder to which FILESTREAM data should be 
restored.
KEEP_REPLICATION
Preserves the replication settings. Use this option when configuring log 
shipping with replication.
KEEP_CDC
Preserves the change data capture (CDC) settings of a database when 
it is being restored. Only relevant if CDC was enabled at the time of the 
backup operation.
(continued)
Chapter 12  Backups and Restores

--- PDF PAGE 454 ---
440
Table 12-14.  (continued)
Argument
Description
ENABLE_BROKER/
ERROR_BROKER_ 
CONVERSATIONS/NEW  
BROKER
ENABLE_BROKER specifies that service broker message delivery will 
be enabled after the restore operation completes so that messages 
can immediately be sent. ERROR_BROKER_CONVERSATIONS specifies 
that all conversations will be terminated with an error message 
before message delivery is enabled. NEW_BROKER specifies that 
conversations will be removed without throwing an error and the 
database will be assigned a new Service Broker identifier. Only 
relevant if Service Broker was enabled when the backup was created.
STOPAT/STOPATMARK/ 
STOPBEFOREMARK
Used for point-in-time recovery and only supported in FULL recovery 
model. STOPAT specifies a datetime value, which will determine 
the time of the last transaction to restore. STOPATMARK specifies 
either an LSN (log sequence number) to restore to or the name of a 
marked transaction, which will be the final transaction that is restored. 
STOPBEFOREMARK restores up to the transaction prior to the LSN or 
marked transaction specified.
To perform the same restore operation that we performed through SSMS, we use 
the command in Listing 12-10. Before running the script, change the path of the backup 
devices to match your own configuration.
Listing 12-10.  Restoring a Database
USE master
GO
--Back Up the tail of the log
BACKUP LOG Chapter12
TO  DISK = N'H:\MSSQL\Backup\Chapter12_LogBackup_2012-02-16_12-17-49.bak'
        WITH NOFORMAT,
             NAME = N'Chapter12_LogBackup_2012-02-16_12-17-49',
             NORECOVERY ,
             STATS = 5 ;
--Restore the full backup
Chapter 12  Backups and Restores

PAGE-451

RESTORE DATABASE/LOG آرگومان‌های Database/Device و WITH Optionهای متعددی دارد. PARTIAL، FILE/FILEGROUP و KEEP_REPLICATION از گزینه‌های خاص سناریو هستند.

PAGE-452

Backup Set Selection با FILE = n Backup Set خاص روی Media را انتخاب می‌کند. Media Set Optionهایی مثل MEDIANAME/MEDIAPASSWORD تاریخی‌اند یا محدودیت نسخه دارند. RESTORE HEADERONLY/FILELISTONLY برای بازرسی Media قبل از Restore بسیار مفید است.

PAGE-453

Error Optionها مانند CONTINUE_AFTER_ERROR باید فقط در Disaster Scenario آگاهانه استفاده شوند. Miscellaneous Optionها شامل STATS، RESTART و RESTRICTED_USER در برخی Restoreها هستند.

PAGE-454

RESTORE DATABASE Chapter12
FROM DISK = 'D:\Backups\Chapter12_full.bak'
WITH MOVE 'Chapter12' TO 'D:\SQLData\Chapter12.mdf',
     MOVE 'Chapter12_log' TO 'E:\SQLLog\Chapter12.ldf',
     RECOVERY, STATS = 10;

PAGE-455

آماده‌سازی Point-in-Time Restore

کتاب مجموعه Full و Log Backup می‌گیرد و سپس تغییراتی در Data ایجاد می‌کند تا Target Time/LSN معنی‌دار داشته باشد. برای Point-in-Time باید Log Chain بدون Gap باشد.

PAGE-456

Backup Database و Logها طبق Sequence تهیه می‌شوند. هر Log Backup محدوده LSN دارد و Restore Engine اتصال این محدوده‌ها را کنترل می‌کند. Missing Log Backup مانع ادامه Restore می‌شود.

PAGE-457

یافتن LSN

RESTORE HEADERONLY و Metadata Backup می‌توانند FirstLSN/LastLSN/CheckpointLSN را نشان دهند. همچنین fn_dblog در محیط آزمایشی برای مشاهده Log Record و LSN قابل استفاده است.

PAGE-458

Full Backup با NORECOVERY Restore و سپس Log Backupهای قبل از نقطه هدف به‌ترتیب اعمال می‌شوند. آخرین Log با STOPAT یا STOPATMARK/STOPBEFOREMARK متناسب با سناریو اجرا می‌شود.

PAGE-459

RESTORE DATABASE Chapter12 FROM DISK='D:\Backups\full.bak' WITH NORECOVERY;
RESTORE LOG Chapter12 FROM DISK='D:\Backups\log1.trn' WITH NORECOVERY;
RESTORE LOG Chapter12 FROM DISK='D:\Backups\log2.trn'
WITH STOPAT = '2019-01-01T12:34:56', RECOVERY;

PAGE-460

File Restore

برای Demonstration، File/Filegroup خاص Backup و سپس آسیب/حذف شبیه‌سازی می‌شود. Restore File می‌تواند حجم Recovery را کاهش دهد، اما Database/Log Sequence باید سازگار باشد.

PAGE-461

Tail-Log Backup پیش از Restore معمولاً آخرین Log Recordهای قابل دسترسی را حفظ می‌کند. اگر بدون Tail-Log یک Restore قدیمی روی Database فعال انجام شود، تغییرات اخیر ممکن است برای همیشه از دست بروند.

PAGE-462

Page Restore

کتاب با DBCC WRITEPAGE یک Page را در Lab خراب می‌کند و سپس Page Restore را نشان می‌دهد. Page Restore فقط برای Database/Edition/Recovery Modelهای پشتیبانی‌شده و وقتی Scope خرابی محدود است مناسب است.

PAGE-463

Page خراب با DBCC/Query مشخص می‌شود. این تکنیک باید به همراه Log Backupهای لازم اجرا شود تا Page پس از Restore به وضعیت Current Roll-forward شود.

PAGE-464

RESTORE DATABASE Chapter12 PAGE = '1:12345'
FROM DISK = 'D:\Backups\Chapter12_full.bak'
WITH NORECOVERY;
-- سپس Log Backupهای لازم Restore می‌شوند.

msdb.dbo.suspect_pages نیز اطلاعات Page مشکوک را فراهم می‌کند.

PAGE-465

Piecemeal Restore

Piecemeal Restore Filegroupها را مرحله‌ای Online می‌کند. Primary و Read-Write Filegroupهای ضروری ابتدا Restore می‌شوند و Filegroupهای دیگر بعداً. این روش برای VLDB زمان دسترس‌پذیری بخشی از Database را کاهش می‌دهد.

PAGE-466

Sequence با PARTIAL و NORECOVERY آغاز می‌شود، سپس Filegroup Backupها و Log Backupهای لازم اعمال می‌شوند. هر Filegroup فقط زمانی Online می‌شود که Recovery آن تا LSN سازگار تکمیل شده باشد.

PAGE-467

RESTORE DATABASE Chapter12
FILEGROUP='PRIMARY'
FROM DISK='D:\Backups\Chapter12_fg.bak'
WITH PARTIAL, NORECOVERY;
-- ادامه Restore Filegroupها و Logها بر اساس زنجیره Backup

PAGE-468

جمع‌بندی

Backup Strategy باید RPO/RTO، حجم Database، Restore Complexity و Storage Cost را همزمان در نظر بگیرد. Full، Differential، Log، Filegroup و Partial Backup ابزارهای مختلف‌اند؛ اعتبار واقعی Strategy فقط با Restore Test ثابت می‌شود.

PAGE-469

سناریوهای Restore بسیار متنوع‌اند. DBA باید Recovery Runbook مکتوب و تمرین‌شده داشته باشد، Certificate/Keyهای Backup Encryption و TDE را جداگانه محافظت کند، و Backup Integrity را با CHECKSUM/VERIFYONLY و مهم‌تر از آن Restore واقعی آزمایش کند.

مقاله قبلی | بازگشت به فهرست مادر | مقاله بعدی

Restore، Point-in-Time، File/Page و Piecemeal Restore در SQL Server | Pro SQL Server 2019 Administration