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.

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