The RESTORE Command

Use the RESTORE command with the SQL Backup -SQL parameter to restore a backup that you created using SQL Backup.

Syntax provides the syntax for the command; additional arguments and extended syntax are highlighted
Extended arguments describes the arguments that are provided by SQL Backup; for information about the standard arguments, refer to your SQL Server documentation.

Some example snippets are included in this topic. For more detailed examples, see Toolkit Examples. Note that the extended stored procedure expects only one parameter, which must be delimited by single quotes. Therefore, wherever a single quote is used for the parameters below, for the extended stored procedure you must use two single quotes so that SQL Server does not interpret it as a string delimiter.

For information about how you restore multiple backups, see Restoring Multiple Backups.

Syntax

Restore an entire database

RESTORE DATABASE { database_name }
        < file_or_filegroup_or_pages > [ ,...n ]
[ FROM { DISK } = { 'physical_backup_device_name' } ] [ ,...n ]
[ WITH
    [ [ , ] { CHECKSUM | NO_CHECKSUM } ]
    [ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
    [ [ , ] ERASEFILES = { 'days' | 'hours'h } ]
    [ [ , ] FILEOPTIONS = { 1 | 2 | 3 } ]
    [ [ , ] KEEP_REPLICATION ]
    [ [ , ] LOGTO = { 'target_folder_name' | 'file name' } ] [ ,...n ]
    [ [ , ] MAILTO = { 'recipients' } ]
    [ [ , ] MAILTO_ONERROR = { 'recipients' } ]
    [ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 ]
    [ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ] [ ,...n ]
    [ [ , ] MOVETO ' { 'target_folder_name' } ]
    [ [ , ] { NORECOVERY | RECOVERY | STANDBY = 'undo_file_name' } ]
    [ [ , ] PASSWORD = { 'password' } ]
    [ [ , ] REPLACE ]
    [ [ , ] RESTRICTED_USER ]
    [ [ , ] THREADPRIORITY = { 0 | 1 | 2 | 3 | 4 | 5 | 6 } ]
]

Restore a transaction log

RESTORE LOG { database_name }
        < file_or_filegroup_or_pages > [ ,...n ]
[ FROM { DISK } = { 'physical_backup_device_name' | 'file_search_pattern' } ] [ ,...n ]
[ WITH
    [ [ , ] { CHECKSUM | NO_CHECKSUM } ]
    [ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
    [ [ , ] ERASEFILES = { 'days' | 'hours'h } ]
    [ [ , ] FILEOPTIONS = { 1 | 2 | 3 } ]
    [ [ , ] KEEP_REPLICATION ]
    [ [ , ] LOGTO = { 'target_folder_name | file_name' } ] [ ,...n ]
    [ [ , ] MAILTO = { 'recipients' } ]
    [ [ , ] MAILTO_ONERROR = { 'recipients' } ]
    [ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 ]
    [ [ , ] MOVETO = { 'target_folder_name' } ]
    [ [ , ] { NORECOVERY | RECOVERY | STANDBY = 'undo_file_name' } ]
    [ [ , ] PASSWORD = { 'password' } ]
    [ [ , ] REPLACE ]
    [ [ , ] RESTRICTED_USER ]
    [ [ , ] { STOPAT = { 'date_time' | @date_time_var } ]
      | STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
                  [ AFTER 'datetime' ]
      | STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
                  [ AFTER 'datetime']
    } ]
    [ [ , ] THREADPRIORITY = { 0 | 1 | 2 | 3 | 4 | 5 | 6 } ]
]

Restore file list

RESTORE FILELISTONLY
[ FROM { DISK } = { 'physical_backup_device_name' } ]
[ WITH
    [ [ , ] { CHECKSUM | NO_CHECKSUM } ]
    [ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 ]
    [ [ , ] PASSWORD = { 'password' } ]
]

Restore header

RESTORE HEADERONLY
[ FROM { DISK } = { 'physical_backup_device_name' } ]
[ WITH
    [ [ , ] { CHECKSUM | NO_CHECKSUM } ]
    [ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 ]
    [ [ , ] PASSWORD = { 'password' } ]
]

Note You are recommended to use the SQL Backup 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 details, see The RESTORE SQBHEADERONLY Command.

Verify backup set

RESTORE VERIFYONLY
[ FROM { DISK } = { 'physical_backup_device_name' } ] [ ,...n ]
[ WITH
    [ [ , ] { CHECKSUM | NO_CHECKSUM } ]
    [ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 ]
    [ [ , ] PASSWORD = { 'password' } ]
]

FROM DISK argument

FROM DISK

You can specify wildcard characters in the physical backup device name for full and differential backups. All files that match the wildcard characters must belong to the same backup set. For example, instead of:

FROM DISK = ['C:\Backups\pubs_01.sqb'], ['C:\Backups\pubs_02.sqb'], ['C:\Backups\pubs_03.sqb'], ['C:\Backups\pubs_04.sqb'], ['C:\Backups\pubs_05.sqb']

you can enter:

FROM DISK = ['C:\Backups\pubs_*.sqb']

For more information about restoring multiple backups, see Restoring Multiple Backups.

Extended arguments

ERASEFILES

Deletes all SQL Backup files for the database that are older than the specified number of days (or hours), and are located in the MOVETO folder. You must also set the FILEOPTIONS argument. The files are deleted only if the name of the SQL Server, instance (if applicable), and database recorded in the file header match the details of the database that is being restored.

To specify the age of files to be deleted in hours, type h after the number. For example, ERASEFILES = '24' deletes files that are more than 24 days old; ERASEFILES = '24h' deletes files that are more than 24 hours old.

FILEOPTIONS

Specifies whether old backup files are to be deleted in the primary backup folder and the MOVETO folder. Specify the sum of the values that correspond to the options you require:

1	Delete old backup files in the MOVETO folder if they are older than the number of days or hours specified in ERASEFILES.
2	Do not delete old backup files in the MOVETO folder that are older than the number of days or hours specified in ERASEFILES if they have the ARCHIVE flag set.

Valid values are 1, 2, and 3.

You must also set the age of the files to delete using ERASEFILES. For example, to delete old backup files in the MOVETO folder that are older than 5 days:

BACKUP DATABASE ... WITH MOVETO = ... , ERASEFILES = 5, FILEOPTIONS = 1

To delete any existing files in the MOVETO folder that are older than 5 days and do not have the ARCHIVE flag set, (values 1 + 2):

BACKUP DATABASE ... WITH MOVETO = ... , ERASEFILES = 5, FILEOPTIONS = 3

LOGTO

Specifies that a copy of the log file is to be saved.

By default, the primary log file is created in the folder C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log; you can change this location in your log file options. To create a copy with the same name as the primary log file, specify the folder. For example:

LOGTO = 'C:\Logs'

To create a copy with a different name from the primary log file, specify the folder and file name. For example:

LOGTO = 'C:\Logs\SQBSecondaryLog.txt'

To copy the log file to more than one location, use multiple LOGTO commands.

MAILTO

Specifies that a copy of the log file is to be sent to one or more users by email. SQL Backup uses the settings specified in your email options to send the email. To specify multiple recipients, separate the email addresses with a semi-colon (;). For example:

MAILTO = 'dba01@myco.com;dba02@myco.com'

If you have not defined email settings, the email will not be sent and a warning will be reported.

Note that if SQL Backup Lite is installed on the SQL Server, you cannot use email notification.

MAILTO_ONERROR

Specifies that a copy of the log file is to be sent to one or more users by email if SQL Backup encounters an error during the restore process. SQL Backup uses the settings specified in your email options to send the email. To specify multiple recipients, separate the email addresses with a semi-colon (;). For example:

MAILTO = 'dba01@myco.com;dba02@myco.com'

If you have not defined email settings, the email will not be sent and a warning will be reported.

Note that if SQL Backup Lite is installed on the SQL Server, you cannot use email notification.

MAXTRANSFERSIZE

Specifies the maximum size of each block of memory to be used when SQL Backup 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.

Valid values are integers in multiples of 65536, up to a maximum value of 1048576. Defaults to 1048576 if not specified.

For example:

MAXTRANSFERSIZE = 262144

MOVETO

Specifies that the backup files should be moved to another folder when the restore process completes. For example:

MOVETO = 'C:\Backups\Logs\Processed'

You must ensure that you have permission to delete files from the original folder, and to write the MOVETO folder.

PASSWORD

Specifies the password to be used with encrypted backup files.

THREADPRIORITY

Sets the SQL Backup thread priority when the backup or restore process is run. Valid values are 0 to 6, and correspond to the following priorities:

0	Idle
1	Very Low
2	Low
3	Normal
4	High
5	Very High
6	Time Critical