Description:
Restore the database from the backup.
Syntax:
RESTORE DATABASE database
FROM DISK |
TAPE = physical_backup_device_name
[
WITH [
REPLACE ]
[ [,]
PASSWORD = password ]
[ [,]
MOVE database
TO file]
]
| database | Name of the restored database. |
|---|
| physical_backup_device_name | Name of the physical device with the backup (file or tape). |
|---|
| FROM DISK | Physical device with the backup is a file. |
|---|
| FROM TAPE | Physical device with the backup is a tape. |
|---|
| WITH | Clause prefaces possible accessory restore parameters. |
|---|
| REPLACE | Restoring the database is performed even if the database with this name already exists. |
|---|
| password | Password of the backup set (if entered on creating the database backup). |
|---|
| MOVE database TO file | When restoring, the database (or log) file is moved from the location of creating the backup to the new location in another SQL Server. |
|---|
Note:
When restoring the database backup, it is necessary to set the database content to another database, preferably 'master', by the means of the command USE master.
Example1:
Restore the backup of the whole database 'pm_data' from the file.
RESTORE DATABASE pm_data FROM DISK='c:\temp\pm_data.bak'
Example2:
Restore the backup of the whole database 'pm_data' from the file into another SQL Server.
RESTORE DATABASE pm_data FROM DISK='c:\temp\pm_data.bak' WITH MOVE 'pm_data' TO 'c:\Program Files\microsoft sql server\mssql\data\pm_data.mdf', MOVE 'pm_data_log' TO 'c:\Program Files\microsoft sql server\mssql\data\pm_data_log.ldf'
Example3:
Restore the backup of the whole database 'pm_data' under a new database name 'pm_data2' from the file into SQL Server. Caution! The new name is used both in database name and in *.mdf and *.ldf files.
RESTORE DATABASE pm_data2 FROM DISK='c:\temp\pm_data.bak' WITH MOVE 'pm_data' TO 'c:\Program Files\microsoft sql server\mssql\data\pm_data2.mdf', MOVE 'pm_data_log' TO 'c:\Program Files\microsoft sql server\mssql\data\pm_data2_log.ldf'