Date: Fri, 29 Mar 2024 04:56:00 +0000 (GMT) Message-ID: <1416584936.559.1711688160663@ip-172-31-38-4.eu-west-1.compute.internal> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_558_940587602.1711688160654" ------=_Part_558_940587602.1711688160654 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
Use the RESTORE
command with the SQL Back=
up Pro -SQL
parameter to restore a SQL Backup backup=
using the command line or extended stored procedure.
RESTORE
command.RESTORE
command.RESTORE
&nb=
sp;command.-SQL
) must be delimited by single quotes. Therefore,=
wherever a single quote is used for the arguments below, for the extended =
stored procedure you must use two single quotes so that SQ=
L Server does not interpret it as a string delimiter. See Using the extended stored procedure for more information.
The following arguments are only available with SQL Server 2005 and late= r:
CHECKSUM
/ NO_CHECKSUM
CONTINUE_AFTER_ERROR
/ STOP_ON_ERROR
PARTIAL
The KEEP_CDC
argument is only available with SQL =
Server 2008 and later.
RESTORE DATABASE {&n=
bsp;database_name }
[ FROM
{
{DISK} =3D { 'physical_backup_device_name' | 'file_se=
arch_pattern' } [ ,...n ]<=
/a>
[
&n=
bsp; [ LATEST_FULL | LATEST_DIFF | LATEST_ALL ]
|
&n=
bsp; SOURCE =3D 'source_database=
_name' { LATEST_FULL | LATEST_DIFF | LATEST_ALL =
}
]
|
{BACKUPHISTORY} [ =3D=
'history_database_name'&nb=
sp;]
{ =
LATEST_FULL | LATEST_DIFF | LATEST_ALL }
}
]
[ WITH
[ [ , ] CHECKDB =3D { [NO_INFOMSGS] , [ALL_ERRO=
RMSGS] , [TABLOCK] , [PHYSICAL_ONLY] , [DATA_PURITY] , [EXTENDED_LOGICAL_CH=
ECKS] , [VERBOSE] }
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]<=
br class=3D"atl-forced-newline">
[ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERR=
OR } ]
[ [ , ] DISCONNECT_EXISTING&nbs=
p;]
[ [ , ] DISKRETRYCOUNT =3D { n }&nbs=
p;]
[ [ , ] DISKRETRYINTERVAL =3D { n }=
a> ]
[ [ , ] DROPDB ]
[ [ , ] DROPDBIFSUCCESSFUL =
]
[ [ , ] ERASEFILES | ERASEFILES_PRIMARY&nb=
sp;=3D { days | hours{h} =
| except latest{b} } ]
[ [ , ] ERASEFILES_REMOTE |&=
nbsp;ERASEFILES_=
SECONDARY =3D { days | hours{
[ [ , ] FILEOPTIONS =3D { 1 | 2 | 3 }&n=
bsp;]
[ [ , ] KEEP_CDC ]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] LOG_ONERROR ]
[ [ , ] LOG_ONERRORONLY ]
[ [ , ] LOGTO =3D { 'ta=
rget_folder_name' | 'f=
ile_name' } ] [ ,...n ]
[ [ , ] MAILTO =3D { '=
recipients' } ]
[ [ , ] MAILTO_NOLOG ]
[ [ , ] MAILTO_ONERROR =3D {
[ [ , ] MAILTO_ONERRORONLY =3D {&nbs=
p;'recipients' } ]=
[ [ , ] MAXTRANSFERSIZE =3D { 65536 | 1=
31072 | ... | 1048576 } ]
[ [ , ] MOVE DATAFILES TO {
[ [ , ] MOVE FILESTREAMS TO { 'operating_system_folder' }=
a> ]
[ [ , ] MOVE FULLTEXTCATALOGS TO=
{ 'operating_system_folder' =
strong>} ]
[ [ , ] MOVE LOGFILES TO { =
'operating_system_folder' }&nbs=
p;]
[ [ , ] MOVE 'logical_fi=
le_name' TO 'operating=
_system_file_name' ] [ ,..=
.n ]
[ [ , ] MOVETO =3D { '=
target_folder_name' } ]
[ [ , ] NOLOG ]
[ [ , ] { NORECOVERY | RECOVERY | STANDBY =3D '<=
em>standby_file_name' } ]
[ [ , ] ORPHAN_CHECK ]
[ [ , ] PASSWORD =3D { 'password' | 'FILE:=
file_path'} =
a> ]
[ [ , ] REPLACE ]
[ [ , ] RESTRICTED_USER ]
[ [ , ] SINGLERESULTSET ]
[ [ , ] THREADPRIORITY =3D { 0 | 1 | 2 |=
3 | 4 | 5 | 6 } ]
]
RESTORE DATABASE {&n=
bsp;database_name }
[ F=
ILE =3D { 'logical_file_name' } | FILEGROUP =3D { 'logical_filegroup_na=
me' } | PAGE =3D { 'fi=
le:page' } ] [ ,...n ]
FROM DISK =3D {&n=
bsp;'physical_backup_device_name' | 'file_search_pattern' } [ ,...n ]
[ WITH
PARTIAL
[ [ , ] CHECKDB =3D { [NO_INFOMSGS] , [ALL_ERRO=
RMSGS] , [TABLOCK] , [PHYSICAL_ONLY] , [DATA_PURITY] , [EXTENDED_LOGICAL_CH=
ECKS] , [VERBOSE] } ]
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] { CONTINUE_AFTER_ERROR | S=
TOP_ON_ERROR } ]
[ [ , ] DISCONNECT_EXISTING&nbs=
p;]
[ [ , ] DISKRETRYCOUNT =3D { n }&nbs=
p;]
[ [ , ] DISKRETRYINTERVAL =3D { n }=
a> ]
[ [ , ] DROPDB ]
[ [ , ] DROPDBIFSUCCESSFUL =
]
[ [ , ] ERASEFILES | ERASEFILES_PRIMARY=3D=
{ days | hours{h} | =
;except latest{b} } ]
[ [ , ] ERASEFILES_REMOTE |&=
nbsp;ERASEFILES_=
SECONDARY=3D { days | hours{h<=
/strong>} | except latest{b} } ]
[ [ , ] FILEOPTIONS =3D { 1 | 2 | 3 }&n=
bsp;]
[ [ , ] LOG_ONERROR ]
[ [ , ] LOG_ONERRORONLY ]
[ [ , ] LOGTO =3D { 'ta=
rget_folder_name' | 'f=
ile_name' } ] [ ,...n ]
[ [ , ] MAILTO =3D { '=
recipients' } ]
[ [ , ] MAILTO_NOLOG ]
[ [ , ] MAILTO_ONERROR =3D {
[ [ , ] MAILTO_ONERRORONLY =3D {&nbs=
p;'recipients' } ]=
[ [ , ] MOVE DATAFILES TO {
[ [ , ] MOVE FILESTREAMS TO { 'operating_system_folder' }=
a> ]
[ [ , ] MOVE FULLTEXTCATALOGS TO=
{ 'operating_system_folder' =
strong>} ]
[ [ , ] MOVE LOGFILES TO { =
'operating_system_folder' }&nbs=
p;]
[ [ , ] MOVE 'logical_fi=
le_name' TO 'operating=
_system_file_name' ] [ ,..=
.n ]
[ [ , ] MOVETO =3D { '=
target_folder_name' } ]
[ [ , ] NOLOG ]
[ [ , ] NORECOVERY ]
[ [ , ] ORPHAN_CHECK ]
[ [ , ] PASSWORD =3D { 'password' | 'FILE:file_path<=
/em>'} ]<=
br class=3D"atl-forced-newline">
[ [ , ] REPLACE ]
[ [ , ] RESTRICTED_USER ]
[ [ , ] SINGLERESULTSET ]
[ [ , ] THREADPRIORITY =3D { 0 | 1 | 2 | 3 | 4=
| 5 | 6 } ]
]
RESTORE LOG { da=
tabase_name }
[ FILE =3D { 'logical_file_nam=
e' } | FILEGROUP =3D { ''file:page' } ] [ ,...n ]
[ FROM { DISK } <=
strong>=3D { 'physical_backup_devic=
e_name' | 'file_search=
_pattern' } ] [ ,...n ]
[ WITH
[ [ , ] CHECKDB =3D { [NO_INFOMSGS] , [ALL_ERRO=
RMSGS] , [TABLOCK] , [PHYSICAL_ONLY] , [DATA_PURITY] , [EXTENDED_LOGICAL_CH=
ECKS] , [VERBOSE] } ]
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]<=
br class=3D"atl-forced-newline">
[ [ , ] { CONTINUE_AFTER_ERROR | S=
TOP_ON_ERROR } ]
[ [ , ] DELAY =3D {seconds} ]
[ [ , ] DISCONNECT_EXISTING&nbs=
p;]
[ [ , ] DISKRETRYCOUNT =3D { n }&nbs=
p;]
[ [ , ] DISKRETRYINTERVAL =3D { n }=
a> ]
[ [ , ] DROPDB ]
[ [ , ] DROPDBIFSUCCESSFUL =
]
[ [ , ] ERASEFILES | ERASEFILES_PRIMARY&nb=
sp;=3D { days | hours{h} =
| except latest{b} } ]
[ [ , ] ERASEFILES_REMOTE |&=
nbsp;ERASEFILES_=
SECONDARY =3D { days | hours{
[ [ , ] FILEOPTIONS =3D { 1 | 2 | 3 }&n=
bsp;]
[ [ , ] KEEP_CDC ]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] LOG_ONERROR ]
[ [ , ] LOG_ONERRORONLY ]
[ [ , ] LOGTO =3D { 'ta=
rget_folder_name' | 'f=
ile_name' } ] [ ,...n ]
[ [ , ] MAILTO =3D { '=
recipients' } ]
[ [ , ] MAILTO_NOLOG ]
[ [ , ] MAILTO_ONERROR =3D { 'recipients' } ]
[ [ , ] MAILTO_ONERRORONLY =3D {&nbs=
p;'recipients' } ]=
[ [ , ] MAXTRANSFERSIZE =3D { 65536 | 1=
31072 | ... | 1048576 } ]
[ [ , ] MOVE 'logical_fi=
le_name' TO 'operating=
_system_file_name' ] [ ,..=
.n ]
[ [ , ] MOVETO =3D { '=
target_folder_name' } ]
[ [ , ] NOLOG ]
[ [ , ] { NORECOVERY | RECOVERY | STANDBY =3D '<=
em>standby_file_name' } ]
[ [ , ] ORPHAN_CHECK ]
[ [ , ] PASSWORD =3D { 'password' | 'FILE:file_path<=
/em>'} ]<=
br class=3D"atl-forced-newline">
[ [ , ] REPLACE ]
[ [ , ] RESTRICTED_USER ]
[ [ , ] SINGLERESULTSET ]
[ [ , ] { STOPAT =3D =
;{ 'date_time' | =
@date_time_var } ]
| STOPATMARK =3D { =
'mark_name' | 'lsn:lsn_number' }
[ =
AFTER 'datetime' ]
| STOPBEFOREMARK =3D { =
'mark_name' | =
'lsn:lsn_number' }
[ =
AFTER 'datetime']
} ]
[ [ , ] THREADPRIORITY =3D { 0 | 1 | 2 |=
3 | 4 | 5 | 6 } ]
]
RESTORE FILELISTONLY
[ FROM { DISK } =3D {&=
nbsp;'physical_backup_device_name' } ]
[ WITH
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]<=
br class=3D"atl-forced-newline">
[ [ , ] MAXTRANSFERSIZE =3D { 65536 | 1=
31072 | ... | 1048576 } ]
[ [ , ] PASSWORD =3D { 'password' | 'FILE:file_path<=
/em>'} ]<=
br class=3D"atl-forced-newline">
[ [ , ] SINGLERESULTSET ]
]
RESTORE HEADERONLY
[ FROM { DISK } <=
strong>=3D { 'physical_backup_devic=
e_name' } ]
[ WITH
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]<=
br class=3D"atl-forced-newline">
[ [ , ] MAXTRANSFERSIZE =3D { 65536 | 1=
31072 | ... | 1048576 } ]
[ [ , ] PASSWORD =3D { 'password' | 'FILE:file_path<=
/em>'} ]<=
br class=3D"atl-forced-newline">
[ [ , ] SINGLERESULTSET ]
]
We recommend you use the SQL Backup Pro command RESTORE SQBHEADERONLY to= retrieve the header information for SQL Backup backup files, because = it is much quicker than using the native command RESTORE HEADERONLY. For de= tails, see The RESTORE SQBHEADERONLY command.
RESTORE VERIFYONLY
[ FROM { DISK } =3D {&=
nbsp;'physical_backup_device_name' } ] [ ,...n ]
[ WITH
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]<=
br class=3D"atl-forced-newline">
[ [ , ] MAILTO =3D { '=
recipients' } ]
[ [ , ] MAILTO_NOLOG ]
[ [ , ] MAILTO_ONERROR =3D {
[ [ , ] MAILTO_ONERRORONLY =3D {&nbs=
p;'recipients' } ]=
[ [ , ] MAXTRANSFERSIZE =3D { 65536 | 1=
31072 | ... | 1048576 } ]
[ [ , ] PASSWORD =3D { 'password' | 'FILE:file_path<=
/em>'} ]<=
br class=3D"atl-forced-newline">
[ [ , ] SINGLERESULTSET ]
]
It is only possible to restore one database at a time. The database name= must be enclosed in square brackets [ ] if it includes reserved words or s= paces; if the database name does not include reserved words or spaces, squa= re brackets are optional. For example:
"RESTORE= DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs\FULL_20120229.sqb'"
Specifies the files, filegroups or pages of the database that are to be = restored. There is no limit on the number of files, filegroups or pages tha= t can be restored, provided thay all belong to the same database. Identify = files or filegroups using logical names. For example:
"RESTORE= DATABASE pubs FILE =3D 'SalesF1', FILE =3D 'SalesF2' FROM DISK =3D 'C:\Bac= kups\pubs\salesFiles.sqb'"
If the database is using the simple recovery model, the specified files =
or filegroups must be read-only unless WITH PARTIAL
=
is specified.
Individual pages of read/write filegroups can be restored, provided the =
database is using the full or bulk-logged recovery model. Identify the page=
s to be restored in the format PAGE =3D 'fileID:pageID'
. To restore multiple pages, use the format PAGE =3D 'fileID:p=
ageID, fileID:pageID'
.
For more information, refer to your SQL Server documentation.
To restore transaction log backups, the database must be in an unrecover=
ed or standby state (see NORECOVERY
and STANDBY
below). The database name mu=
st be enclosed in square brackets [ ] if it includes reserved words or spac=
es; if the database name does not include reserved words or spaces, square =
brackets are optional. For example:
"RESTORE= LOG [pubs] FROM DISK =3D 'C:\Backups\pubs\LOG__20120229_111500.sqb'"
To restore log backups using successive restore commands, include <=
code>WITH NORECOVERY in each RESTORE LOG
 =
;command. For example:
"RESTORE= LOG [pubs] FROM DISK =3D 'C:\Backups\pubs\LOG_20120229_111500.sqb' WITH NO= RECOVERY" "RESTORE LOG [pubs] FROM DISK =3D 'C:\Backups\pubs\LOG_20120229_113000.sqb'= WITH NORECOVERY"
Restore the last log WITH RECOVERY
to recover the=
database to a usable state. For information on restoring multiple log back=
ups in one RESTORE
command, see the FROM DISK
argument below.
Specifies the files, filegroups or pages of the database that are to be = restored. There is no limit on the number of files, filegroups or pages tha= t can be restored, provided thay all belong to the same database. Identify = files or filegroups using logical names. For example:
"RESTORE= LOG pubs FILE =3D 'SalesF1', FILE =3D SalesF2' FROM DISK =3D 'C:\Backups\p= ubs\SalesFiles.sqb'"
If the database is using the simple recovery model, the specified files =
or filegroups must be read-only unless WITH PARTIAL
=
is specified.
Individual pages of read/write filegroups can be restored, provided the =
database is using the full or bulk-logged recovery model. Identify the page=
s to be restored in the format PAGE =3D 'fileID:pageID'
. To restore multiple pages, use the format PAGE =3D 'fileID:p=
ageID, fileID:pageID'
.
For more information, refer to your SQL Server documentation.
You can specify up to 32 DISK values. This is useful when you have split= a backup across multiple files. You can also enter a 'file search pattern'= by specifying wildcard characters in the physical backup device name. All = files that match the wildcard characters must belong to the same backup set= . For example, instead of:
"RESTORE= DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs_01.sqb', DISK =3D 'C:\Backu= ps\pubs_02.sqb', DISK =3D 'C:\Backups\pubs_03.sqb', DISK =3D 'C:\Backups\pu= bs_04.sqb', DISK =3D 'C:\Backups\pubs_05.sqb'"
you can enter:
"RESTORE= DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs_*.sqb'"
You can also use the file search pattern to restore multiple transaction=
log backups. The database that the logs are restored to must be in an unre=
covered state, that is, it must have been restored using the NOR=
ECOVERY
or STANDBY
option. For example:<=
/p>
"RESTORE= LOG [pubs] FROM DISK =3D 'C:\Backups\Pubs\Logs*.*'"
SQL Backup Pro ensures that the files are restored in the correct sequen=
ce. To specify a number of folders, use the DISK
com=
mand repeatedly. For example:
"RESTORE= LOG [pubs] FROM DISK =3D 'C:\Backups\Pubs\Logs*.*', DISK =3D 'E:\OtherBack= ups\Pubs\Logs*.*'"
The backup files that match the wildcard characters must belong to the s= ame backup set. Any encrypted files must use the same password.
FROM DISK
argum=
ent for RESTORE FILELISTONLY
, RESTORE HEADERONLY
,=
and RESTORE VERIFYONLY
commands.
You can use the optional LATEST_FULL
keyword to s=
elect the most recent full backup of the destination database that matches =
the DISK
values you specify. The DISK values you specify must contain the '*' wildcard. For example:
"RESTORE= DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs*.sqb' LATEST_FULL"
will find backup files of the destination database in the C:\Ba= ckups directory with a file name matching pubs*.sqb= , and will then restore the latest full backup. You can specify multiple di= sk values; this is useful if you have split a backup across multiple files.= The files that match the file search pattern must belong to the same backu= p set.
Note that you cannot use LATEST_FULL
when restori=
ng a file or filegroup backup.
You can use the optional LATEST_DIFF
keyword to s=
elect the most recent differential backup of the destination database that =
matches the disk values you specify. You can specify multiple locations; th=
is is useful if you have split the backup across multiple files. The <=
code>DISK values you specify must contain the '*' wildcard. For=
example:
"RESTORE= DATABASE [pubs] FROM DISK =3D 'C:\Backups\sales*.sqb', DISK =3D 'D:\Backup= s\sales*.sqb' LATEST_DIFF"
will find all differential backup files of the destination database in t= he C:\Backups and D:\Backups director= ies with a file name matching sales*.sqb, and will then resto= re the latest differential backup. The files that match the file search pat= tern must belong to the same backup set.
Note that if you specify LATEST_DIFF
, you must restore=
to a database that has already had the most recent full backup applied. Yo=
u cannot use LATEST_DIFF
when restoring a file or fi=
legroup backup.
If you specify the will find backup files of the destination database in the C:\Ba=
ckups directory with a file name matching pubs*.sqb=
, and will then restore the latest full backup, the latest differential bac=
kup (if applicable) and the latest log backups (if applicable). All the fil=
es that match the file search pattern must belong to the same backup set.=
p>
You can specify multiple disk values. This is useful if you store differ=
ent backup types in separate locations or if you have split backups across =
multiple files. For example: You can restore the latest backup files taken from a database other than=
the destination database by including will restore the latest full backup of the Sales_Prod =
;database (followed by subsequent differential and transaction log backups,=
if applicable), to the Sales_Test database. Note that you can only use Use LATEST_ALL
keyword, the most r=
ecent full backup of the destination database will be restored, followed by=
the most recent differential backup (if one exists), and then finally by t=
he most recent transaction log backups (if any exist). The DISK<=
/code> values you specify must contain the '*' wildcard. For example:<=
/p>
keyword. For example:"RESTORE=
DATABASE pubs FROM DISK =3D 'C:\Backups\pubs*.sqb' LATEST_ALL"
"RESTORE=
DATABASE [pubs] FROM DISK =3D 'C:\Backups\FULL_pubs*.sqb', DISK =3D 'D:\Ba=
ckups\DIFF_pubs*.sqb', DISK =3D 'E:\Backups\LOG_pubs*.sqb' LATEST_ALL"
LATEST_ALL
when restoring a file or filegr=
oup backup.
SOURCE
SOURCE =3D 'source_databa=
se_name'
. For example:"RESTORE=
DATABASE [Sales_Test] FROM DISK =3D 'D:\backups*.sqb' SOURCE =3D 'Sales_Pr=
od' LATEST_ALL"
SOURCE
if L=
ATEST_FULL
, LATEST_DIFF
or LATEST_=
ALL
is included in the RESTORE
statement=
. You cannot use SOURCE
when restoring a file or fil=
egroup backup.FROM BACKUPHISTORY argument
FROM BACKUPHISTORY
when you want to restore t=
he latest full, latest differential, or all the latest backups (including t=
ransaction log backups) for a particular database, without having to list t=
he individual backup file locations. SQL Backup Pro searches its own backup=
history to determine which backup files to restore. You must also specify =
the LATEST_FULL
, LATEST_DIFF
, or <=
code>LATEST_ALL
"RESTORE= DATABASE [sales] FROM BACKUPHISTORY LATEST_FULL WITH RECOVERY, REPLACE"
will search the backup history for the 'sales' database, and will then r= estore the latest full backup over the current sales dat= abase.
To search the backup history of a different database, you can specify th=
is as part of the BACKUPHISTORY
parameter. For examp=
le:
"RESTORE= DATABASE [sales_dev] FROM BACKUPHISTORY =3D 'sales' LATEST_FULL WITH RECOV= ERY, REPLACE"
will search the backup history for the 'sales' database, and will then r= estore the latest full backup to the sales_dev database.=
Runs a database integrity check (DBCC CHECKDB) on the database once the =
restore is complete. This checks the logical and physical integrity of all =
the objects in the specified database. CHECKDB
canno=
t be used in conjunction with NORECOVERY
. For more inform=
ation, refer to your SQL Server documentation.
NO_INFOMSGS | Suppresses informational messages. Informational=
messages are only returned if other CHECKDB options=
are included in the command. |
ALL_ERRORMSGS | Displays all reported errors and includes them i= n the log file. |
TABLOCK | Obtains a lock on the database while the integri= ty check is performed. This includes a temporary exclusive lock which will = prevent concurrent access to the database. |
PHYSICAL_ONLY | Limits the database integrity check to the physi=
cal structure of the database. The contents of the check depends on the ver=
sion of SQL Server you are using; refer to your SQL Server documentation fo=
r more information. This option cannot be included with EXTENDED_LOGI=
CAL_CHECKS or DATA_PURITY . |
DATA_PURITY | Checks database columns for invalid or out-of-ra=
nge values. Column values are checked by default in databases created in SQ=
L Server 2005 or later. This option cannot be included with PHYSICAL_=
ONLY . |
EXTENDED_LOGICAL_CHECKS | Only available with SQL Server 2008 or later. Pe=
rforms logical checks on an indexed view, XML indexes, and spatial indexes,=
where present. Running this check can have a considerable effect on perfor=
mance. This option cannot be included with PHYSICAL_ONLY . |
VERBOSE | Displays all reported error and informational me= ssages and includes them in the log file. |
By default, if the backup process included WITH CHECKSUM the backup checksum and any page checksums are validated on restore=
. If the backup does not include a backup checksum, any page checksums will=
not be validated. Specify
NO_CHECKSUM
to disable de=
fault validation of checksums. If you specify CHECKSUM
, t=
he backup checksum and any page checksums will be validated as by default, =
but if the backup does not include a backup checksum, an error is returned.=
For more information, refer to your SQL Server documentation.
CONTINUE_AFTER_ERROR
specifies that the RES=
TORE
process should continue after an error is encountered, res=
toring what it can. This is the default behavior for RESTORE VER=
IFYONLY
(see VERIFY in The BACKUP command). The&n=
bsp;RESTORE VERIFYONLY
process then reports all errors it=
has encountered.
STOP_ON_ERROR
specifies that the RESTORE process should stop if an error is encountered. This is the defau=
lt behavior for
RESTORE
.
For more information, refer to your SQL Server documentation.
DELAY is only available in SQL Backup Pro 7.4 and later.
Specifies a minimum age, in seconds, for transaction log backups. Only b= ackups older than the specified age will be restored. This is useful i= f you are log shipping to maintain a standby database and want to delay res= tores to that database, for example, to help protect against corrupt or err= oneous data.
The following example restores all transaction log backups in C= :\Backups\pubs\LOG that are at least 30 minutes old to the <= em>pubs database. The backups are restored in the correct order.<= /p>
"RESTOR= E LOG [pubs] FROM DISK =3D 'C:\Backups\pubs\LOG\*.sqb' WITH DELAY =3D = 1800 "
Kills any existing connections to the database before starting the resto= re. Restoring to an existing database will fail if there are any connection= s to the database.
In combination with DISKRETRYINTERVAL
, this argument c=
ontrols network resilience behavior.
DISKRETRYCOUNT
specifies the maximum number of times t=
o retry a failed data-transfer operation (reading or moving a backup file).=
If you omit this keyword, the default value of 10 is used. If you specify =
a value for DISKRETRYCOUNT
, you should also specify a val=
ue for DISKRETRYINTERVAL
.
In combination with DISKRETRYCOUNT
, this argument cont=
rols network resilience behavior.
Drops the database after the restore process (and database integrity che=
ck if used in conjunction with Drops the database if the restore completed successfully. When used in c=
onjunction with Specifies the number of existing SQL Backup backups to be deleted from t=
he You can choose to delete SQL Backup backups based on: Files are deleted only if the following details match the details of the=
database being backed up: If SQL Backup cannot list the contents of the folder that contains the f=
iles to be deleted, it cannot delete the files. Ensure the SQL Backup Agent=
service startup account (or, if you are using the command line, the user a=
ccount from which you are running SQLBackupC.exe) has permiss=
ions to list the folder contents. The following example restores a transaction log backup to the =
pubs database and leaves it in a non-operational state, then move=
s the backup to C:\processed_logs and deletes all t=
ransaction log backups of pubs other than the latest 10 =
from that folder: If ERASEFILES_PRIMARY is only available in SQL Backup Pro 7.4 and later. To manage deletion of backup files, use either: Do not use Manages deletion of existing SQL Backup backups from the You can choose to delete SQL Backup files based on: Files are deleted only if the following details match the details of the=
database being backed up: The following example restores a full backup to the pubs&n=
bsp;database, then deletes all full backups of the pubs =
database older than 5 days from C:\Backups\pubs. To delete existing backups from the Manages deletion of existing SQL Backup backups from remot=
e You can choose to delete SQL Backup files based on: Files are deleted only if the following details match the details of the=
database being backed up: If SQL Backup cannot list the contents of the folder that contains the f=
iles to be deleted, it cannot delete the files. Ensure the SQL Backup Agent=
service startup account (or, if you are using the command line, the user a=
ccount from which you are running SQLBackupC.exe) has permiss=
ions to list the folder contents. The following example restores a transaction log backup to the =
pubs database and leaves it in a non-operational state, then=
moves the backup to \\Server01\processed_logs and delet=
es all backups older than 6 hours from that folder: To delete files from local ERASEFILES_SECONDARY is only available in SQL Backup Pro 7.4 and later.<=
/p>
To manage deletion of backup files, use either: Do not use Manages deletion of existing SQL Backup backups from the You can choose to delete SQL Backup files based on: Files are deleted only if the following details match the details of the=
database being backed up: The following example restores a transaction log backup to the =
pubs database and leaves it in a non-operational state, then=
moves the backup to C:\processed_logs and deletes all t=
ransaction log backups of pubs over 12 hours old from th=
at folder. The following example restores a full backup to the pubs&n=
bsp;database and deletes all full backups of pubs other =
than the latest 5 from C:\Backups\pubs, then moves the backup=
to E:\Archive\pubs and deletes all full backups of the&=
nbsp;pubs database older than 60 days from that folder.<=
/p>
Use in conjunction with Valid values are 1, 2, and&n=
bsp;3. You must also set the age of the files to delete using To delete any existing files in the Specifies that Change Data Capture settings are to be retained when a da=
tabase or log is restored to another server. This option cannot be included with This option is for use when log shipping is used in conjunction with rep=
lication. Specifies that replication settings are to be retained when a dat=
abase or log is restored to a standby server. This option cannot be included with Specifies that a log file should only be created if SQL Backup Pro encou=
nters an error during the restore process, or the restore completes success=
fully but with warnings. Use this option if you want to restrict the number=
of log files created by your restore processes, but maintain log informati=
on whenever warnings or errors occur. This argument controls the creation o=
f log files on disk only; emailed log files are not affected. (See the =
; Specifies that a log file should only be created if SQL Backup Pro encou=
nters an error during the restore process. Use this option if you want to r=
estrict the number of log files created by your restore processes, but main=
tain log information whenever errors occur. This argument controls the crea=
tion of log files on disk only; emailed log files are not affected. (See th=
e Specifies that a copy of the log file is to be saved. By default, the primary log file is created in the folder %Prog=
ramData%\Red Gate\SQL Backup\Log (Windows Vista, Windows 2008 and=
later) or %ALLUSERSPROFILE%\Application Data\Red Gate\SQL Backup\=
Log (Windows XP and Windows 2003); you can change this location i=
n your file manageme=
nt options. To create a copy with the same name as the primary log file, specify the=
folder. For example: To create a copy with a different name from the primary log file, specif=
y the folder and file name. For example: To copy the log file to more than one location, use multiple Specifies that the outcome of the restore operation is emailed to one or=
more users; the email includes the contents of the log file. SQL Backup Pr=
o uses the settings specified in your email settings to send the email. To specify multiple rec=
ipients, separate the email addresses with a semi-colon (;=
). For example: If you have not defined em=
ail settings, the email will not be sent and a warning will be reported=
. Specifies that SQL Backup Pro should not include the contents of the log=
file in the email. An email will still be sent to notify the specified rec=
ipients of success and/or failure, depending on which Specifies that that the outcome of the restore operation is emailed to o=
ne or more users if SQL Backup Pro encounters an error during the restore p=
rocess or the restore process completes successfully but with warnings. The=
email includes the contents of the log file. SQL Backup Pro uses the setti=
ngs specified in your email s=
ettings to send the email. To specify multiple recipients, separat=
e the email addresses with a semi-colon (;). For example:<=
/p>
If you have not defined em=
ail settings, the email will not be sent and a warning will be reported=
. Specifies that that the outcome of the restore operation is emailed to o=
ne or more users if SQL Backup Pro encounters an error during the restore p=
rocess. The email includes the contents of the log file. SQL Backup Pro use=
s the settings specified in your email settings to send the email. To specify multiple recipien=
ts, separate the email addresses with a semi-colon (;). Fo=
r example: If you have not defined em=
ail settings, the email will not be sent and a warning will be reported=
. Specifies the maximum size of each block of memory to be used when SQL B=
ackup Pro restores backup data. You may want to specify this argument if a =
SQL Server reports that it has insufficient memory to service requests from=
SQL Backup Pro. Valid values are integers in multiples of 65536, up to a maximum value o=
f 1048576. For example: If not specified, defaults to 1048576. However, if you have created the =
following DWORD registry key, SQL Backup Pro uses the defined value as the =
default value: HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal=
\<instance name>\MAXTRANSFERSIZE Specifies the data files should be restored to the specified location us=
ing the operating system file names defined in the backup file. The specifi=
ed location must exist before the restore, otherwise the restore will fail.=
If this option is not included, the data files will be restored to the def=
ault locations. This option can be used in conjunction with In this example, TestDB_Primary2 could have been rest=
ored to a new file name as well as to a different folder. This option can also be used in conjunction with Only available with SQL Server 2008 and later. Specifies that filestream=
s should be restored to the specified location. The specified location must=
exist before the restore, otherwise the restore will fail. If the database=
contains multiple filestreams, each filestream will be restored to a separ=
ate subfolder. If this argument is not included, the filestreams will be re=
stored to the default locations. If the database includes multiple filestreams, this option can be used i=
n conjunction with This option can be used in conjunction with Only available with SQL Server 2005. Specifies that full text catalogs s=
hould be restored to the specified location. If the database contains multi=
ple full text catalogs, each full text catalog will be restored to a separa=
te subfolder. If this option is not included the full text catalogs will be=
restored to the default locations. If the database includes multiple full text catalogs, this option can be=
used in conjunction with This option can be used in conjunction with Specifies the log files should be restored to a new location with the op=
erating system file names specified in the backup file. The specified locat=
ion must exist before the restore process, otherwise the restore will fail.=
If this option is not included, the log files will be restored to the defa=
ult locations. This option can be used in conjunction with In this example, TestDB_Log2 could have been restored=
to a new file name as well as to a different folder. This option can also be used in conjunction with Specifies that the data file, log file, full text catalog (SQL Server 20=
05 only) or filestream data (SQL Server 2008 or later) identified by the lo=
gical file name should be restored to the physical location specified. The =
location must exist before the Specifies that the backup files should be moved to another folder when t=
he restore process completes. If the folder you specify does not exist, it =
will be created. You must ensure that you have permission to delete files from the origin=
al folder, and to write to the MOVETO folder. You can also use tags with the Prevents a log file from being created for the restore process, even if =
errors or warnings are generated. You may want to use this option if you ar=
e concerned about generating a large number of log files, and are certain t=
hat you will not need to review the details of errors or warnings (for exam=
ple, because it's possible to run the process again without needing to know=
why it failed). This argument controls the creation of log files on disk o=
nly; emailed log files are not affected. (See the Specifies that incomplete transactions are not to be rolled back on rest=
ore. The database cannot be used but differential backups and transaction l=
og backups can be restored. For more information, refer to your SQL Server =
documentation. Specifies that once the restore has completed, the database should be ch=
ecked for orphaned users. Database user names are considered to be orphaned=
if they do not have a corresponding login defined on the SQL Server instan=
ce. Orphaned users are often created when you restore a database backup to =
a different SQL Server instance. Note that If orphaned users are detected, warning 472 is generated and each orphan=
ed user is listed in the SQL Backup log file along with the associated SID.=
Specifies a partial restore of a database. The primary filegroup is rest=
ored, together with any specified secondary filegroups. See the For more information, refer to your SQL Server documentation. Specifies the password to be used with encrypted backup files. You cannot use the encrypted form of the password. This is to prevent un=
authorized users from restoring backups if they have access to the encrypte=
d password from the backup script. SQL Backup version 3 allowed the use of encrypted passwords; these will =
no longer work. You must specify the password in unencrypted form: Storing your password in a plain text =
file (only available in SQL Backup 7.5 and later) If you don't want the password to be stored in your SQL Agent jobs, you =
can use a password stored in a plain text file instead. This means access t=
o the password can be restricted using Windows file permissions. To do this, specify the file path and name after the PASSWORD keyword in=
stead of the password itself. SQL Backup will read only the first line of text in the file (up to the =
first line return), and ignore everything after. Specifies that incomplete transactions are to be rolled back. Recovery i=
s completed and the database is in a usable state. Further differential bac=
kups and transaction log backups cannot be restored. If no recovery completion state is specified, Specifies that the database should be restored, even if another database=
of that name already exists. The existing database will be deleted. <=
code>REPLACE For more information, refer to your SQL Server documentation. Specifies that access to the restored database is to be limited to membe=
rs of the db_owner, dbcreator or sysadmin roles. Return the database to mul=
ti-user or single-user mode using your SQL Server application. For more inf=
ormation, refer to your SQL Server documentation. Specifies that the results returned by the RESTORE command should be lim=
ited to just one result set. This may be useful if you want to manipulate r=
esults using a Transact-SQL script. Such scripts can only manipulate result=
s when a single result set is returned. The Specifies a standby file that allows the recovery effects to be undone. =
The Refer to your SQL Server documentation for more information about the&nb=
sp; Is a standby file used to keep a "copy-on-write" pre-image for pages mod=
ified during the undo pass of a When used with the Specifies a point in time to which a transaction log backup should be re=
stored. The database will be recovered up to the last transaction commit th=
at occurred at or before the specified time. When restoring to a point in t=
ime, include this option in each For more information, refer to your SQL Server documentation. Specifies the point to which a transaction log backup should be restored=
, using either the log sequence number or a marked transaction. The databas=
e will be recovered up to and including the log record that contains the sp=
ecified LSN or the marked transaction. For more information, refer to your SQL Server documentation. Specifies the point to which a transaction log backup should be restored=
, using either the log sequence number or a marked transaction. The databas=
e will be recovered up to but excluding the log record that contains the sp=
ecified LSN or the marked transaction. For more information, refer to your SQL Server documentation. Sets the SQL Backup Pro thread priority when the backup or restore proce=
ss is run. Valid values are 0 to 6<=
/strong>, and correspond to the following priorities: If this value is not specified, normal priority is used. This example restores a full backup of the pubs database from a single f=
ile. This example restores a full backup of the pubs datab=
ase from two files. This example restores a full backup of the pubs database and restores it=
to a new database called pubs02. It also renames the databas=
e data and log files and moves them to a new location. This example restores the most recent full backup of the pubs=
em> database that matches the file name pattern and moves the database=
files to different locations. The specified locations must already exist.<=
/p>
This example searches multiple disk locations for full backups of the&nb=
sp;pubs database that match the file name pattern and restore=
s the latest full backup. This example searches multiple disk locations for full, differential and=
transaction log backups of the pubs database that match=
the file name pattern, and restores the most recent full backup, followed =
by the most recent differential backup and the most recent transaction log =
backups. This example restores the most recent full backup of the pubs=
em> database, followed by the most recent differential backup and the =
most recent transaction log backups available from C:\Backups to a new data=
base called pubs02 and checks for orphaned users. This example restores an encrypted backup of the pubs =
;database, specifying the password MyPassword. This example restores an encrypted backup of the pubs =
;database using the password stored in the password.txt =
file. This example restores a full backup of the pubs datab=
ase, specifying that the database is to be left in an unrecovered state so =
that differential and transaction log backups can be restored to it. This example restores a full backup of the pubs datab=
ase, specifying that the database should be left in an unrecovered, read-on=
ly state so that its data can be viewed and differential and transaction lo=
g backups can be restored to it. The location of the standby file is specif=
ied.DISKRETRYINTERVAL
specifies the time interval between =
retries, in seconds, following a failed data-transfer operation (reading or=
moving a backup file). If you omit this keyword, the default value of 30 s=
econds is used. If you specify a value for DISKRETRYINTERVAL, you should also specify a value for
DISKRETRYCOUNT
.<=
/p>
DROPDB
CHECKDB
). The restored dat=
abase is removed from the SQL Server instance regardless of whether any err=
ors or warnings were returned.DROPDBIFSUCCESSFULCHECKDB
, drops the database if the restor=
e completed successfully and the database integrity check completed without=
errors or warnings.ERASEFILES
MOVETO
folder. This is useful for managing t=
he number of backups in the MOVETO
folder when log s=
hipping. Note: You must also include =
FILEOPTIONS
.
For example, ERASEFILES =3D 24
deletes files that a=
re more than 24 days old; ERASEFILES =3D 24h
deletes=
files that are more than 24 hours old. Note that a day is calculated as a =
period of 24 hours, and takes no account of calendar date.
For example, ERASEFILES =3D 5b
ensures the latest 5=
backups are kept; older backups are deleted.
PASSWORD
option is n=
ot specified (because the backup is not encrypted), any existing encrypted =
backups in the DISK
location will not be identified =
by ERASEFILES
because the file header cannot be read=
. This may result in backups older than the specified age or in excess =
;of the specified number being retained. Example
"RESTORE=
LOG [pubs] FROM DISK =3D 'C:\shipped_logs\pubs\LOG_20120229_151009.sqb' WI=
TH MOVETO =3D 'C:\processed_logs', ERASEFILES =3D 10b, FILEOPTIONS =3D 1, N=
ORECOVERY"
ERASEFILES
and ERASEFILES_REMOTE
are includ=
ed in the same command, the ERASEFILES_REMOTE
setting override=
s the ERASEFILES
setting for remote MOVETO=
locations.ERASEFILES_PRIMARY
ERASEFILES_PRIMARY
and ERASEFILES_SECONDARY
, =
orERASEFILES
, ERASEFILES_REMOTE
and FILEO=
PTIONS
ERASEFILES_PRIMARY
in the same command as =
ERASEFILES
, ERASEFILES_REMOTE
or FILEOPTIONS.
DISK=
location. If multiple DISK
locatio=
ns are specified, the setting is applied to each folder. The backup fi=
les are deleted only if the backup process completes successfully.
ERASEFILES_PRIMARY =3D 24
de=
letes files that are more than 24 days old; ERASEFILES_PRIMARY =
=3D 24h
deletes files that are more than 24 hours old. Note tha=
t a day is calculated as a period of 24 hours, and takes no account of cale=
ndar date.ERASEFILES_PRIMARY =3D 5b
e=
nsures the latest 5 backups are kept; older backups are deleted.
PASSWORD
option is n=
ot specified (because the backup is not encrypted), any existing encrypted =
backups in the DISK
location will not be identified =
by ERASEFILES_PRIMARY
because the file header cannot=
be read. This may result in backups older than the specified age or in exc=
ess of the specified number being retained. Example
"RESTORE=
DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs\FULL_20130501_213000.sqb' W=
ITH ERASEFILES_PRIMARY =3D 5"
MOVETO
locati=
on, use ERASEFILES_SECONDARY
.MOVETO
folders. This is useful for managing t=
he number of files in the MOVETO
folder when log shi=
pping.
ERASEFILES_REMOTE =3D 24
del=
etes files that are more than 24 days old; ERASEFILES_REMOTE =3D=
24h
deletes files that are more than 24 hours old. Note that a=
day is calculated as a period of 24 hours, and takes no account of calenda=
r date.ERASEFILES_REMOTE =3D 5b
en=
sures the latest 5 backups are kept; older backups are deleted.
PASSWORD
option is n=
ot specified (because the backup is not encrypted), any existing encrypted =
backups in the DISK
location will not be identified =
by ERASEFILES_REMOTE
because the file header cannot =
be read. This may result in backups older than the specified age or in exce=
ss of the specified number being retained. Example
"RESTORE=
LOG [pubs] FROM DISK =3D 'C:\shipped_logs\pubs\LOG_20120229_151009.sqb' WI=
TH MOVETO =3D '\\Server01\processed_logs', ERASEFILES_REMOTE =3D 6h, NORECO=
VERY"
MOVETO
folders, use&nb=
sp;ERASEFILES
and FILEOPTIONS
.ERASEFILES_SECONDARY
ERASEFILES_PRIMARY
and ERASEFILES_SECONDARY
, =
orERASEFILES
, ERASEFILES_REMOTE
and =
FILEOPTIONS
ERASEFILES_SECONDARY
in the same command as ERASEFILES_REMOTE
or FIL=
EOPTIONS
.MOVE=
TO
folder.
ERASEFILES_SECONDARY =3D 24
=
deletes files that are more than 24 days old; ERASEFILES_SECONDA=
RY =3D 24h
deletes files that are more than 24 hours old. Note =
that a day is calculated as a period of 24 hours, and takes no account of c=
alendar date.ERASEFILES_SECONDARY =3D 5b
 =
;ensures the latest 5 backups are kept; older backups are deleted.
PASSWORD
option is n=
ot specified (because the backup is not encrypted), any existing encrypted =
backups in the DISK
location will not be identified =
by ERASEFILES_SECONDARY
because the file header cann=
ot be read. This may result in backups older than the specified age or in e=
xcess of the specified number being retained. Examples
"RESTORE=
LOG [pubs] FROM DISK =3D 'C:\shipped_logs\pubs\LOG_20120229_151009.sqb' WI=
TH MOVETO =3D 'C:\processed_logs', ERASEFILES_SECONDARY =3D 12h, NORECOVERY=
"
"RESTORE=
DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs\FULL_20130501_213000.sqb' W=
ITH MOVETO =3D 'E:\Archive\<DATABASE>', ERASEEFILES_PRIMARY =3D 5b, E=
RASEFILES_SECONDARY =3D 60"
=
FILEOPTIONS
ERASEFILES
. Specifies whether =
backup files are to be deleted from the MOVETO
folde=
r. Specify the sum of the values that correspond to the options you require=
:
1
Delete backup files in the
MOVETO
f=
older if they are older than the number of days or hours specified in ERASEFILES
.
2
Do not delete backup files in the
MOVETO=
code> folder that are older than the number of days or hours specified in <=
code>ERASEFILES
if they have the ARCHIVE
flag set.ERASEF=
ILES
. For example, to delete backup files in the MOVETO=
code> folder that are older than 5 days:
"RESTORE=
DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs\FULL_20120229.sqb' WITH MOV=
ETO =3D 'C:\Backups\Archive\pubs', ERASEFILES =3D 5, FILEOPTIONS =3D 1"
MOVETO
folder=
that are older than 5 days and do not have the ARCHIVE
&n=
bsp;flag set, (values 1 + 2):"RESTORE=
DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs\FULL_20120229.sqb' WITH MOV=
ETO =3D 'C:\Backups\Archive\pubs', ERASEFILES =3D 5, FILEOPTIONS =3D 3"
=
strong>KEEP_CDC
NORECOVERY
. Refer =
to your SQL Server documentation for more information.NORECOVERY
. Refer =
to your SQL Server documentation for more information.=
LOG_ONERROR
MAILTO
opti=
ons below for details on emailing log files.)=
LOG_ONERRORONLY
MAILTO
&nbs=
p;options below for details on emailing log files.)LOGTO
"RESTORE=
DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs\FULL_20120229.sqb' WITH LOG=
TO =3D 'C:\Logs'"
"RESTORE=
DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs\FULL_20120229.sqb' WITH LOG=
TO =3D 'C:\Logs', LOGTO =3D 'C:\Logs\SQBSecondaryLog.txt'"
=
LOGTO
commands.MAILTO
"RESTORE=
DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs\FULL_20120229.sqb' WITH MAI=
LTO =3D 'dba01@myco.com*;*dba02@myco.com'"
MAILTO_NOLOG
MAILTO parameter has been specified.
MAILTO_ONERROR"RESTORE=
DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs\FULL_20120229.sqb' WITH MAI=
LTO_ONERROR =3D 'dba01@myco.com;dba02@myco.com'"
MAILTO_ONERRORONLY"RESTORE=
DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs\FULL_20120229.sqb' WITH MAI=
LTO_ONERRORONLY =3D 'dba01@myco.com;dba02@myco.com'"
=
MAXTRANSFERSIZE
"RESTORE=
DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs\FULL_20120229.sqb' WITH MAX=
TRANSFERSIZE =3D 262144"
=
MOVE DATAFILES TO
MOVE 'logical_fil=
e_name' TO 'operating_system_file_name'
to move one data file t=
o a new location and all other data files to a different location. For exam=
ple:"RESTORE=
DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs\FULL_20120229.sqb' WITH MOV=
E DATAFILES TO 'C:\Test\NewDataLocation', MOVE 'TestDB_Primary2' TO 'E:\Tes=
t\NewDataLocation\AnotherFolder\TestDBPrimary2.mdf'"
LATEST_FULL<=
/code> and
LATEST_ALL
(see FROM DISK
argument).<=
/p>
MOVE 'logical_name' TO 'operating_system_file=
_name'
to specify one location for a particular filestream data=
container and another location for all other filestreams. For example:"RESTORE=
DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs\FULL_20120229.sqb' WITH MOV=
E FILESTREAMS TO 'C:\Test\NewFilestreamLocation', MOVE 'Test_Data' TO 'C:\T=
est\NewFilestreamLocation\AnotherFolder\Test_FSData'"
LATEST_FULL
and LATEST_ALL
(see FROM DISK
argument).MOVE FULLTEXTCATALOGS TO=
h3>
MOVE 'logical_name' TO 'operating_syst=
em_file_name'
to specify one location for a particular full tex=
t catalog and another location for all other full text catalogs. For exampl=
e:"RESTORE=
DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs\FULL_20120229.sqb' WITH MOV=
E FULLTEXTCATALOGS TO 'C:\Test\NewFullTextLocation', MOVE 'sysft_MoveFilesC=
atalog' TO 'C:\Test\NewFullTextLocation\AnotherFolder\NewMoveFilesCatalog'"=
LATEST_FULL
and LATEST_ALL
(see FROM DISK
argument).MOVE LOGFILES TOMOVE 'logical_fil=
e_name' TO 'operating_system_file_name'
to move one log file to=
a new location and all other log files to a different location. For exampl=
e:"RESTORE=
DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs\FULL_20120229.sqb' WITH MOV=
E LOGFILES TO 'C:\Test\NewLogLocation', MOVE 'TestDB_Log2' TO 'E:\Test\NewL=
ogLocation\AnotherFolder\TestDBLog2.ldf'"
LATEST_FULL<=
/code> and
is required to prevent a database of a different n=
ame being overwritten by accident.LATEST_ALL
(see FROM DISK
argument).<=
/p>
MOVE 'logical_file_name' TO 'operating_system_file_name'
RESTORE
command is ex=
ecuted. This option can also be used to rename the physical files. For exam=
ple:"RESTORE=
DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs\FULL_20120229.sqb' WITH MOV=
E 'pubs_data' TO 'F:\Pubs02\Data\pubs_data02'"
MOVETO
MOVETO
argument, f=
or example:"RESTORE=
DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs\FULL_20120229.sqb' WITH MOV=
ETO =3D 'C:\Backups\Archive\<INSTANCE>\<DATABASE>\'"
NO=
LOG
MAILTO
options above fo=
r details on emailing log files.)NORECOVERY
ORPHAN_CHECK
ORPHAN_CHECK
will only detect database =
users that are based on SQL Server logins; orphaned users based on Windows =
principals are not detected.PARTIAL
DATABASE
argumen=
t above for details on how to specify particular filegroups in a r=
estore operation.=
strong>PASSWORD
"RESTORE=
DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs\FULL_20120229.sqb' WITH PAS=
SWORD =3D 'Password'"
PASSWOR=
D =3D 'FILE:C:\mypasswords\password.txt'
=
strong>RECOVERY
WITH RECOVERY=
code> is the default behavior. For more information, refer to your SQL=
Server documentation.
REPLACE
REPLACE
is not required to overwrite a database which =
matches the name recorded in the backup.=
RESTRICTED_USER
SINGLERESULTSET
RESTORE
=
command will return two result sets by default in most cases, unless you sp=
ecify the SINGLERESULTSET
keyword.STANDBY
STANDBY
option is allowed for offline restore (i=
ncluding partial restore). The option is disallowed for online restore.STANDBY
argument.'standby_file_name=
'
RESTORE WITH STANDBY
.RESTORE DATABASE
or RESTORE LOG
command, 'standby_file_name' can =
include tags, but the=
se are not required.STOPAT
RESTORE LOG
stateme=
nt. For example:"RESTORE=
DATABASE [pubs] FROM DISK =3D 'C:\Backups\pubs_full.sqb' WITH NORECOVERY'"
"RESTORE LOG [pubs] FROM DISK =3D 'C:\Backups\pubs_log_20120601093000.sqb' =
WITH NORECOVERY, STOPAT =3D '2012-06-01T09:40:30'"
"RESTORE LOG [pubs] FROM DISK =3D 'C:\Backups\pubs_log_20120601094500.sqb' =
WITH RECOVERY, STOPAT =3D '2012-06-01T09:40:30'"
STOPATMARK
AFTER
can be used when specifying a marked transaction=
and is useful when the mark name is not unique. The database is recovered =
as far as the first marked transaction to have occurred on or after the spe=
cified time.STOPBEFOREMARK
AFTER
can be used when specifying a marked transaction=
and is useful when the mark name is not unique. The database is recovered =
up to the first marked transaction to have occurred on or after the specifi=
ed time.THREADPRIORITY
0
Idle
1
Very low
2
Low
3
Normal
4
High
5
Very high
6
Time critical
Examples=
h2>
Restore a data=
base from a single file
SQLBack=
upC.exe -I {instance name} -SQL "RESTORE DATABASE [pubs] FROM DIS=
K =3D 'C:\Backups\pubs_01.sqb' WITH REPLACE"
EXECUTE =
master..sqlbackup '-SQL "RESTORE DATABASE [pubs] FROM DISK =3D ''=
C:\Backups\pubs_01.sqb'' WITH REPLACE" '
Restore a database from multiple (split) backup files
SQLBack=
upC.exe -I {instance name} -SQL "RESTORE DATABASE [pubs] FROM DIS=
K =3D 'C:\Backups\pubs_01.sqb', DISK =3D 'C:\Backups\pubs_02.sqb' WITH REPL=
ACE"
EXECUTE =
master..sqlbackup '-SQL "RESTORE DATABASE [pubs] FROM DISK =3D ''=
C:\Backups\pubs_01.sqb'', DISK =3D ''C:\Backups\pubs_02.sqb'' WITH REPLACE"=
'
Restore a database to a new name and move the database files
SQLBack=
upC.exe -I {instance name} -SQL "RESTORE DATABASE [pubs02] FROM D=
ISK =3D 'C:\Backups\pubs_01.sqb' WITH MOVE 'pubs' TO 'E:\Data\pubs02.mdf', =
MOVE 'pubs_log' TO 'E:\Data\pubs02.ldf' "
EXECUTE =
master..sqlbackup '-SQL "RESTORE DATABASE [pubs02] FROM DISK =3D =
''C:\Backups\pubs_01.sqb'' WITH MOVE ''pubs'' TO ''E:\Data\pubs02.mdf'', MO=
VE ''pubs_log'' TO ''E:\Data\pubs02.ldf'' " '
Restore a database from the latest full backup and move th=
e database files
SQLBack=
upC.exe -I {instance name} -SQL "RESTORE DATABASE [pubs] FROM DIS=
K =3D 'C:\Backups\pubs*.sqb' LATEST_FULL WITH MOVE DATAFILES TO 'E:\Data', =
MOVE LOGFILES TO 'E:\Logs', REPLACE"
EXECUTE =
master..sqlbackup '-SQL "RESTORE DATABASE [pubs] FROM DISK =3D ''=
C:\Backups\pubs*.sqb'' LATEST_FULL WITH MOVE DATAFILES TO ''E:\Data'', MOVE=
LOGFILES TO ''E:\Logs'', REPLACE" '
Restore a database from the latest full backup on different disks=
SQLBack=
upC.exe -I {instance name} -SQL "RESTORE DATABASE [pubs] FROM DIS=
K =3D 'C:\Backups*pubs*.sqb', DISK =3D 'D:\Backups*pubs*.sqb', DISK =3D 'E:=
\Backups*pubs*.sqb' LATEST_FULL WITH REPLACE"
EXECUTE =
master..sqlbackup '-SQL "RESTORE DATABASE [pubs] FROM DISK =3D ''=
C:\Backups*pubs*.sqb'', DISK =3D ''D:\Backups*pubs*.sqb'', DISK =3D ''E:\Ba=
ckups*pubs*.sqb'' LATEST_FULL WITH REPLACE
Restore a database from the latest backup set on different disks=
h3>
SQLBack=
upC.exe -I {instance name} -SQL "RESTORE DATABASE [pubs] FROM DIS=
K =3D 'C:\Backups*pubs*.sqb', DISK =3D 'D:\Backups*pubs*.sqb', DISK =3D 'E:=
\Backups*pubs*.sqb' LATEST_ALL WITH REPLACE"
EXECUTE =
master..sqlbackup '-SQL "RESTORE DATABASE [pubs] FROM DISK =3D ''=
C:\Backups*pubs*.sqb'', FROM DISK =3D ''D:\Backups*pubs*.sqb'', DISK =3D ''=
E:\Backups*pubs*.sqb'' LATEST_ALL WITH REPLACE" '
Restore to a new database from the latest backup set =
and check for orphaned users
SQLBack=
upC.exe -I {instance name} -SQL "RESTORE DATABASE [pubs02] FROM D=
ISK =3D 'C:\Backups\pubs*.sqb' SOURCE =3D 'pubs' LATEST_ALL WITH ORPHAN_CHE=
CK"
EXECUTE =
master..sqlbackup '-SQL "RESTORE DATABASE [pubs02] FROM DISK =3D =
''C:\Backups\pubs*.sqb'' SOURCE =3D ''pubs'' LATEST_ALL WITH ORPHAN_CHECK" =
'
Rest=
ore a database from an encrypted backup file
SQLBack=
upC.exe -I {instance name} -SQL "RESTORE DATABASE [pubs] FROM DIS=
K =3D 'C:\Backups\pubs_01.sqb' WITH PASSWORD =3D 'MyPassword' "
EXECUTE =
master..sqlbackup '-SQL "RESTORE DATABASE [pubs] FROM DISK =3D ''=
C:\Backups\pubs_01.sqb'' WITH PASSWORD =3D ''MyPassword'' "
Restore a database from an encrypted backup file=
using a password stored in a text file
SQLBack=
upC.exe -I {instance name} -SQL "RESTORE DATABASE [pubs] FROM DIS=
K =3D 'C:\Backups\pubs_01.sqb' WITH PASSWORD =3D 'FILE:C:\mypasswords\passw=
ord.txt' "
EXECUTE =
master..sqlbackup '-SQL "RESTORE DATABASE [pubs] FROM DISK =3D ''=
C:\Backups\pubs_01.sqb'' WITH PASSWORD =3D 'FILE:C:\mypasswords\password.tx=
t' "
Restore a dat=
abase in NORECOVERY mode
SQLBack=
upC.exe -I {instance name} -SQL "RESTORE DATABASE [pubs] FROM DISK =3D 'C:\=
Backups\pubs_01.sqb' WITH NORECOVERY"
EXECUTE =
master..sqlbackup '-SQL "RESTORE DATABASE [pubs] FROM DISK =3D ''C:\Backups=
\pubs_01.sqb'' WITH NORECOVERY"
Restore a data=
base in READ-ONLY mode =
h3>
SQLBack=
upC.exe -I {instance name} -SQL "RESTORE DATABASE [pubs] FROM DISK =3D 'C:\=
Backups\pubs_01.sqb' WITH STANDBY =3D 'C:\Standby\pubs_log.DAT' "
EXECUTE =
master..sqlbackup '-SQL "RESTORE DATABASE [pubs] FROM DISK =3D ''C:\Backups=
\pubs_01.sqb'' WITH STANDBY =3D ''C:\Standby\pubs_log.DAT'' " '