RESTORE (TSQL Statement)
restores a database from a backup file.
Syntax
RESTORE DATABASE database_name [ WITH <option> , ... ]
FROM DISK = 'file_name'
<option> ::= REPLACE | NO_USER | VERBOSE
Arguments
- database_name
- name of the database to restore. If the database exists, it is dropped and recreated. If it doesn’t exist, it is created.
- ‘file_name’
- relative or absolute path of the backup file.
- If file_name is a relative path, the base directory is given by the server parameter
SERVER_DUMP_DIR
.
- If file_name is a relative path, the base directory is given by the server parameter
REPLACE
- if database_name is not the same as the name of the database saved in the backup file, the
REPLACE
option is needed to replace database_name with the content of the backup file. NO_USER
- this option restores only the tables, but not the logins, users, roles, permissions.
VERBOSE
- displays more information about the elements that are being restored.
Remarks
The RESTORE
statement first drops the database database_name, and then creates a new empty database with the same name.
Then, the content of the backup file is restored into this database.
The logins that are attached to the users of the database are created if they don’t exist.
Only sa is allowed to execute this statement, because only sa has permission to drop and create the database.
The backup file is in fact a gzipped tar file (.tgz):
- It contains the SQL scripts to create the tables.
- It also contains the data files for the base tables and indexes. The
RESTORE
statement will change the content of these data files to match the new id of database and tables created in the restored database. - It also contains description of the logins, users, roles and permissions.
Examples
RESTORE DATABASE mydb
FROM DISK = '/Backups/mydb_20160504.rbak' WITH VERBOSE
To restore a database into a database with another name, use the REPLACE
option.
RESTORE DATABASE mydb2
FROM DISK = '/Backups/mydb_20160504.rbak' WITH REPLACE, VERBOSE
With the REPLACE
option, you can easily create many copies of the backup up database.
RESTORE DATABASE mydb_copy1
FROM DISK = '/Backups/mydb_20160504.rbak' WITH REPLACE, VERBOSE
RESTORE DATABASE mydb_copy2
FROM DISK = '/Backups/mydb_20160504.rbak' WITH REPLACE, VERBOSE
RESTORE DATABASE mydb_copy3
FROM DISK = '/Backups/mydb_20160504.rbak' WITH REPLACE, VERBOSE