RESTORE Database

Complete Database Restore - an entire database from a full backup.

Syntax
      RESTORE DATABASE {database | @database_var} 
         [FROM backup_device [ ,...n ] ]
            [WITH option [,option...]] 
               [;]

   Options:
      {CHECKSUM | NO_CHECKSUM } 
      {CONTINUE_AFTER_ERROR | STOP_ON_ERROR } 
      ENABLE_BROKER
      ERROR_BROKER_CONVERSATIONS 
      FILE = { backup_set_file_number | @backup_set_file_number } 
      KEEP_REPLICATION 
      MEDIANAME = { media | @media_variable } 
      MEDIAPASSWORD = { mediapassword | @mediapw_variable } 
      MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name'   [ ,...n ] 
      NEW_BROKER 
      PASSWORD = { password | @password_variable } ] 
      {RECOVERY | NORECOVERY | STANDBY = 
          {standby_file_name | @standby_file_name_var }  } 
      REPLACE 
      RESTART 
      RESTRICTED_USER 
      {REWIND | NOREWIND } 
      {UNLOAD | NOUNLOAD } 
      STATS [ = percentage ] 
      { STOPAT = { date_time | @date_time_var } 
       | STOPATMARK = { 'mark' | 'lsn:lsn_number' }  [ AFTER datetime ] 
       | STOPBEFOREMARK = { 'mark' | 'lsn:lsn_number' }  [ AFTER datetime ] 
      } 

   backup_device:
      logical_backup_device
      @logical_backup_device_var
      {DISK | TAPE } = 'physical_backup_device'
      {DISK | TAPE } = @physical_backup_device_var

Key:
DATABASE - The target database.
FROM backup_device - The backup device from which to restore.
CHECKSUM - verify all backup checksums (or fail)
NO_CHECKSUM - Disable validation of checksums
ENABLE_BROKER - Start Service Broker so that messages may be sent ( default = disabled during a restore)
ERROR_BROKER_CONVERSATIONS - End all conversations, disable broker until operation is complete.
FILE - The backup set to be restored.
KEEP_REPLICATION - Retain replication settings for log shipping. Recover a warm standby.
LOADHISTORY - Load information about the restore operation into the msdb history tables.
MEDIANAME - Specify the name for the media, check for a matching media name on the backup volume.
MOVE - The data or log file (logical name) should be moved by restoring it to the file location specified.
NEW_BROKER - Create a new service_broker_guid value in both sys.databases and the restored database.
RECOVERY- After the restore, roll back any uncommitted transactions.
NORECOVERY- Do not roll back any uncommitted transactions. Use if applying another tx log later.
STANDBY - Specify a standby (undo) file that allows the recovery to be undone, for offline restore only.
REPLACE - Overwrite any existing database with the same name.
RESTART - Restart a restore operation that has been interrupted.
RESTRICTED_USER - Restrict access to the newly restored db to the db_owner, dbcreator, or sysadmin roles.
UNLOAD - Automatically rewind and unload the backup tape when the backup is finished.
STATS - Display a message each time n percentage completes.
STOPATMARK - Recover to a marked transaction or log sequence number
STOPBEFOREMARK - Recover to a marked transaction or log sequence number

Examples

Restore one database onto another, replacing everything:

use master
BACKUP DATABASE Sales TO DISK='d:\backups\sales.bak' with init
RESTORE DATABASE SalesTrain FROM DISK='d:\backups\sales.bak' WITH REPLACE

Restore one database from another:

RESTORE DATABASE ss64
   FROM ss64_1
GO

Restore file list:

RESTORE FILELISTONLY 
   FROM DISK = 'C:\db_backup_file.bak'
GO

Restore one database from another and specify a new location for the data files:

RESTORE DATABASE NewDB 
   FROM DISK = 'C:\ss64db_backup.bak'
   WITH MOVE 'SS64_Data' TO 'C:\Newdb.mdf',
   MOVE 'SS64_Log' TO 'C:\Newdb.ldf'
GO

In SQL Server 2005 DTS was replaced with SSIS, it is often much quicker and easier to just backup and restore than to write, publish and execute a simple SSIS package that does the same thing.

"We don't want to go back to tomorrow, we want to go.. forward" - Dan Quale

Related commands:

Partial Restore - part of database/point in time
Restore Files - Files, Filegroups, or Pages
LOG Restore - a Transaction Log
RESTORE FILELISTONLY - List database and log files
RESTORE DATABASE_SNAPSHOT
RESTORE VERIFYONLY - Verify the backup but don't restore it
BACKUP
Equivalent Oracle command: RMAN - Recovery Manager