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

The KEEP_CDC argument is only available with SQL Server 2008 instances.

Restore an entire database

RESTORE DATABASE { database_name }
    < file_or_filegroup_or_pages > [ ,...n ]
[ FROM    
{
            {DISK} = { 'physical_backup_device_name' } [ ,...n ]
            [
                [ LATEST_FULL | LATEST_DIFF | LATEST_ALL ]
                |
                SOURCE = 'source_database_name' { LATEST_FULL | LATEST_DIFF | LATEST_ALL }
            ]
    |
            {BACKUPHISTORY} [ = 'history_database_name' ]
            { LATEST_FULL | LATEST_DIFF | LATEST_ALL }
    }
]
[ WITH
    [ [ , ] { CHECKSUM | NO_CHECKSUM } ]
   [ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
    [ [ , ] DISCONNECT_EXISTING ]
    [ [ , ] DISKRETRYCOUNT = { n } ]
    [ [ , ] DISKRETRYINTERVAL = { n } ]
    [ [ , ] ERASEFILES = { days | hours{h} | keep{b} } ]
    [ [ , ] ERASEFILES_REMOTE = { days | hours{h} | keep{b} } ]
    [ [ , ] FILEOPTIONS = { 1 | 2 | 3 } ]
    [ [ , ] KEEP_CDC ]
    [ [ , ] KEEP_REPLICATION ]
    [ [ , ] LOG_ONERROR ]
    [ [ , ] LOG_ONERRORONLY ]
    [ [ , ] LOGTO = { 'target_folder_name' | 'file name' } ] [ ,...n ]
    [ [ , ] MAILTO = { 'recipients' } ]
    [ [ , ] MAILTO_NOLOG ]
    [ [ , ] MAILTO_ONERROR = { 'recipients' } ]
    [ [ , ] MAILTO_ONERRORONLY = { 'recipients' } ]
    [ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
    [ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ] [ ,...n ]
    [ [ , ] MOVETO ' { 'target_folder_name' } ]
    [ [ , ] NOLOG ]
    [ [ , ] { 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 ]
    [ [ , ] DISKRETRYCOUNT = { n } ]
    [ [ , ] DISKRETRYINTERVAL = { n } ]
    [ [ , ] ERASEFILES = { days | hours{h} | keep{b} } ]
    [ [ , ] ERASEFILES_REMOTE = { days | hours{h} | keep{b} } ]
    [ [ , ] FILEOPTIONS = { 1 | 2 | 3 } ]
    [ [ , ] LOG_ONERROR ]
    [ [ , ] LOG_ONERRORONLY ]
    [ [ , ] LOGTO = { 'target_folder_name' | 'file name' } ] [ ,...n ]
    [ [ , ] MAILTO = { 'recipients' } ]
    [ [ , ] MAILTO_NOLOG ]
    [ [ , ] MAILTO_ONERROR = { 'recipients' } ]
    [ [ , ] MAILTO_ONERRORONLY = { 'recipients' } ]
    [ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ] [ ,...n ]
    [ [ , ] MOVETO ' { 'target_folder_name' } ]
    [ [ , ] NOLOG ]
    [ [ , ] 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 ]
    [ [ , ] DISKRETRYCOUNT = { n } ]
    [ [ , ] DISKRETRYINTERVAL = { n } ]
    [ [ , ] ERASEFILES = { days | hours{h} | keep{b} } ]
    [ [ , ] ERASEFILES_REMOTE = { days | hours{h} | keep{b} } ]
    [ [ , ] FILEOPTIONS = { 1 | 2 | 3 } ]
    [ [ , ] KEEP_CDC ]
    [ [ , ] KEEP_REPLICATION ]
    [ [ , ] LOG_ONERROR ]
    [ [ , ] LOG_ONERRORONLY ]
    [ [ , ] LOGTO = { 'target_folder_name | file_name' } ] [ ,...n ]
    [ [ , ] MAILTO = { 'recipients' } ]
    [ [ , ] MAILTO_NOLOG ]
    [ [ , ] MAILTO_ONERROR = { 'recipients' } ]
    [ [ , ] MAILTO_ONERRORONLY = { 'recipients' } ]
    [ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
    [ [ , ] MOVETO = { 'target_folder_name' } ]
    [ [ , ] NOLOG ]
    [ [ , ] { 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 ]
]

Note that you cannot use wildcards in the FROM DISK argument with RESTORE FILELISTONLY.

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.

Note that you cannot use wildcards in the FROM DISK argument with RESTORE HEADERONLY.

Verify backup set

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

Note that you cannot use wildcards in the FROM DISK argument with RESTORE VERIFYONLY.

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'

Note that you cannot use wildcard characters in the FROM DISK argument for RESTORE FILELISTONLY, RESTORE HEADERONLY, and RESTORE VERIFYONLY commands.

 

You can also use the optional LATEST_FULL or LATEST_DIFF keyword to select the most recent full or differential backup that matches the DISK values you specify, for the target database. If you use LATEST_FULL or LATEST_DIFF, the DISK values you specify must contain the '*' wildcard. For example:

FROM DISK = 'C:\Backups\pubs*.sqb' LATEST_FULL

will find backup files of the target database in the 'C:\Backups' directory with a filename matching 'pubs*.sqb', and will then restore the latest full backup.

Another example:

FROM DISK = 'C:\Backups\sales*.sqb', DISK = 'D:\Backups\sales*.sqb' LATEST_DIFF

will find all backup files of the target database in the 'C:\Backups' and 'D:\backups' directories with a filename matching 'sales*.sqb', and will then restore the latest differential backup from each directory. The latest files that match the wildcard characters 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.

  

If you specify the LATEST_ALL keyword, the most recent full backup of the target database will be restored, followed by the most recent differential backup, and then finally by the most recent transaction log backups. The DISK values you specify must contain the '*' wildcard. For example:

FROM DISK = 'C:\Backups\pubs*.sqb' LATEST_ALL

 

If you want to use LATEST_FULL, LATEST_DIFF, or LATEST_ALL to restore the latest backup files taken from a database other than the target database, include SOURCE = 'source_database_name'. For example:

RESTORE DATABASE Sales_Test FROM DISK = 'D:\backups\*.sqb' SOURCE = 'Sales_Prod' LATEST_ALL WITH RECOVERY

will restore the latest full backup of the Sales_Prod database (followed by subsequent differential and transaction log backups), to the Sales_Test database.

Read more about restoring multiple backups.

FROM BACKUPHISTORY argument

FROM BACKUPHISTORY

Use FROM BACKUPHISTORY when you simply want to restore the latest full, latest differential, or all the latest backups (including transaction log backups) for a particular database, without having to list the individual backup file locations. SQL Backup searches its own backup history to determine which backup files to restore. You must also specify the LATEST_FULL, LATEST_DIFF, or LATEST_ALL keyword. For example:

RESTORE DATABASE sales FROM BACKUPHISTORY LATEST_FULL WITH RECOVERY, REPLACE

will search the backup history for the 'sales' database, and will then restore the latest full backup over the current 'sales' database.

To search the backup history of a different database, you can specify this as part of the BACKUPHISTORY parameter. For example:

RESTORE DATABASE sales_dev FROM BACKUPHISTORY = 'sales' LATEST_FULL WITH RECOVERY, REPLACE

will search the backup history for the 'sales' database, and will then restore the latest full backup to the 'sales_dev' database.

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.

DISKRETRYCOUNT

In combination with DISKRETRYINTERVAL, this argument controls network resilience behavior.

DISKRETRYCOUNT specifies the maximum number of times to 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 value for DISKRETRYINTERVAL.

DISKRETRYCOUNT is valid with SQL Backup only.

DISKRETRYINTERVAL

In combination with DISKRETRYCOUNT, this argument controls network resilience behavior.

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 seconds is used. If you specify a value for DISKRETRYINTERVAL, you should also specify a value for DISKRETRYCOUNT.

DISKRETRYINTERVAL is valid with SQL Backup only.

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. Note that a 'day' is calculated as a period of 24 hours, and takes no account of calendar date.
• To specify the number of backups to keep, type b after the number. For example, ERASEFILES = 5b ensures the latest 5 backups are kept; older backups 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. Note that a 'day' is calculated as a period of 24 hours, and takes no account of calendar date.
• To specify the number of backups to keep, type b after the number. For example, ERASEFILES_REMOTE = 5b ensures the latest 5 backups are kept; older backups 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:

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

LOG_ONERROR

Specifies that a log file should only be created if SQL Backup encounters an error during the restore process, or the restore completes successfully but with warnings. Use this option if you want to restrict the number of log files created by your restore processes, but maintain log information whenever warnings or errors occur. This argument controls the creation of log files on disk only; emailed log files are not affected.

LOG_ONERRORONLY

Specifies that a log file should only be created if SQL Backup encounters an error during the restore process. Use this option if you want to restrict the number of log files created by your restore processes, but maintain log information whenever errors occur. This argument controls the creation of log files on disk only; emailed log files are not affected.

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 the outcome of the restore operation is emailed to one or more users; the email includes the contents of the log file. 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_NOLOG

Specifies that SQL Backup should not include the contents of the log file in the email. An email will still be sent to notify the specified recipients of success and/or failure, depending on which MAILTO parameter has been specified.

MAILTO_ONERROR

Specifies that that the outcome of the restore operation is emailed to one or more users if SQL Backup encounters an error during the restore process or the restore process completes successfully but with warnings. The email includes the contents of the log file. 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 that the outcome of the restore operation is emailed to one or more users if SQL Backup encounters an error during the restore process. The email includes the contents of the log file. 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. If the folder you specify does not exist, it will be created.

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

You can also use tags with the MOVETO argument, for example:

MOVETO = 'C:\Backups\Archive\<INSTANCE>\<DATABASE>\'

NOLOG

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 are concerned about generating a large number of log files, and are certain that you will not need to review the details of errors or warnings (for example, 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 only; emailed log files are not affected.

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. This is to prevent unauthorized users from restoring backups if they have access to the encrypted password, but not the unencrypted password.

SQL Backup version 3 allowed the use of encrypted passwords; these will no longer work. You must specify the password in unencrypted form:

RESTORE DATABASE database FROM DISK='filename' WITH PASSWORD = 'Password'

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:

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

See also File management options The BACKUP command The RESTORE SQBHEADERONLY command Toolkit parameters Toolkit syntax examples Using the command line Using the extended stored procedure