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 syntax 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

Note that the following arguments are only available with SQL Server 2005 and SQL Server 2008 instances:

    CHECKSUM / NO_CHECKSUM
    CONTINUE_AFTER_ERROR / STOP_ON_ERROR

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 } ]
    [ [ , ] DISCONNECT_EXISTING ]
    [ [ , ] ERASEFILES = { days | hours{h} | keep{b} } ]
    [ [ , ] ERASEFILES_REMOTE = { days | hours{h} | keep{b} } ]
    [ [ , ] FILEOPTIONS = { 1 | 2 | 3 } ]
    [ [ , ] KEEP_REPLICATION ]
    [ [ , ] LOGTO = { 'target_folder_name' | 'file name' } ] [ ,...n ]
    [ [ , ] MAILTO = { 'recipients' } ]
    [ [ , ] MAILTO_ONERROR = { 'recipients' } ]
    [ [ , ] MAILTO_ONERRORONLY = { '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' } ]
    [ [ , ] ORPHAN_CHECK ]
    [ [ , ] PASSWORD = { 'password' } ]
    [ [ , ] REPLACE ]
    [ [ , ] RESTRICTED_USER ]
    [ [ , ] SINGLERESULTSET ]
    [ [ , ] THREADPRIORITY = { 0 | 1 | 2 | 3 | 4 | 5 | 6 } ]
]

Restore part of a database

RESTORE DATABASE { database_name }
    < file_or_filegroup > [ ,...n ]
[ FROM { DISK } = { 'physical_backup_device_name' } ] [ ,...n ]
[ WITH
   PARTIAL
    [ [ , ] { CHECKSUM | NO_CHECKSUM } ]
    [ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
    [ [ , ] DISCONNECT_EXISTING ]
    [ [ , ] ERASEFILES = { days | hours{h} | keep{b} } ]
    [ [ , ] ERASEFILES_REMOTE = { days | hours{h} | keep{b} } ]
    [ [ , ] FILEOPTIONS = { 1 | 2 | 3 } ]
    [ [ , ] LOGTO = { 'target_folder_name' | 'file name' } ] [ ,...n ]
    [ [ , ] MAILTO = { 'recipients' } ]
    [ [ , ] MAILTO_ONERROR = { 'recipients' } ]
    [ [ , ] MAILTO_ONERRORONLY = { 'recipients' } ]
    [ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ] [ ,...n ]
    [ [ , ] MOVETO ' { 'target_folder_name' } ]
    [ [ , ] NORECOVERY ]
    [ [ , ] ORPHAN_CHECK ]
    [ [ , ] PASSWORD = { 'password' } ]
    [ [ , ] REPLACE ]
    [ [ , ] RESTRICTED_USER ]
    [ [ , ] SINGLERESULTSET ]
    [ [ , ] 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 } ]
    [ [ , ] DISCONNECT_EXISTING ]
    [ [ , ] ERASEFILES = { days | hours{h} | keep{b} } ]
    [ [ , ] ERASEFILES_REMOTE = { days | hours{h} | keep{b} } ]
    [ [ , ] FILEOPTIONS = { 1 | 2 | 3 } ]
    [ [ , ] KEEP_REPLICATION ]
    [ [ , ] LOGTO = { 'target_folder_name | file_name' } ] [ ,...n ]
    [ [ , ] MAILTO = { 'recipients' } ]
    [ [ , ] MAILTO_ONERROR = { 'recipients' } ]
    [ [ , ] MAILTO_ONERRORONLY = { 'recipients' } ]
    [ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
    [ [ , ] MOVETO = { 'target_folder_name' } ]
    [ [ , ] { NORECOVERY | RECOVERY | STANDBY = 'undo_file_name' } ]
    [ [ , ] ORPHAN_CHECK ]
    [ [ , ] PASSWORD = { 'password' } ]
    [ [ , ] REPLACE ]
    [ [ , ] RESTRICTED_USER ]
    [ [ , ] SINGLERESULTSET ]
    [ [ , ] { 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' } ]
    [ [ , ] SINGLERESULTSET ]
]

Restore header

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

We recommend you 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 } ]
    [ [ , ] MAILTO = { 'recipients' } ]
    [ [ , ] MAILTO_ONERROR = { 'recipients' } ]
    [ [ , ] MAILTO_ONERRORONLY = { 'recipients' } ]
    [ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
    [ [ , ] PASSWORD = { 'password' } ]
    [ [ , ] SINGLERESULTSET ]
]

FROM DISK argument

FROM DISK

You can specify up to 32 DISK values. You can also 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', DISK = 'C:\Backups\pubs_02.sqb', DISK = 'C:\Backups\pubs_03.sqb',
   DISK = 'C:\Backups\pubs_04.sqb'], DISK = '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

DISCONNECT_EXISTING

Kills any existing connections to the database before starting the restore. Restoring to an existing database will fail if there are any connections to the database.

ERASEFILES

Deletes all SQL Backup files for the database that are older than the specified number of days (or hours), or that exceed the number of files you want to keep, 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.
• To specify the number of files to keep, type b after the number. For example, ERASEFILES = 5b ensures the latest 5 files are kept; older files are deleted.

Note that if SQL Backup cannot list the contents of the folder that contains the files to be deleted, it cannot delete the files. Ensure that the user account from which you are running SQL Backup has permissions to list the folder contents.

ERASEFILES_REMOTE

Deletes all SQL Backup files for the database that are older than the specified number of days (or hours), or that exceed the number of files you want to keep, and are located on a network share. You use this argument if you want to specify different deletion intervals for local files and files located on network shares. If not specified, SQL Backup uses the ERASEFILES value.

The backup files are deleted only if the restore process completes successfully.

• To specify the age of files to be deleted in hours, type h after the number. For example, ERASEFILES_REMOTE = 24 deletes files that are more than 24 days old; ERASEFILES_REMOTE = 24h deletes files that are more than 24 hours old.
• To specify the number of files to keep, type b after the number. For example, ERASEFILES = 5b ensures the latest 5 files are kept; older files are deleted.

You can use FILEOPTIONS to specify that files will also be deleted in the MOVETO folder.

Note that if SQL Backup cannot list the contents of the folder that contains the files to be deleted, it cannot delete the files. Ensure that the user account from which you are running SQL Backup has permissions to list the folder contents.

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:

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 or the restore process completes successfully but with warnings. 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_ONERROR = '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_ONERRORONLY

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_ONERRORONLY = '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.

For example:

MAXTRANSFERSIZE = 262144

If not specified, defaults to 1048576. However, if you have created the following DWORD registry key, SQL Backup uses the defined value as the default value:

HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>\MAXTRANSFERSIZE (32-bit)

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>\MAXTRANSFERSIZE (64-bit)

MOVETO

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

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

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

ORPHAN_CHECK

Specifies that once the restore has completed, the database should be checked for orphaned users. Database user names are considered to be orphaned if they do not have a corresponding login defined on the SQL Server instance. Orphaned users are often created when you restore a database backup to a different SQL Server instance.

If orphaned users are detected, warning 472 is generated and each orphaned user is listed in the SQL Backup log file along with the associated SID.

PASSWORD

Specifies the password to be used with encrypted backup files; you cannot use the encrypted form of the password, see Troubleshooting for more information.

SINGLERESULTSET

Specifies that the results returned by the RESTORE command should be limited to just one result set. This may be useful if you want to manipulate results using a Transact-SQL script. Such scripts can only manipulate results when a single result set is returned. The BACKUP command will return two result sets by default in most cases, unless you specify the SINGLERESULTSET keyword.

STANDBY

Specifies a standby file that allows the recovery effects to be undone. The STANDBY option is allowed for offline restore (including partial restore). The option is disallowed for online restore.

Refer to your SQL Server documentation for more information about the STANDBY argument.

'undo_file_name'

Is a standby file used to keep a "copy-on-write" pre-image for pages modified during the undo pass of a RESTORE WITH STANDBY.

When used with the RESTORE DATABASE or RESTORE LOG command, 'undo_file_name' can include tags, but these are not required.

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: