Specifying Restoration Options

  • Article

You can specify the following options for a SQL Server restore process:

  • RECOVERY. You use this option with the last transaction log fiile to be restored or with a full database restore to return the database to a consistent state. SQL Server rolls back any uncommitted transactions in the transaction log file and rolls forward any committed transactions. The database is available for use after the recovery process is complete.
  • NORECOVERY. You use this option when you have multiple backups to restore. Consider the following when you use the NORECOVERY option:
    • Specify the NORECOVERY option for all backups except the last backup to be restored.
    • SQL Server neither rolls back any uncommitted transactions in the transaction log file nor rolls forward any committed transactions.
    • The database is unavailable for use until the database is recovered.

When you use the RESTORE statement, use the following options to specify how the restore is done:

  • FILE. You use this option to select specific backups from a backup file that contains multiple backups. You must specify a file number that corresponds to the order in which the backup exists within the backup file.

  • RESTART. You use this option to continue an interrupted recovery operation. The recovery will continue from the point at which the previous attempt was interrupted.

  • MOVE...TO. Use this option to specify where to restore the backup files if you are restoring files to a different location, such as a different disk, server, or standby SQL Server.

    Ee824987.note(en-US,CS.20).gifNote

    • You can also use the system stored procedures sp_attach_db or sp_attach_single_file_db to move a database from one server to another by copying the database files and then attaching them to the master database.

  • REPLACE. You use this option only if you want to replace an existing database with data from a backup of a different database.

    Ee824987.caution(en-US,CS.20).gifCaution

    • If you use the REPLACE option, SQL Server does not perform a safety check.

The following code example shows how to use SQL Server restore options:

RESTORE DATABASE {database_name|@database_name_var}
  [FROM <backup_device>[,...n]
  [WITH
    [ FILE=file_number]
    [[,] MOVE 'logical_file_name' TO 'operating_system_file_name']
    [[,] REPLACE]
    [[,] {NORECOVERY|RECOVERY|STANDBY = undo_file_name}]]
    [[.] RESTART
Where <backup_device> is
    {{backup_device_name|@backup_device_name_var}|
    {DISK|TAPE|PIPE} = 'temp_backup_device'|
@temp_backup_device_var}
  }

This section contains:

Copyright © 2005 Microsoft Corporation.
All rights reserved.