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'